Simple DBCC CHECKDB process to report on database corruption for all SQL Server databases


By:   |   Updated: 2020-08-18   |   Comments (19)   |   Related: More > Database Console Commands DBCCs


Problem

As DBAs, we all know how important it is to check all of the SQL Server databases under our care for potential corruption issues. However, to accomplish that, we might not have the time to do any of the following:

  • Setup a maintenance plan across all X SQL Server instances under our support.
  • Setup a custom job to run a consistency check across all databases in all X SQL Server instances (which this tip particularly aims to address).
  • Build a PowerShell solution (if you want to get a bit fancy here).

Regardless of the way you choose to go, you have to consider other important factors as well:

  • Environment type (production / non-production)
  • When is the maintenance window?
  • How much time do you have during the maintenance window?
Solution

I have come up with a custom *simple solution that lets you check the consistency of all the databases under your care, save only the end result (without that much overhead) with a few additional important values, while having to modify only a couple of things that are very specific to your use case.

*By simple I’m not implying that the amount of code used is small, but instead simple in terms of what it accomplishes in the end.

SQL Server DBCC CHECKDB Script as a whole

The entire script consists of 3 sections:

  • Creation of the table to store the final results.
  • Creation of the custom stored procedure.
  • Creation of the job to automate it.

You can find complete "plug-and-play" script, in case you want to test it right away, attached within this tip.

I will be detailing each section in the next sub-sections.

Creation of the SQL Server DBCC CheckDB table

This will be used to store the result of the execution of the DBCC CHECKDB statement against each database.

  • You can use a different database, I used the master just as an example.
  • Here’s the description for the fields that might not be so obvious at a first glance:
    • [checkdb_type] -> ‘FULL’ or ‘PHYSICAL ONLY’ (will explain later the reason for these 2 values in the Stored Procedure).
    • [completion_time] in seconds because it is very useful to know how long a CHECKDB against each database took. You can even use this information to plan your checks in a more efficient way because you can get an average of the time it takes to complete for each database.
    • [last_good_dbcc] tells you when was the last time a successful CHECKDB took place, something very important to know so that later on you can prioritize those that haven’t had a check in a while (for whatever reason).
