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.
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.
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
or the log device is
expanded. Prevent this crash scenario by using
to purge committed transactions or actually dumping the log
to a backup using
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.
(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.)
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:
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.
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.
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
DUMP TRAN <myDB> WITH NO_LOG
DUMP TRAN <myDB> WITH TRUNCATE_ONLY
DUMP TRAN <myDB> TO [DEV = <myDumpDevice> | DISK = <myFileName>].
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.
%System%\cmd.exe /K dbmaint.bat
Using an administration subsystem