Subscribe to this blog
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…
SELECT
DBNAME,
backup_type,
MAX(sizeMB) as MaxBackupFileSizeMB,
Max(duration) as MaxDurationSEC,
MAX(speedMBSEC) as MaxSpeedMBperSEC,
MIN(sizeMB) as…
0 comments, 316 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…
For instance:
--clear plan cache first
DBCC FREEPROCCACHE
--run…
2 comments, 2,232 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…
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…
0 comments, 3,351 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…
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…
0 comments, 295 reads
Posted in James' SQL Footprint on 9 May 2012