DCBB CHECKDB is my bi-polar command, on one hand it checks the integrity of our databases (sometimes for hidden corruptions that might hit us long after the corruption happened thus making the restore option complicated) but on the other hand the command is very time and resources consuming.
Over the internet there are many interesting recommendations on how to balance the need of running the DBCC CHECKDB occasionally with limitations of a production environment.
One of the most interesting recommendation I found was to restore a backup of the database to a non-production server and to run the DBCC CHECKDB on it there.
I like this idea a lot and I might get to build an automated procedure that will do just that but, for now, since I lazy I have tried a different approach.
My current approach is based on that I have managed to get a short, two hours, maintenance window once a week at the weekend so I have built a stored procedure that selects all databases with their last DBCC CHECKDB and their size (and order them by last DBCC CHECKDB and their size), then I start running DBCC CHECKDB on my databases from the oldest and biggest.
After each DBCC CHECKDB, before pulling the next database name, I check the DATEDIFF between the time I have started to run the stored procedure to the current time.
if the DATEDIFF is larger than two hours I exit the store procedure.
I have scheduled the first run of the stored procedure for this weekend.
Update on Sunday.
כתיבת תגובה