USE [master]
GO
CREATE TABLE [dbo].[CheckDB](
   [instance] [varchar](255) NOT NULL,
   [database] [varchar](255) NOT NULL,
   [size] [int] NOT NULL,
   [result] [varchar](max) NULL,
   [checkdb_type] [varchar](255) NULL,
   [data_collection_timestamp] [smalldatetime] NULL,
   [completion_time] [int] NULL,
   [last_good_dbcc] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Creation of the SQL Server DBCC CheckDB Stored Procedure

At a first glance the stored procedure might seem quite big, but it is only because it contains some validations and considerations that you probably won’t find in similar versions.

Here are the most important points to note:

  • From the very start, you can see that the stored procedure can accept 2 parameters:
    • @dbSizeThreshold: by default, it is set at 512,000 MB (500GB) and it is used to determine if the CHECKDB will use the "PHYSICAL_ONLY" option for databases larger than 500GB (you can set it to whatever you think fits your case). Let’s keep in mind that your maintenance window might not be enough to accommodate a full CHECKDB for a big database (or databases), and a run with "PHYSICAL_ONLY" option might represent at least a small victory for you.
    • @force: this parameter is designed to override the @dbSizeThreshold behavior. For instance, you might have an instance with only 1 big database (bigger than the threshold value) but you feel confident enough that a full CHECKDB can be successful within your maintenance window, so you go for it and set it to 1 to proceed with a full run of the CHECKDB.
  • This stored procedure takes into account the SQL Server version where it’s being executed, so you don’t have to worry to deploy a different version depending on how old yours is.
    • Since we are executing the CHECKDB command WITH TABLERESULTS, there is a slight variation in the output obtained starting from SQL Server 2012 (since it adds more fields to the mix), but the stored procedure creates the right table for you.
  • You might notice that I use a cursor to traverse the database list and run the CHECKDB against each one of them.
    • I know that cursors are somewhat of a "taboo" thing within our profession, for their low performance, but its purpose is purely for control.
    • First I populate the table "master.dbo.CheckDB" with the databases to be checked by the stored procedure. As each one is checked successfully, then the respective record is updated in the table.
    • By doing this, you know if a particular database was not a part of the process.
      • Server might have been patched and rebooted while the execution was taking place.
      • Server was unexpectedly shutdown while the execution was taking place.
      • The SQL Server instance was restarted while the execution was taking place.
    • When CHECKDB is executed against the master database, the output for the ResourceDB is also obtained, so I also make sure to capture that one as well.
USE [master]
GO

/****** Object:  StoredProcedure [dbo].[Simple_CHECKDB]    Script Date: 8/30/2018 3:45:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Alejandro Cobar
-- Create date: 8/22/2018
-- Description:	Runs DBCC CHECKDB ON each database and stores the output message 
-- =============================================
CREATE PROCEDURE [dbo].[Simple_CHECKDB]
@dbSizeThreshold INT = 512000,
@force INT = 0
AS
BEGIN
	SET NOCOUNT ON;

	-- Temporal table to obtain the "Last Known Good DBCC CHECKDB" execution for each database
	IF NOT OBJECT_ID('tempdb..#DBInfo') IS NULL  
	DROP TABLE #DBInfo; 

	CREATE TABLE #DBInfo
	([ParentObject] VARCHAR(255)
	,[Object] VARCHAR(255)
	,[Field] VARCHAR(255)
	,[Value] VARCHAR(255)
	)

	-- Depending on the SQL Server version, the respective temporal table will be created to store the CHECKDB results
	DECLARE @version INT;
	SELECT @version = RIGHT(LEFT(@@VERSION,25),4);

	--Starting from SQL Server 2012, new fields were introduced to the output of DBCC CHECKDB WITH TABLERESULTS
	IF NOT OBJECT_ID('tempdb..#CheckDB_old') IS NULL  
	DROP TABLE #CheckDB_old; 

	IF NOT OBJECT_ID('tempdb..#CheckDB_new') IS NULL  
	DROP TABLE #CheckDB_new;

	IF @version >= 2012
	CREATE TABLE #CheckDB_new 
	([Error] INT 
	,[Level] INT 
	,[State] INT 
	,[MessageText] VARCHAR(MAX) 
	,[RepairLevel] INT 
	,[Status] INT 
	,[DbId] INT 
	,[DbFragId] INT 
	,[ObjectID] INT 
	,[IndexId] INT 
	,[PartitionId] INT 
	,[AllocUnitId] INT 
	,[RidDbId] INT 
	,[RidPruId] INT 
	,[File] INT 
	,[Page] INT 
	,[Slot] INT 
	,[RefDbID] INT 
	,[RefPruId] INT 
	,[RefFile] INT 
	,[RefPage] INT 
	,[RefSlot] INT 
	,[Allocation] INT);
	ELSE
	CREATE TABLE #CheckDB_old 
	([Error] INT 
	,[Level] INT 
	,[State] INT 
	,[MessageText] VARCHAR(MAX) 
	,[RepairLevel] INT 
	,[Status] INT 
	,[DbId] INT 
	,[ObjectID] INT 
	,[IndexId] INT 
	,[PartitionId] INT 
	,[AllocUnitId] INT 
	,[File] INT 
	,[Page] INT 
	,[Slot] INT 
	,[RefFile] INT 
	,[RefPage] INT 
	,[RefSlot] INT 
	,[Allocation] INT);
	
	-- We don't want to keep all the CHECKDB results here for a very long time...
	TRUNCATE TABLE master.dbo.CheckDB;

	-- Insert all the databases that will be checked
	-- Only consider those in ONLINE state and exclude the SNAPSHOTS
	INSERT INTO master.dbo.CheckDB
	SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), DB_NAME(mf.database_id), SUM(CAST(mf.size AS BIGINT)*8)/1024, NULL, NULL, NULL, NULL,NULL
	FROM sys.master_files mf
	JOIN sys.databases db ON mf.database_id = db.database_id
	WHERE db.state = 0 AND db.source_database_id IS NULL
	GROUP BY mf.database_id;
	
	-- Prepare a cursor to have a better control of which databases where checked and which weren't
	-- A sudden server or instance reboot might affect this whole process...
	DECLARE @db VARCHAR(255);

	DECLARE checkdb_cursor CURSOR FOR 
	SELECT [database] 
	FROM master.dbo.CheckDB
	WHERE result IS NULL;

	OPEN checkdb_cursor  
	FETCH NEXT FROM checkdb_cursor INTO @db
	
	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		DECLARE @startTime DATETIME;
		DECLARE @endTime DATETIME;

      	DECLARE @databaseSize BIGINT;	
		SELECT @databaseSize = size FROM master.dbo.CheckDB WHERE [database] = @db;
		
		SET @startTime = GETDATE();
	
		IF @databaseSize <= @dbSizeThreshold OR @force = 1
		BEGIN
			IF @version >= 2012
			BEGIN
				INSERT INTO #CheckDB_new 
				([Error], [Level], [State], [MessageText], [RepairLevel], 
				[Status], [DbId], [DbFragId], [ObjectID], [IndexId], [PartitionId], 
				[AllocUnitId], [RidDbId], [RidPruId], [File], [Page], [Slot], [RefDbID], 
				[RefPruId], [RefFile], [RefPage], [RefSlot], [Allocation]) 
				EXEC ('DBCC CHECKDB(['[email protected]+']) WITH TABLERESULTS');
				
				SET @endTime = GETDATE();

				UPDATE master.dbo.CheckDB
				SET result = MessageText, checkdb_type = 'FULL', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)
				FROM #CheckDB_new
				WHERE [Error] = 8989 AND [database] = @db;

				IF @db = 'master'
				INSERT INTO master.dbo.CheckDB 
				SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'FULL', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL
				FROM #CheckDB_new
				WHERE [Error] = 8989 AND DbId = 32767;

				TRUNCATE TABLE #CheckDB_new;
			END;
			ELSE
			BEGIN
				INSERT INTO #CheckDB_old
				([Error], [Level], [State], [MessageText], [RepairLevel], 
				[Status], [DbId], [ObjectID], [IndexId], [PartitionId], 
				[AllocUnitId], [File], [Page], [Slot], [RefFile], [RefPage], [RefSlot], [Allocation]) 
				EXEC ('DBCC CHECKDB(['[email protected]+']) WITH TABLERESULTS');

				SET @endTime = GETDATE();

				UPDATE master.dbo.CheckDB
				SET result = MessageText, checkdb_type = 'FULL', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)
				FROM #CheckDB_old
				WHERE [Error] = 8989 AND [database] = @db;

				IF @db = 'master'
				INSERT INTO master.dbo.CheckDB 
				SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'FULL', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL
				FROM #CheckDB_old
				WHERE [Error] = 8989 AND DbId = 32767;

				TRUNCATE TABLE #CheckDB_old;
			END;
		END;
		ELSE
		BEGIN
			IF @version >= 2012
			BEGIN
				INSERT INTO #CheckDB_new 
				([Error], [Level], [State], [MessageText], [RepairLevel], 
				[Status], [DbId], [DbFragId], [ObjectID], [IndexId], [PartitionId], 
				[AllocUnitId], [RidDbId], [RidPruId], [File], [Page], [Slot], [RefDbID], 
				[RefPruId], [RefFile], [RefPage], [RefSlot], [Allocation]) 
				EXEC ('DBCC CHECKDB(['[email protected]+']) WITH TABLERESULTS, PHYSICAL_ONLY');
				
				SET @endTime = GETDATE();

				UPDATE master.dbo.CheckDB
				SET result = MessageText, checkdb_type = 'PHYSICAL ONLY', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)
				FROM #CheckDB_new
				WHERE [Error] = 8989 AND [database] = @db;

				IF @db = 'master'
				INSERT INTO master.dbo.CheckDB 
				SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'PHYSICAL ONLY', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL
				FROM #CheckDB_new
				WHERE [Error] = 8989 AND DbId = 32767;

				TRUNCATE TABLE #CheckDB_new;
			END;
			ELSE
			BEGIN
				INSERT INTO #CheckDB_old
				([Error], [Level], [State], [MessageText], [RepairLevel], 
				[Status], [DbId], [ObjectID], [IndexId], [PartitionId], 
				[AllocUnitId], [File], [Page], [Slot], [RefFile], [RefPage], [RefSlot], [Allocation]) 
				EXEC ('DBCC CHECKDB(['[email protected]+']) WITH TABLERESULTS, PHYSICAL_ONLY');

				SET @endTime = GETDATE();

				UPDATE master.dbo.CheckDB
				SET result = MessageText, checkdb_type = 'PHYSICAL ONLY', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)
				FROM #CheckDB_old
				WHERE [Error] = 8989 AND [database] = @db;

				IF @db = 'master'
				INSERT INTO master.dbo.CheckDB 
				SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'PHYSICAL ONLY', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL
				FROM #CheckDB_old
				WHERE [Error] = 8989 AND DbId = 32767;

				TRUNCATE TABLE #CheckDB_old;
			END;
		END;
		
		-- Get the information for the "Last Known Good DBCC CHECKDB" execution 
		INSERT INTO #DBInfo ([ParentObject], [Object], [Field], [Value]) 
	    EXEC ('DBCC DBINFO(['[email protected]+']) WITH TABLERESULTS');

		UPDATE master.dbo.CheckDB
		SET last_good_dbcc = [Value]
		FROM #DBInfo
		WHERE [Field] = 'dbi_dbccLastKnownGood' AND [database] = @db;
		
		IF @db = 'master'
		UPDATE master.dbo.CheckDB
		SET last_good_dbcc = (SELECT last_good_dbcc FROM master.dbo.CHECKDB WHERE [database] = 'master')
		WHERE [database] = 'mssqlsystemresource';

		TRUNCATE TABLE #DBInfo; 

		FETCH NEXT FROM checkdb_cursor INTO @db 
	END

	CLOSE checkdb_cursor  
	DEALLOCATE checkdb_cursor  
	
	-- Drop whichever temporal table was created
	IF NOT OBJECT_ID('tempdb..#CheckDB_old') IS NULL  
	DROP TABLE #CheckDB_old; 

	IF NOT OBJECT_ID('tempdb..#CheckDB_new') IS NULL
	DROP TABLE #CheckDB_new; 

	IF NOT OBJECT_ID('tempdb..#DBInfo') IS NULL  
	DROP TABLE #DBInfo; 
END
GO

Creation of the DBCC CheckDB SQL Server Agent Job

Depending on your particular case, you might want to accommodate the schedule of the job to fit your maintenance window.

  • You might create a version for your production environment and another for your non-prod environment and start the deployment from there.
  • The modifications you probably would want to make are the following:
    • This example uses the ‘sa’ account, but you can use a different one.
    • You can change the name of the job.
    • You can change the schedule of the job.
USE [msdb]
GO

/****** Object:  Job [Database Consistency Check]    Script Date: 8/28/2018 5:53:06 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 8/28/2018 5:53:06 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - Database Consistency Check', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Runs a DBCC CHECKDB operation against all the databases in the SQL Server instance', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CheckDB]    Script Date: 8/28/2018 5:53:07 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N'CheckDB', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC Simple_CHECKDB', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], @name=N'CHECKDB Execution', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=64, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20180822, 
		@active_end_date=99991231, 
		@active_start_time=200000, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

SQL Server DBCC CHECKDB Demo

Let me present you a few images of the output stored in the CheckDB table, for possible execution cases.

The execution times for the StackOverflow database are a bit high because I have those test databases in an external mechanical hard drive (5400 RMP), so that kind of slowness is expected.

Normal Execution:

EXEC Simple_CHECKDB
dbcc checkdb output

Modifying the @databaseSizeThreshold parameter:

EXEC Simple_CHECKDB @dbSizeThreshold = 100000

Notice the "PHYSICAL ONLY" result and it took 1,356 sec. less to complete.

dbcc checkdb output

Modifying the @force parameter:

EXEC Simple_CHECKDB @force = 1, @dbSizeThreshold = 5

Even if the database size parameter, to trigger a "PHYSICAL_ONLY" run, matches with the actual sizes of the databases to check, the @force parameter will "force" the CHECKDB to be a full one.

dbcc checkdb output

Bonus Content - PowerShell Version to Collect SQL Server DBCC CHECKDB Results

Attached within this tip you can also find a custom PowerShell that allows you to grab all the CheckDB results from a set of SQL Server instances and store them in a central database that you might have.

  • You will have to modify some parameters, but I’ve put XXX where your values should go to make it work.
$labServer = "XXX"
$inventoryDB = "XXX"


#Fetch all the instances to connect to
$instanceLookupQuery = "SELECT instance from XXX"

$instances = Invoke-Sqlcmd -ServerInstance $labServer -Database $inventoryDB -Query $instanceLookupQuery

if($args[0].Length -gt 0 -and $instances -eq $null){
	Write-Host "Instance doesn't exist"
	break
}

#For each instance, grab the CheckDB results from master.dbo.CheckDB
foreach ($instance in $instances){
	$CheckDBQuery = "SELECT * FROM master.dbo.CheckDB"
	
	Write-Host "Fetching CheckDB results for instance" $instance.instance
	try{$results = Invoke-Sqlcmd -Query $CheckDBQuery -ServerInstance $instance.instance -ErrorAction Stop -querytimeout 30}
         catch{Invoke-Sqlcmd -ServerInstance $labServer -Database $inventoryDB -Query "INSERT INTO XXX VALUES('CheckDB','$($instance.instance)','$($_.Exception.Message)',GETDATE())"}
	
	#Perform the INSERT in the XXX table only if it returns information
   	if($results.Length -ne 0){
    
		#Build the insert statement
		$insert = "INSERT INTO XXX VALUES"
		foreach($result in $results){        
            	$result_text = $result['result'] -replace "'","''"
            
			$insert += "
			(
			'"+$result['instance']+"',
			'"+$result['database']+"',
			"+$result['size']+",
			'"+$result_text+"',
			'"+$result['checkdb_type']+"',
            		'"+$result['data_collection_timestamp']+"',
            		"+$result['completion_time']+",
            		'"+$result['last_good_dbcc']+"'
            		),
			"
	     	}

		#Store the results in the local XXX table in our Lab Server instance
		Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $labServer -Database $inventoryDB
	}	
}

Write-Host "Done!"
Next Steps
  • This code has been tested in the following SQL Server versions: 2005, 2008, 2008R2, 2012, 2014, 2016 and 2017.
  • I’m all ears for any suggestions, improvements, recommendations regarding my code; which, in fact, I’m planning to keep updating to make it even better.
  • I have seen a lot of debate on whether or not you should run a CHECKDB against the tempdb. You should check out this post by Kendra Little.
  • Check out all of the SQL Server DBCC CHECKDB tips.


Last Updated: 2020-08-18


get scripts

next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips
Related Resources





Comments For This Article




Friday, July 03, 2020 - 2:51:22 PM - Alejandro Cobar Back To Top (86085)

Thanks for pointing this one out Matt!

I will make the update and request the article to reflect that update.

Good luck with that big database!!!


Friday, July 03, 2020 - 6:19:55 AM - Matt H Back To Top (86083)

Someone mentions this below, but I had to make a number of changes to accommodate a larger database: (approx 4.6Tb)

On the table designer - change the Size column to a BigInt (currently Int)

On the SP - change this line:
SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), DB_NAME(mf.database_id), SUM(mf.size*8)/1024, NULL, NULL, NULL, NULL,NULL

To:
SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), DB_NAME(mf.database_id), SUM(CAST(mf.size AS BIGINT)*8)/1024, NULL, NULL, NULL, NULL,NULL

This line:
DECLARE @databaseSize INT;

To:
DECLARE @databaseSize BIGINT;

It should then work work successfully, as it does for me. - Prepare for a long wait with databases this size!


Monday, April 20, 2020 - 8:07:29 AM - Charles Groleau Back To Top (85419)

It's present in the site but not in the plug&play script.


Monday, April 20, 2020 - 8:05:50 AM - Charles Groleau Back To Top (85418)

To avoid failing on db names that contains an hyphen consider editing the following lines as below. i.e.: Add the square bracket.

    EXEC ('DBCC DBINFO(['[email protected]+']) WITH TABLERESULTS');

Wednesday, April 15, 2020 - 3:19:06 PM - Charles Groleau Back To Top (85384)

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Completion time: 2020-04-15T14:19:25.2889944-04:00


Wednesday, April 15, 2020 - 1:48:31 PM - Alejandro Cobar Back To Top (85382)

@Charles, it would be useful if you could share the error that your database is encountering to determine why it is not making its way to the CheckDB table.

@Gryph, that is weird... it shouldn't pick up offline databases. I'll give it a shot and submit the respective update accordingly.


Wednesday, April 15, 2020 - 11:20:13 AM - Charles Groleau Back To Top (85379)

If errors are found in database the results are not saved in the CheckDB. See below.

instance         database size result         checkdb_type         data_collection_timestamp completion_time last_good_dbcc

SH1-DBS-D01 ASPState 866 NULL                  NULL         NULL           NULL                  2020-04-14 22:29:33.970


Wednesday, February 26, 2020 - 5:30:56 AM - Gryph Back To Top (84807)

I've tested this script but it still fails on our server halfway through because it hits an offline database. I ran the query that selects all the databases to put into the table that the DBCC query is ran against and it picks upp offline databases.

The query in question is line 95-99:

SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), DB_NAME(mf.database_id), SUM(mf.size*8)/1024, NULL, NULL, NULL, NULL,NULL
FROM sys.master_files mf
JOIN sys.databases db ON mf.database_id = db.database_id
WHERE mf.state_desc = 'ONLINE' AND db.source_database_id IS NULL
GROUP BY mf.database_id;

This returns all databases on the server no matter if they are online or not. What seems to work is to change the first WHERE variable from:
mf.state_desc = 'ONLINE'
to:
db.state = 0
like so:

SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), DB_NAME(mf.database_id), SUM(mf.size*8)/1024, NULL, NULL, NULL, NULL,NULL
FROM sys.master_files mf
JOIN sys.databases db ON mf.database_id = db.database_id
WHERE db.state = 0 AND db.source_database_id IS NULL
GROUP BY mf.database_id;


Thursday, February 06, 2020 - 7:15:57 PM - Alejandro Cobar Back To Top (84231)

The fix was really simple:

Within the code of the SP "Simple_CHECKDB", the code that invokes the execution of the DBCC command that looks like this "EXEC ('DBCC CHECKDB('[email protected]+') WITH TABLERESULTS');", has been replaced by this "EXEC ('DBCC CHECKDB(['[email protected]+']) WITH TABLERESULTS');".

The reason is that if the name of the database has a "." in the name, like "xxxx.yyyy" then this instruction fails. Thanks to Carla Romere who noticed this while using the script!!!


Tuesday, February 04, 2020 - 11:01:10 AM - Alejandro Cobar Back To Top (84147)

I'm so glad to hear that everything went perfectly fine!

With your confirmation, I'm going to send the updated code so it can be formally updated within the article itself.


Tuesday, February 04, 2020 - 8:53:39 AM - Carla Back To Top (84145)

Alejandro, your fix worked beautifully!!! Thank you so much for that :-)

Carla


Tuesday, January 28, 2020 - 12:20:30 PM - Carla Romere Back To Top (84018)

Thank you so much for that!!! I've made the changes and the script will run again this weekend. I will post back next Tuesday and let you know if it works for me. I really appreciate your assistance!!


Tuesday, January 28, 2020 - 11:36:09 AM - Alejandro Cobar Back To Top (84016)

Sorry for the late reply Carla...

I was able to reproduce the error using a dummy database with the exact same name that you gave me, and I was able to fix it. Please make the following modification in the Simple_CHECKDB stored procedure:

Wherever you see "EXEC ('DBCC CHECKDB('[email protected]+')", replace it with "EXEC ('DBCC CHECKDB(['[email protected]+'])" (there should be 4 occurrences).

Wherever you see "EXEC ('DBCC DBINFO('[email protected]+')", replace it with "EXEC ('DBCC DBINFO(['[email protected]+'])" (there should be only 1 occurrence).

With that, all your cases should be covered :).

*I'm going to make the formal fix for this one and will submit it so that the code is up-to-date within the article.


Tuesday, January 21, 2020 - 12:40:46 PM - Carla Romere Back To Top (83912)

Thanks for getting back to me, Alejandro. The name of the database is [ZollData.StaticData]. I don't know if the "period" in the middle of the name would throw it off or not. It's a third party database and I can't control the name.


Tuesday, January 21, 2020 - 1:47:04 AM - Alejandro Cobar Back To Top (83901)

Hi Carla,

I'm extremely happy to hear that you found my solution helpful!!!

The only thing I can think of right now is if you give me the exact name of the database that is not working for you. Perhaps I'm able to reproduce the error from my end and I can provide to you a fix.

Best regards.


Monday, January 13, 2020 - 10:20:37 AM - Carla Romere Back To Top (83743)

I am using your script on all of my sql servers and 99% of them work perfectly and I love the script!

However, I have one database on one server that comes up with NULL values all the way across the CheckDB table. All of the other databases on that server check successfully. All of the other SQL Servers check all databases successfully. I can run DBCC CHECKDB against this one database and it completes successfully. I just can't figure out why Simple_CheckDB doesn't run against it. I get the following error twice in the output of the job completion:

[SQLSTATE 01000] (Message 2528)  Incorrect syntax near '.'.

[SQLSTATE 42000] (Error 102)  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Without a line number or more information, I am not able to figure out what is failing. I have tried replacing the Simple_CheckDB stored procedure also to no avail. Any suggestions?


Wednesday, March 13, 2019 - 1:40:36 PM - Alejandro Cobar Back To Top (79285)

Thanks for the heads-up Sreekanth!

I will definitely make the update and will request the respective modification to the tip.

Regards,

-Alejandro


Tuesday, March 12, 2019 - 2:12:22 PM - Sreekanth Bandarla Back To Top (79275)

Nice script Alejandro. A small suggestion, this proc will fail to calculate size of multi tera byte databases. "mf.size*8" should be casted to BIGINT in the select statement to avoid arithmatic overflow errors.


Tuesday, September 25, 2018 - 11:24:59 AM - Max Back To Top (77722)

Alejandro, great script. Very handy. Thanks for sharing!



download





Recommended Reading

Using DBCC PAGE to Examine SQL Server Table and Index Data

Error 601: Could not continue scan with NOLOCK due to SQL Server data movement

Execute SQL Server DBCC SHRINKFILE Without Causing Index Fragmentation

SQL Server DBCC CHECKDB Overview

SQL Server Database Corruption and Impact of running CHECKDB repair with allow data loss








get free sql tips
agree to terms