Database maintenance is of primal import in the vast expanse. Today's database architectures represent humanities best model yet as to the working off the cranial storage device. Continued innovation in database technology will lead us to new heights of communication and understanding. Therein lies hope for future generations. Below is my small offering toward that end. You'll find information about (in oredr of complexity) the SQL Server Maintenance Wizard vis-a-vis SQLMAINT, ISQL from DOS command line procedures, a SQL Server admin subsystem, and details about index maintenance. Take what you want and leave the rest.

MS SQL Server includes a rich set of DBCC (Database Consistency Checker) commands and stored procedures to check and fix problems within the SQL Server. There are so many tools it can be difficult for you as the DBA to pinpoint which to use. The exact answer is specific to the application(s) and platform(s) under consideration. In this document the concepts are addressed as generalizations and the examples presented are easily customized to suit the requirements.

Maintenance should be done in batch so it can be scheduled at off hours. Ideally the maintenance process can be completely automated. In reality, there seems to be limitations to that ideal. An automated process is easily forgotten. A forgotten maintenance process is worthless. If possible, train someone to start and monitor the database maintenance process. Short of that, you as the administrator must review the output of the maintenance process(es) that run within the 'batch window' on a timely basis. Even if you have the luxury of an operator or technician to run the maintenance, make it a point to personally review the results daily.

PLEASE NOTE:

Only a few of the less common DBCC commands actually change anything. Most simply do their checking task and generate output to indicate the results of the check. If you don't capture and review the output, there is no benefit to running such commands.

Microsoft SQL Server 6.50 running on Windows NT 4.0 provides a number of potential maintenance environments. The range is from wizard automation (for ease of use) to a subsystem that supports asynchronous multi-processing, interprocess communication, and thread control for distributed database management.

Microsoft SQL Server's SQLMAINT.EXE is an excellent way to automate the maintenance of a moderate sized database. There seems to be some discouragement about using this executable for larger databases, though I have heard of people using SQLMAINT and the Database Mintenance Wizard successfully on multi-gigabyte databases. The flexibility of the utility is limited but there is no excuse for lack of database maintenance given the existence of the Database Maintenance Wizard.

If an application requires different processing for certain maintenance windows, using a command prompt (DOS) script can easily provide customization features to optimize maintenance performance and effectiveness. This is an especially useful strategy if you need to stop and start SQL Server within the batch.

In my view, the most robust option is to create a subsystem for maintenance and general administration of a SQL Server. This requires an administration database with connectivity to all SQL Servers to be maintained, SQL Mail, and a good grasp of the Enterprise Manager's Scheduler. (Its easy to use!)

Details on implementing a maintenance plan using each of these paradigms is presented below. Examples and source code are included where appropriate.

Using the Database Maintenance Wizard

From the Help menu option of SQL Server's Enterprise Manager, select the 'Database Maintenance Wizard' option and follow the prompts. If you're not sure what to do, just take the defaults. It's way too easy

If it's so easy why use anything else? My answer is, Use this method until it doesn't suit your needs. If you're just starting to use SQL Server you may not have the time to fully know the product at install. Running the Maintenance Wizard can give you a fast black box type recovery path. The thing is, when that day comes to open the black box and recover a database, you're going to need an understanding of what the Maintenance Wizard has done for you. And where. Implementing the command line script or admin subsystem will enable you to become familiar with with the SQL Server tool set: i/sql, BCP, SQL Mail, Asymetric Multiprocessing with SQL Server, Interprocess Communication with SQL Server, multithreading with SQL Server, Windows NT Networking, stored procedures, DDL, and more (if you can stand it)!

Be aware that the Maintenance Wizard generated task will not truncate the log in a database. When the log fills, use of the database is halted until the log is truncated as

DUMP TRAN <myDB> WITH NO_LOG 

or the log device is expanded. Prevent this crash scenario by using

DUMP TRAN <myDB> WITH TRUNCATE_ONLY

to purge committed transactions or actually dumping the log to a backup using

DUMP TRAN <myDB> TO [DEV = <myDumpDevice> | DISK = <myFileName>].

A trick I have seen is to use the wizard to schedule the maintenance as a weekly task and the dump as a daily task then to edit the scheduled task of the maintenance to actually run daily. This lets you use the wizard to maintain the database and keep the log from filling.

Using the Command Line (aka DOS)

Consider this script that can be tweeked to do different maintenance procedures on a nightly basis. The script determines the day of the week and includes an example of alternate processing for a specific day in the section where index maintenance is handled. Either of the index maintenance operations include a call to stored procedures that are further discussed in my index maintenance document. The script has no hardcoded variables, so plugging it in to your database should be a snap.

(I have successfully used this script on NT 4.0. On Windows 3.51, some of the functionality is lost. For example, the working directory environment variables are not properly built so you would need to hard code this stuff. Similarly, find.exe from 3.51 and Windows 95 may produce 'unexpected' results (ka-bluey), and the shortcut used to evoke the shell must be replaced with a program group item on an NT 3.51 box.)

Take a look at the script.

If you want to use anything, feel free to cut and paste. Be cautious of unexpected word wrap if you do cut and paste. I tried to fix the scripts when I built the pages, but I may have missed one or two.

Keeping in mind that maintenance operations in master are as necessary but different than the checks required for a user database, notice that the script includes alternate processing based on whether or not the database is the master database. One script to maintain all databases! This provides a good foundation to maintain the entire SQL Server with one call. To do this, we need a driver that can itertively call the maintenance script for each database to be maintained on the SQL Server.

Take a look at a functional driver.

Notice that even the driver does not have any hardcoded passwords. You could put the password and server in the script, but why bother using passwords if your going to save them to ascii files on the system? I prefer to create a shortcut by copying the MS DOS prompt shortcut and adding the /K switch and the name of the driver batch file including its full path. The target for such a shortcut would look something like:

%System%\cmd.exe /K dbmaint.bat

This will cause the command prompt window to open and the usage message for the driver to display. This acts as a reminder of how to start the vmaintenance process. Another possibility would be to select name from sysdatabase and call the maintenance script for each database on the SQL Server. The right solution is the one that keeps all databases available 100% of the time.

To set up the maintenance of a Microsoft SQL Server as presented here, follow these steps:

Once set up, the script will create all other subdirectories and files needed. Become familiar with all files produced by the script. They hold the information you need to keep your database healthy.

Using an administration subsystem

A distributed database environment creates additional obstacles for the administrator. A copy file on a network drive can push the maintenance process beyond the hard won maintenance batch window. What's more, security is often compromised in the name of the need to maintain the database. Likewise, DOS commands can change their behavior depending on the operating system the CPU uses. Just as with any programming language T-SQL provides very straight forward conditional logic and allows the advantage of multithreading. These are some of the reasons to base the maintenance process within SQL Server.

Here is documentation of the setup and useage of the admin subsystem.