Tuesday, December 18, 2012

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
  select @tsql = 'BACKUP DATABASE ' + @dbname + ' TO  DISK = N''E:\MSSQL10\MSSQL\Backup\' + @dbname + CONVERT(varchar(10),getdate(),120) + '.bak'
  --print @tsql
  select @dbname = min(dbname) from #databases where dbname > @dbname


Monday, November 26, 2012

index auditing script

Index counts:

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

--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
i.NAME + ' ' +' ON ' +
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 ['+
) + ']' 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 
 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
            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)
            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


-- 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],

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.

Saturday, November 17, 2012

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.

Monday, September 24, 2012

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.

# limit on the size (in KB) of the directory
# if directory exists, find out it's size
if [ -d /var/backup/Dropbox ]
  size=$(/usr/bin/du -sk /var/backup/Dropbox | cut -f1)
  echo "/var/backup/Dropbox is invalid !!!" | /bin/mail -s "Invalid directory" $adminmail
# if directory size is greater than limit, alert the user
if [ $size -gt $limit ]
  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
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

Saturday, August 25, 2012

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:

    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.

Sunday, June 10, 2012

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"

Tuesday, April 10, 2012

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.

Friday, March 9, 2012

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...