Microsoft® SQL Server® 2012 Bible

613

Chapter 22: Maintaining the Database

22

Command-Line Maintenance

Database maintenance is normally performed within SQL Server Management Studio or

automated with SQL Server Agent. However, maintenance can be performed from the DOS

command prompt by means of SQLMaint utility. This utility has numerous options that

can perform backups, update statistics, and run DBCC.

This feature will be removed in the next version of SQL Server. Do not use this feature in new development work and

plan to change applications that use this feature. To run SQL Server maintenance plans from the command line, use

the dtexec utility.

You can fi nd specifi c information on SQLMaint and dtexec in SQL Server 2012 Books

Online.

Monitoring Database Maintenance

It’s not enough to simply schedule the tasks; they must be monitored as well. In larger instal-

lations with dozens of SQL Servers spread around the globe, just monitoring the health of

SQL Server and the databases becomes a full-time job. Table 22-1 provides a sample DBA daily

checklist that you can use as a starting point to develop a database monitoring plan.

TABLE 22-1 DBA Daily Checklist

Item S M T W T F S

System Databases Backup

Production User Databases Backup

SQL Agent, SQL Maint, & DTC running

Database Size, Growth, Disk Free Space

Batch Jobs Execute OK

DBCC Jobs Execute OK

SQL Log Errors

Replication Log Agent Running

Replication Distribution Cleanup Job Execute OK

SQL Server Last Reboot

Depending on the complexity and the number of servers, you can manually maintain the

DBA daily checklist with an Excel spreadsheet or track it in a SQL Server table.

c22.indd 613c22.indd 613 7/31/2012 9:24:34 AM7/31/2012 9:24:34 AM

http://www.it-ebooks.info