Problem
One common task as DBA is to run DBCC CHECKDB on a regular basis to detect and correct potential corruption in the database. You can run the command as part of a Transact-SQL script or it can be a part of a Database Maintenance Plan or possibly, even better step a SQL Server Agent job. But running it as a SQL Server Agent job has a side effect, and that is that you probably don’t want to have local job on each sql server instance rather to have a centralized server that runs the same check against each sql server instance.
Two important things to remember about DBCC CHECKDB command:
· It's best to run DBCC CHECKDB before performing a full backup, not after. The reason for this is because if there are any problems with the database, you don't want to back up a database that has corruption.
· Running DBCC CHECKDB is resource intensive task. Because of this, it should only be run on a production server when it is less busy than usual or even better restore the database to another environment and then run DBCC CHECKDB. That way, not only are you validating that your backup files are ok, but you are also validating the database without affecting production
Solution
One solution is to create a SQL Server agent job on centralized server that executes DBCC CHECKDB command on a sql server instance over all user databases and logs the result to the history. The agent job is schedule to run regularly and if any errors occur the job is failed and by using notifications an email is sent to the DBA. The history output for the SQL Server agent job is customized to include the failing database name and the specific error are log too, by using this solution are:
· All errors are documented in the history of the job.
· Monitoring, easy.
· Getting alerts, easy.
· Updating the SQL Server Agent DBCC CHECKDB Maintenance job, easy
· Finding out what take action on, easy, even if you have a lot of sql servers.
The details
Create a SQL Server agent job with a step, of type PowerShell
Notice that the step needs to run as an account that has rights on the servers it accesses. I used a proxy account for that. For easier reading of the sql server agent job history, I use the advanced option "Include step output in history"
The code
Just replace "YourSqlServerInstanceName" below
…
#Reset error output
$sqlerr = $null;
Invoke-Sqlcmd –ServerInstance "YourSqlServerInstanceName" –Database Master -ErrorVariable sqlerr -OutputSqlErrors $true -Query "DECLARE @dbName varchar(100)
DECLARE @sqlCommand nvarchar(300)
DECLARE @errorMsg nvarchar(100)
DECLARE @Return INT
DECLARE myCursor CURSOR FAST_FORWARD FOR
SELECT a.name AS LogicalName from SYS.databases a
WHERE a.database_id > 4 --Only User DB
ORDER BY name DESC
OPEN myCursor
FETCH NEXT FROM myCursor INTO @dbName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @errorMsg = 'DB ['+@dbName+'] is corrupt'
SET @sqlCommand = 'Use ['+@dbName+']'
SET @sqlCommand += 'DBCC CHECKDB (['+@dbName +']) WITH PHYSICAL_ONLY,NO_INFOMSGS, ALL_ERRORMSGS;
EXEC @Return = sp_executesql @sqlCommand
IF @Return <> 0 RAISERROR (@errorMsg , 11, 1)
FETCH NEXT FROM myCursor INTO @dbName
END
CLOSE myCursor
DEALLOCATE myCursor
" -QueryTimeout 0
#Any errors?
if($sqlerr -ne $null)
{
Write-Output ($sqlerr ) | format-table -auto
Throw
}
More reading
