So you are not a database expert. In fact, you just inherited this job of taking care of your company’s database, or maybe more than one. It makes sense to you to keep things running smooth, but you don’t know where to start. Today let’s talk about a helpful tool that ships with SQL Server that can help you implement some frequent management tasks.
If you open SQL Server Management Studio and connect to the SQL Server service, you will see a tree view with different things you can do for your database. If you expand the Management folder in the tree you will see Management Plans. Right click Management Plans and choose Maintenance Plan Wizard. Opening the Wizard you can select a number of different maintenance tasks to help keep your databases working efficiently. The available maintenance tasks for SQL Server 2014 are:
- Check Database integrity
- Shrink Database
- Reorganize Index
- Rebuild Index
- Update Statistics
- Clean Up History
- Execute SQL Server Agent Job
- Back Up Database (Full)
- Back Up Database (Differential)
- Back Up Database (Transaction Log)
- Maintenance Cleanup Task
I’ll be unpacking the meaning of these different tasks for you in upcoming editorials. Today I want to talk about Check Database Integrity.
Your SQL Server database is stored on a permanent disk device such as a hard disk, SAN, SSD, etc. There are a lot of pieces between that storage and your SQL Server application. If something happens it is possible for a single piece of data to not be written correctly. Depending on that piece of data, it can corrupt your database, with varying consequences based on exactly what piece of data is impacted.
When you check the database integrity, SQL server walks through the data structure assuring that none of the links that hold things together are broken. Checking database integrity has the ability to fix some issues if they are detected. Others require additional action. Having your maintenance job check database integrity on a daily basis allows you to review the maintenance execution log after it has completed, and determine if further action is required. You may go for years without ever seeing anything bad in your database integrity check plan results. It does happen often enough that you should have this process in place. It will save you a lot of money in the long run if something does go wrong.
I have only experienced a database integrity failure once in the last 30 years. But having this regular maintenance task made the recovery from the fault quite painless. My recommendation is to create this maintenance plan and run it daily, and review the log after it completes.
Cheers,
Ben