SQL Functions – PATINDEX() « Sql And Me

SQL Functions – PATINDEX() « Sql And Me:

Backup mirror databases script

-- Authro: George Wen
-- this script is to be run on database mirror and will backup all principle databases.

DECLARE @dbname varchar(200)
DECLARE @tsql varchar(500)

if OBJECT_ID('tempdb..#databases') is not null drop table #databases

select distinct d.name as dbname
into #databases
from sys.database_mirroring m join sys.databases d on m.database_id = d.database_id
where mirroring_role = 1

SELECT @dbname = MIN(dbname) FROM #databases

while @dbname is not null
begin
  select @tsql = 'BACKUP DATABASE ' + @dbname + ' TO  DISK = N''E:\MSSQL10\MSSQL\Backup\' + @dbname + CONVERT(varchar(10),getdate(),120) + '.bak'
  exec(@tsql)
  --print @tsql
  select @dbname = min(dbname) from #databases where dbname > @dbname

end

index auditing script

Index counts:

select

o.name, COUNT(*)

from  sys.indexes i inner join sys.objects o on i.object_id=o.object_id

where o.type='U' and i.index_id<>1

group by o.name

order by COUNT(*) desc

index usage/unused indexes

select

--db_name(db_id()) as [database]

--, objectname=object_name(s.object_id)

ROW_NUMBER() over (partition by o.name,i.name order by i.index_id) as rowcouter

,

'CREATE ' +

CASE i.IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +

'NONCLUSTERED ' + ' INDEX '+

i.NAME + ' ' +' ON ' +

OBJECT_NAME (i.OBJECT_ID) +

'('+

DBO.fGetIndexCols (object_NAME(i.OBJECT_ID), i.index_id) + ')'

+Case when dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id)+')' end +

' ON ['+

( SELECT GROUPNAME

FROM SYSFILEGROUPS

WHERE GROUPID = DATA_SPACE_ID

) + ']' IndexScript

,

'drop index ' + i.name + ' on ' + o.name as dropscript

,    Indexed_Columns  = LEFT(ixColumns, LEN(ixColumns) -1)

     , Included_Columns = LEFT(includedColumns, LEN(includedColumns) -1)

, o.name  as tablename

, indexname=i.name

, i.index_id 

, user_seeks

, user_scans

, user_lookups

, user_updates

--, user

--, reads=user_seeks + user_scans + user_lookups 

--, writes =  user_updates 

from

 sys.indexes i inner join sys.objects o on i.object_id=o.object_id

left join sys.dm_db_index_usage_stats s on i.index_id=s.index_id and i.object_id=s.object_id

 CROSS APPLY (

            SELECT sc.Name + CASE WHEN is_descending_key = 1 THEN ' DESC'

                                  ELSE '' END

                 + ', '

              FROM sys.index_columns ic

              JOIN sys.columns sc

                on ic.object_id = sc.object_id

               AND ic.column_id = sc.column_id

             WHERE i.object_id = ic.object_id

               AND is_included_column = 0

               and i.index_id = ic.index_id

             ORDER BY ic.Key_Ordinal

              FOR XML PATH('') ) ix (ixColumns)

CROSS APPLY (

            SELECT sc2.Name + ', '

              FROM sys.index_columns ic2 JOIN sys.columns sc2 on ic2.object_id = sc2.object_id

               AND ic2.column_id = sc2.column_id

             WHERE i.object_id = ic2.object_id

               AND is_included_column = 1

               and i.index_id = ic2.index_id

             ORDER BY ic2.Key_Ordinal

              FOR XML PATH('') ) nc (includedColumns)

             

 where o.type='U' --and s.database_id=DB_ID()

  and i.index_id <>1

  and o.name not like 'tem%'

  and (isnull(user_seeks,0) + isnull(user_scans,0) + isnull(user_lookups,0))= 0  --comment out to show all indexes usage

  and i.name is not null

   --and i.index_id=0

--order by o.name, user_seeks, user_scans,user_lookups, user_updates

Missing indexes

;WITH I AS (

-- Missing Indexes current database by Index Advantage

-- This DMV Query written by Glenn Berry

SELECT user_seeks * avg_total_user_cost *

      (avg_user_impact * 0.01) AS [index_advantage],

migs.last_user_seek,

mid.[statement] AS [Database.Schema.Table],

mid.equality_columns, mid.inequality_columns,

mid.included_columns,migs.unique_compiles, migs.user_seeks,

migs.avg_total_user_cost, migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

WHERE mid.database_id = DB_ID()

      AND user_seeks * avg_total_user_cost *

      (avg_user_impact * 0.01) > 10000 -- Set this to Whatever

)

Note: multiple sources were used for the above script.

free web stress test tools

Since LoadRunner is somewhat expensive and I am interested to find out if there is any free tools available that can be similar job, so far I have read a few good reviews and articles, it seems the combination of Fiddler + WCat and Badboy+Jmeter might be a good way to go, will try to do some test soon.

automated backup of mysql and web site content to dropbox under text mode

I used to run a personal web site on a linux VPS, below is how I backup my sites and database content to dropbox.
(please note, not all these ideas came from me, I came across a few sources from internet and just put it together and make it work for me)

1. install dropbox on linux, see http://www.dropboxwiki.com/Text_Based_Linux_Install#Post-installation, I use folder /var/backup/Dropbox as dropbox folder.
2. use crontab to backup mysql and web content to dropbox folder, use compression to reduce bandwidth usage.
3. you can also you crontab to remove old backup files automatically.

