Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James' SQL Footprint

Love SQL Server, Love life.

Archives: May 2012

Backup duration statistics

Here is a simple query which can list backup duration statistics for all database, including the max, min, avg of the backup duration.  you can estimate the backup duration by this query when you plan your backup job



 SELECT
  DBNAME,
  backup_type,
  MAX(sizeMB) as MaxBackupFileSizeMB,
  Max(duration) as MaxDurationSEC,
  MAX(speedMBSEC) as MaxSpeedMBperSEC,
  MIN(sizeMB) as…

Read more

0 comments, 537 reads

Posted in James' SQL Footprint on 25 May 2012

Ad hoc query optimization in SQL Server

When ad hoc queries are executed in sql server, if it is executed without parameters, and it is simple,  SQL Server parameterizes the query internally to increase the possibility of matching it against an existing execution plan, that's called "Simple Parameterization"

For instance:

--clear plan cache first
DBCC FREEPROCCACHE

--run…

Read more

3 comments, 2,999 reads

Posted in James' SQL Footprint on 15 May 2012

Use Powershell script to verify backup files

Before restoring backup, we always verify the backup file first, and run the 3 command below:

1. RESTORE HEADERONLY
    Returns a result set containing all the backup header information for all backup sets on a particular backup device.

2. RESTORE FILELISTONLY
    Returns a result set containing a list of…

Read more

0 comments, 4,140 reads

Posted in James' SQL Footprint on 12 May 2012

Backup database in CMS

Sometime you know the database name which you want to backup, but you don't know which server it is on. Normally we will
1. run query on CMS to find out which server the database is on
2. then connect to the server and run backup query.

If you backup…

Read more

0 comments, 492 reads

Posted in James' SQL Footprint on 9 May 2012