MSDE & SQL Database Maintenance
Once your database has been created and being used in a transactional application, database maintenance is one of the most important tasks a "production" DBA will undertake. These tasks will help ensure that the database will continue to function at an appropriate performance level and maintain the database integrity. This article looks at some of these tasks and we also look at how these can be implemented using the MSDE Manager.
The tasks related to the maintenance of a SQL Server database include database backups (both the database and the transaction log), rebuilding indexes, updating statistics, and running integrity checks against the database. These are repetitive tasks that need to be run on an ongoing basis and can be scheduled. We will look at scheduling some of these tasks in a later article.
We will make use of the DBCC statements in this article. These have previously been referred to Database Consistency Checker, but in all relevant Microsoft documentation refers to these statements as Database Console Commands – probably because not all of these statements are used to check database consistency.
Database Backups
This has been covered in an earlier article so we only briefly revise here. It is important to schedule regular backups to avoid data loss. The frequency of these backups and your whole recovery strategy depends on several factors including the level of data loss your organisation is prepared to accept!
Rebuilding Indexes or Updating Statistics
You have two options when deciding on rebuilding (Dropping and recreating) and de-fragmenting indexes. There are advantages and disadvantages of both and Microsoft SQL Server Books Online identify the following points:
"Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built. Another advantage is that with DBCC INDEXDEFRAG, the index is always available, unlike DBREINDEX. A large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index."[1]
As highlighted by books online, your choice may or may not be influenced by the up-time required for your database. Which ever method you choose I would schedule this code to be run when the database is used the least – out of peak time etc.
Database Integrity Checks
To run the database integrity checks you will need to execute the DBCC CHECKDB statement. This checks the data pages for inconsistencies. You have the option of including the indexes, which is more thorough but more time consuming. You also have the option of repairing any minor problems, if a problem is found SQL Server will have to put the object into single user mode before attempting to repair the problem.
I would recommend scheduling these commands to be run on a regular basis. Backing up the database is essential for database recovery. The index option whichever path you take will be important in the performance of queries, which will affect the performance of the clients/applications making use of the application. The database consistency check will highlight any potential problems in your database.
In a later article we will look at how we can schedule the above maintenance statements to be run out of hours when your database is used the least. Running these jobs outside of peak times will ensure that any overhead incurred on the server will have minimal impact on your database users. These jobs can be created and managed using the MSDE Manager provided by Vale Software.
[1] Microsoft SQL Server Books Online
Click here for Product Information on MSDE Manager
Click here for Product Information on Express Agent