SQL Clone
SQLServerCentral is supported by Redgate
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

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

Read more

0 comments, 1,948 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


Read more

3 comments, 4,646 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:

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

    Returns a result set containing a list of…

Read more

0 comments, 7,394 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, 1,030 reads

Posted in James' SQL Footprint on 9 May 2012