Database maintenance and when is it needed (Part 1: Integrity Checks)

One very common question I get is: “What kind of maintenance does my database need?”

The answer depends on the nature of the database and the workload associated with the database. In this first part, I will answer some frequently asked questions about Integrity Checks:

 

Q1: Why do we check the database integrity?

A1: Because we cannot afford the risk of having damaged databases running in our production environment. The sooner we discover that damage has occurred, the better our chances will be to quickly repair it and minimize the impact.

 

Q2: How do we end up with damaged databases?

A2: Hardware failures, power outages, drivers and many other nasty situations

 

Q3: Aren’t damaged databases reported automatically in the SQL Server ERRORLOG anyway?

A3: Only if you are lucky enough that the SQL Server process actually 'touches' the damaged pages. Otherwise, if the pages are untouched, corruption might exist for a long time in a database before you realize it. Maybe long time enough for your backup sets to be unable to recover the lost data…

 

Q4: How often should we check the database integrity?

A4: Ideally, I would go for a daily basis. You never know when disaster will hit you. This is not possible in most cases, so most administrators go for weekly checks. At the minimum, you can go for monthly checks. Any more than that and you are pushing your luck.

 

Q5: Should I run integrity checks for the system databases too?

A5: Absolutely. The integrity check for all the system databases (master, model, msdb, distribution) should take a few seconds. The TEMPDB database does not need an integrity check, as it gets created from scratch at each SQL Server service restart.

 

Q6: How long do Integrity Checks take?

A6: I will answer that with a question: How big is the database and how powerful is your hardware? Integrity checks put a lot of pressure on the disks, so it could take several hours for a "TB" database. Only way to know the exact execution time is to actually execute it one time. Then in subsequent executions you will know approximately how much time it needs.

 

For more information about integrity checks you can visit this URL: https://technet.microsoft.com/en-us/library/ms176064.aspx

 

Coming next week:

Part 2: Shrink Operations