purgeDropbox.sh:

#!/bin/bash

# limit on the size (in KB) of the directory

limit=1572864

adminmail=webmaster@yourdomain.com

# if directory exists, find out it's size

if [ -d /var/backup/Dropbox ]

then

  size=$(/usr/bin/du -sk /var/backup/Dropbox | cut -f1)

else

  echo "/var/backup/Dropbox is invalid !!!" | /bin/mail -s "Invalid directory" $adminmail

  exit

fi

# if directory size is greater than limit, alert the user

if [ $size -gt $limit ]

then

  echo "Data volume in your user home dir /var/backup/Dropbox with a total size of  ${size} kB exceeds the maximum limit of $limit kB (1.5 GB) ! Please take action!" |

 /bin/mail -s "user home dir exceeds max limit" $adminmail

   find /var/backup/Dropbox/redmine -name "*.gz" -mtime +30 -exec rm {} \; >> /root/purge.log 2>&1

fi

crontab entries:

30 20 * * *  mysqldump -u root -pPASSWORD --databases db1 db2 |gzip >/var/backup/Dropbox/database_$(date +\%Y\%m\%d__\%H:\%M:\%S\%z).sql.gz
00 19 * * * /root/purgeDropbox.sh

00 22 * * *  /bin/tar czfv /var/backup/Dropbox/web_$(date  +\%Y\%m\%d__\%H:\%M:\%S\%z).tar.gz /var/www

Copy Directory Structure ONLY – Windows 7 | Knowledgement from Target Integration

Copy Directory Structure ONLY – Windows 7 | Knowledgement from Target Integration:

xcopy /T /E “Source” “Destination”

'via Blog this'

Update Resource Cost rate Using PSI « Project Server And Sharepoint Solutions

Update Resource Cost rate Using PSI « Project Server And Sharepoint Solutions:

'via Blog this'

AJAX call web service behind ISA / TMG firewall with authentication

To be able to call web service behind ISA / TMG firewall, a few conditions need to be met:
1. use beforeSend when making the service call:

 $.ajax({
    url: weburl,
    beforeSend: function (xhr) { xhr.setRequestHeader ("Authorization", "Basic xxx"); }, 
    type: "POST",
    dataType: "xml",
    data: soapEnv,
    complete: processResult,
    contentType: "text/xml; charset=\"utf-8\""
   });

2. Basic authentication needs to be enabled on both ISA / TMG and IIS.
3. Enable keberos authentication for the published web app.
4. disable loopback check on the web server.

Handling Apostrophe (single-quote) in XPath expressions in .NET « Bugs! Space

Handling Apostrophe (single-quote) in XPath expressions in .NET « Bugs! Space:

'via Blog this'

DataTable .Select and .RowFilter Escaping with Apostrophe’s « Travis J. Gosselin

DataTable .Select and .RowFilter Escaping with Apostrophe’s « Travis J. Gosselin:

'via Blog this'

Batch Import dtsx packages from folder to SSIS Store

FOR %i In (*.dtsx) DO dtutil /FILE "%i" /DESTSERVER LOCALHOST /COPY SQL;"\MYBI\Project Analysis\Initial Load\%~ni"

Verify SQL Server Stored Procedures are the same on multiple servers

Verify SQL Server Stored Procedures are the same on multiple servers:

'via Blog this'

Grumpy DBA: Service Broker GUID does not match the one in sys.databases

Grumpy DBA: Service Broker GUID does not match the one in sys.databases:

'via Blog this'

Authentication Problems by using of NTLM - Damir Dobric Posts - developers.de

While working at solving the infamous error message:
The HTTP request is unauthorized with client authentication scheme 'Ntlm'. The authentication header received from the server was 'NTLM'.
I came across the following article:


Authentication Problems by using of NTLM - Damir Dobric Posts - developers.de:

unfortunately, this isn't exactly the same issue, at the end, my problem was fixed by enabling cookies in app.config. it seems that when accessing a web service behind a ISA web farm and the infinity settings is set to by cookies, we will need to enable cookies in the app.config to be able to access the web service correctly, which in turn, is not hard to understand.

Conformed Dimensions & Conformed Facts

Conformed Dimensions & Conformed Facts:

'via Blog this'

KB: Vmware Resize Linux Hard drive Increase Size Enlarge Disk

KB: Vmware Resize Linux Hard drive Increase Size Enlarge Disk:

'via Blog this'

Trivial Proof: Resizing a VirtualBox Virtual Hard Disk

Trivial Proof: Resizing a VirtualBox Virtual Hard Disk:

'via Blog this'

When you should pick up the phone, and why - CBS News

When you should pick up the phone, and why - CBS News: #in

'via Blog this'

How to re-produce the "black screen with cursor before login" issue

I came into this two days ago and it has given me a really hard time to figure out a fix.
it turned out though, the cause of my issue was probably bit different: I have two video output from the computer connect to the same monitor, one of them via a KVM. this setup had been working for a while but somehow when I rebooted my PC this time, I only get this infamous black screen death symptom, it is only after I powered off one of the monitor that I figured out that's the cause...

count string Occurrences in a column and stuff string by number of space stored in another column

select len(YourCol)-len(replace(YourCol,'1','')),space(col2) + col3