Monthly Archives: October 2010

Master Database Compatibility Level

If you performed an in place upgrade of your SQL Server, your master database may be at a lower compatibility level than all of the other system databases.  This was done by conscious decision from Microsoft in an attempt to make for a smooth upgrade and ensure that existing applications can still function post upgrade.  This has the negative side effect of not being able to use operators, keywords and other functionality when the database, in whose context you are executing these queries, is not at the right compatibility level.

For instance, if you want to examine currently executing queries using SQL 2005 with your master database in 80 compatibility …

USE master

GO

SELECT s2.dbid,s2.objectid,SUBSTRING (s2.text, s1.statement_start_offset/2 + 1,

CASE WHEN s1.statement_end_offset = -1 THEN 8192

ELSE s1.statement_end_offset/2 – s1.statement_start_offset/2 END + 1) as QueryText

FROM sys.dm_exec_requests s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

GO

… you will receive the following error…

Msg 321, Level 15, State 1, Line 5

“sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

As a workaround, you can execute the same query against tempdb, model or msdb and it will work with no errors.  It is permissible to change the compatibility level of the master database should you find it in a low compatibility level.

–SQL 2005

EXEC sp_dbcmptlevel ‘master’ , 90

GO

–SQL 2008

EXEC sp_dbcmptlevel ‘master’ , 100

GO

http://msdn.microsoft.com/en-us/library/ms178653.aspx

http://technet.microsoft.com/en-us/library/bb933942.aspx

Oct 2010 TOM: Are your backups ready for a disaster?

Database backups are a critical part of SQL Server disaster planning. It’s also critical to know that your backups are viable. Read the following tip and be assured you’re ready should a disaster hit. And don’t forget to regularly monitor the 11 VitalSigns  by SQLRx to debug performance bottlenecks.

SQL Server Administration: The best way to verify backups is to actually restore backup files, but many times this task falls by the wayside due to lack of space or time. In lieu of restoring backup files, you can use RESTORE VERIFYONLY to confirm that a backup set is complete and readable. Here is a routine that can be used to verify the most recent backup file for each database in a SQL instance. This can be used in a job to routinely verify that backup files are intact and should be able to be restored in the event of a disaster.
___________________________________

SET NOCOUNT ON
DECLARE @dbname VARCHAR(128)
DECLARE @finishdate DATETIME
DECLARE @diskbackup NVARCHAR(260)
DECLARE @verifystatement NVARCHAR(360)
DECLARE @timebegin DATETIME
DECLARE @timeend DATETIME
DECLARE @timeelapsed INT
DECLARE @FileFound INT

DECLARE csrDatabases CURSOR FOR
SELECT database_name, MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE type = ‘D’
GROUP BY database_name

OPEN csrDatabases

FETCH NEXT FROM csrDatabases INTO @dbname, @finishdate

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @diskbackup = bmf.physical_device_name
FROM msdb.dbo.backupmediafamily bmf JOIN msdb.dbo.backupset bs
ON (bmf.media_set_id = bs.media_set_id)
WHERE bs.type = ‘D’
AND bs.database_name = @dbname
AND bs.backup_finish_date = @finishdate

EXEC master.dbo.xp_fileexist @diskbackup, @FileFound OUTPUT
IF @FileFound=1
BEGIN
SET @verifystatement = ‘RESTORE VERIFYONLY FROM DISK = ”’+@diskbackup+”’ WITH LOADHISTORY’
SELECT @timebegin = GETDATE()
EXEC sp_executesql @verifystatement
SELECT @timeend = GETDATE()
SELECT @timeelapsed = DATEDIFF(s,@timebegin,@timeend);

PRINT ‘Backup file ‘+@diskbackup+’ verified in ‘+CAST(@timeelapsed AS VARCHAR(20))+’ seconds.’

END

FETCH NEXT FROM csrDatabases INTO @dbname, @finishdate
END
 
CLOSE csrDatabases
DEALLOCATE csrDatabases
SET NOCOUNT OFF