Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

DBCC CHECKDB stored procedure - new version

I've updated my isp_DBCC_CHECKDB stored procedure as I was getting errors on a SQL Server 2005 server when one of the databases was the mirror in a database mirroring scenario or when one of the databases was in a loading state (NORECOVERY option of RESTORE command).  This issue will actually appear on SQL Server 2000 as well; I just hadn't noticed it on 2000 yet.  

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_DBCC_CHECKDB
--
-- AUTHOR               : Tara Duggan
-- DATE     : May 3, 2004
--
-- INPUTS    : None
-- OUTPUTS    : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure runs DBCC CHECKDB for each of the databases.
--
-- EXAMPLES (optional)  : EXEC isp_DBCC_CHECKDB
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- 02/07/2007 Tara Kizer
-- Added support for SQL Server 2005 (didn't want to use sysdatabases since it's only there for
-- backward compatibility.
--
-- Added the option to skip databases that are in a loading state, such as those with the NORECOVERY
-- option set or those that are the mirror in database mirroring.
----------------------------------------------------------------------------------------------------
ALTER PROC [dbo].[isp_DBCC_CHECKDB]
AS

SET NOCOUNT ON

DECLARE @dbid int, @DBName sysname, @SQL nvarchar(4000), @version char(1)

SELECT @dbid = 0, @version = CONVERT(char(1), SERVERPROPERTY('ProductVersion'))

IF @version = '8'
BEGIN
 WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases WHERE dbid > @dbid AND status & 32 = 0)
 BEGIN
  SELECT TOP 1 @dbid = dbid, @DBName = name
  FROM master.dbo.sysdatabases
  WHERE dbid > @dbid AND status & 32 = 0
  ORDER BY dbid

  SET @SQL = 'DBCC CHECKDB([' + @DBName + '])'

  EXEC sp_executesql @statement = @SQL
 END
END
ELSE IF @version = '9'
BEGIN
 WHILE @dbid < (SELECT MAX(database_id) FROM master.sys.databases WHERE database_id > @dbid AND state IN (0, 4))
 BEGIN
  SELECT TOP 1 @dbid = database_id, @DBName = name
  FROM master.sys.databases
  WHERE database_id > @dbid AND state IN (0, 4)
  ORDER BY database_id

  SET @SQL = 'DBCC CHECKDB([' + @DBName + '])'

  EXEC sp_executesql @statement = @SQL
 END
END

Print | posted on Wednesday, February 07, 2007 2:55 PM | Filed Under [ SQL Server - Database Administration ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET