Microsoft SQL Server DBA Survival Guide --Chapter 25 Chapter 25 Developing a SQL Server Maintenance Plan Areas of Maintenance SQL Server Maintenance Database Maintenance Table/Object Maintenance Windows NT Maintenance Maintenance Checklist Summary by Orryn Sledge Developing a SQL Server maintenance plan is a proactive approach that can help minimize system downtime. In terms of maintenance, SQL Server is no different than your car. Both require preventive maintenance and periodic tune ups. To help you keep SQL Server motoring along, this chapter discusses the types of maintenance that should be performed by a DBA. Areas of Maintenance As a DBA, you should be concerned with four broad areas of maintenance: SQL Server maintenance Database maintenance Table/object maintenance Windows NT maintenance SQL Server Maintenance The following list summarizes the types of maintenance that should be performed at the SQL Server database engine level: Monitor error logs Record configuration information Review the number of concurrent users Manage logins Monitor Error Logs A DBA should frequently review SQL Server's error log. When you review the error log, you should look for messages that do not appear under normal circumstances. Unfortunately, the error log contains more than just error messages. It also contains statements about the status of events, copyright information, and so on. This means that you have to know what to look for when you scan the error log. A good starting point is to look for the following keywords: error : table corrupt level 16 level 17 level 21 Severity: 16 Severity: 17 Severity: 21 NOTE: You can view the error log from the Enterprise Manager or from a text editor. To view the error log from the Enterprise Manager, select Error Log from the Server menu. To view the current error log with a text editor, open the file c:\mssql\log\errorlog. You also can view the last six versions of the error log by opening the corresponding file (errorlog.1, errorlog.2, and so on). The following listing shows a sample error log. Items in bold indicate errors a DBA may want to investigate: 96/04/29 01:35:59.03 kernel Microsoft SQL Server 6.50 - 6.50.201 (Intel X86) Apr 3 1996 02:55:53 Copyright (c) 1988-1996 Microsoft Corporation 96/04/29 01:35:59.03 kernel Copyright (C) 1988-1994 Microsoft Corporation. 96/04/29 01:35:59.04 kernel All rights reserved. 96/04/29 01:35:59.04 kernel Logging SQL Server messages in file `C:\MSSQL\LOG\ERRORLOG' 96/04/29 01:35:59.06 kernel initconfig: number of user connections limited to 15 96/04/29 01:35:59.06 kernel SQL Server is starting at priority class `normal' with dataserver serialization turned on (1 CPU detected). 96/04/29 01:35:59.15 kernel Attempting to initialize Distributed Transaction Coordinator 96/04/29 01:35:59.60 server Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE 96/04/29 01:35:59.62 kernel initializing virtual device 0, C:\MSSQL\DATA\MASTER.DAT 96/04/29 01:35:59.62 kernel Opening Master Database ... 96/04/29 01:35:59.85 spid1 Loading SQL Server's default sort order and character set 96/04/29 01:35:59.97 spid1 Recovering Database `master' 96/04/29 01:36:00.04 spid1 Recovery dbid 1 ckpt (7958,25) oldest tran=(7958,24) 96/04/29 01:36:00.11 spid1 1 transactions rolled forward 96/04/29 01:36:00.22 spid1 Activating disk `dev1' 96/04/29 01:36:00.26 kernel udopen: operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\MSSQL\DATA\dev1.DAT 96/04/29 01:36:00.27 kernel udactivate (primary): failed to open device C:\MSSQL\DATA\dev1.DAT for vdn 4 96/04/29 01:36:00.27 spid1 Activating disk `MSDBData' 96/04/29 01:36:00.28 kernel initializing virtual device 127, C:\MSSQL\DATA\MSDB.DAT 96/04/29 01:36:00.28 spid1 Activating disk `MSDBLog' 96/04/29 01:36:00.28 kernel initializing virtual device 126, C:\MSSQL\DATA\MSDBLOG.DAT 96/04/29 01:36:00.29 spid1 Activating disk `salesdat' 96/04/29 01:36:00.29 kernel udopen: operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\MSSQL\DATA\salesdat.DAT 96/04/29 01:36:00.30 kernel udactivate (primary): failed to open device C:\MSSQL\DATA\salesdat.DAT for vdn 1 96/04/29 01:36:00.33 spid1 Activating disk `salesdat2' 96/04/29 01:36:00.33 kernel udopen: operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\MSSQL\DATA\salesdat2.DAT 96/04/29 01:36:00.34 kernel udactivate (primary): failed to open device C:\MSSQL\DATA\salesdat2.DAT for vdn 3 96/04/29 01:36:00.34 spid1 Activating disk `saleslog' 96/04/29 01:36:00.35 kernel udopen: operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\MSSQL\DATA\saleslog.DAT 96/04/29 01:36:00.36 kernel udactivate (primary): failed to open device C:\MSSQL\DATA\saleslog.DAT for vdn 2 96/04/29 01:36:00.39 spid1 server name is `OTS' 96/04/29 01:36:00.44 spid1 Recovering database `model' 96/04/29 01:36:00.46 spid1 Recovery dbid 3 ckpt (338,0) oldest tran=(339,0) 96/04/29 01:36:00.54 spid1 Clearing temp db 96/04/29 01:36:03.24 kernel Read Ahead Manager started. 96/04/29 01:36:03.29 kernel Using `SQLEVN60.DLL' version `6.00.000'. 96/04/29 01:36:03.38 kernel Using `OPENDS60.DLL' version `6.00.01.02'. 96/04/29 01:36:03.39 kernel Using `NTWDBLIB.DLL' version `6.50.201'. 96/04/29 01:36:03.48 ods Starting SQL Mail session... 96/04/29 01:36:05.34 kernel udread: Operating system error 6(The handle is invalid.) on device `C:\MSSQL\DATA\salesdat.DAT' (virtpage 0x01000018). 96/04/29 01:36:05.54 ods Using `SSNMPN60.DLL' version `6.5.0.0' to listen on `\\.\pipe\sql\query'. 96/04/29 01:36:05.60 spid12 Error : 840, Severity: 17, State: 2 96/04/29 01:36:05.60 spid12 Device `salesdat' (with physical name `C:\MSSQL\DATA\salesdat.DAT', and virtual device number 1) is not available. Please contact System Administrator for assistance. 96/04/29 01:36:05.60 spid12 Buffer 8a24e0 from database `sales' has page number 0 in the page header and page number 24 in the buffer header 96/04/29 01:36:05.73 spid12 Unable to proceed with the recovery of dbid <6> because of previous errors. Continuing with the next database. 96/04/29 01:36:05.73 kernel udread: Operating system error 6(The handle is invalid.) on device `C:\MSSQL\DATA\dev1.DAT' (virtpage 0x04000018). 96/04/29 01:36:05.74 spid12 Error : 840, Severity: 17, State: 2 96/04/29 01:36:05.74 spid12 Device `dev1' (with physical name `C:\MSSQL\DATA\dev1.DAT', and virtual device number 4) is not available. Please contact System Administrator for assistance. 96/04/29 01:36:05.75 spid12 Buffer 8a24e0 from database `dev1' has page number 0 in the page header and page number 24 in the buffer header 96/04/29 01:36:05.76 spid12 Unable to proceed with the recovery of dbid <7> because of previous errors. Continuing with the next database. 96/04/29 01:36:05.80 spid11 Recovering database `msdb' 96/04/29 01:36:05.83 spid10 Recovering database `pubs' 96/04/29 01:36:05.89 spid11 Recovery dbid 5 ckpt (3587,11) oldest tran=(3587,10) 96/04/29 01:36:05.95 spid10 Recovery dbid 4 ckpt (778,31) oldest tran=(778,30) 96/04/29 01:36:06.02 spid11 1 transactions rolled forward in dbid 5. 96/04/29 01:36:06.11 spid10 1 transactions rolled forward in dbid 4. 96/04/29 01:36:06.57 spid1 Recovery complete. 96/04/29 01:36:06.75 spid1 SQL Server's default sort order is: 96/04/29 01:36:06.75 spid1 `nocase' (ID = 52) 96/04/29 01:36:06.76 spid1 on top of default character set: 96/04/29 01:36:06.76 spid1 `iso_1' (ID = 1) 96/04/29 01:36:07.11 spid1 Launched startup procedure `sp_sqlregister' 96/04/29 01:36:07.42 ods Error : 17903, Severity: 18, State: 1 96/04/29 01:36:07.42 ods MAPI login failure. 96/04/29 01:36:07.44 ods Error : 17951, Severity: 18, State: 1 96/04/29 01:36:07.44 ods Failed to start SQL Mail session. TIP: Use the Windows NT FINDSTR.EXE utility to search for text patterns in the error logs. (For the UNIX folks, FINDSTR.EXE is NT's equivalent of GREP.) This utility can help automate the process of scanning the log for errors. The following example shows how to scan the error log for the keyword error : C:\mssql\log>findstr /i /n /c:"error :" errorlog Following is some sample output: C:\mssql\log>findstr /i /n /c:"error :" errorlog 34:95/09/16 11:24:20.42 ods Error : 17903, Severity: 18, State: 1 36:95/09/16 11:24:20.43 ods Error : 17951, Severity: 18, State: 1 53:95/09/16 11:24:35.53 ods Error : 17903, Severity: 18, State: 1 55:95/09/16 11:24:35.54 ods Error : 17951, Severity: 18, State: 1 Record Configuration Information Two types of configuration information should be frequently generated and saved: Device Allocation Information and SQL Server Configuration. Device Allocation Information If you have to create a lost or damaged device to restore a database from a backup, you must know the size and type of device used by the database. (For example, was the log on the same device as the database or was it on a different device?) If this information is used to re-create a lost device, it is important to remember that device fragments must be re-created in the same order as they were originally created. Use the following query to generate device allocation information: select b.name `db_name',a.segmap `fragment type', a.size `fragment size' from master..sysusages a, master..sysdatabases b where a.dbid = b.dbid Following is sample output: db_name fragment type fragment size ------------------------------ ------------- ------------- master 7 1536 master 7 7168 model 7 512 msdb 3 1024 msdb 4 1024 pubs 7 512 pubs 7 1024 sales 3 10240 sales 4 2560 sales 4 2048 tempdb 7 1024 Following is an explanation for fragment type: 3 Data device 4 Log device 7 Log and data are on the same device Any other values are user-defined segments. Fragment size is displayed in 2K blocks (512 = 1M). TIP: Use ISQL to generate device allocation information to a text file and then save the file as part of your nightly backup routine. The following example uses an input file named device.sql that contains the SQL statement to generate device information. The information is saved in the file device_configure.txt. Following is the device.sql statement: select b.name `db_name',a.segmap `fragment type', a.size `fragment size' from master..sysusages a, master..sysdatabases b where a.dbid = b.dbid go Following is the ISQL statement: isql -U sa -P -i device.sql -o device_configuration.txt SQL Server Configuration When you are unable to start SQL Server, server configuration information may help Microsoft's technical support group get you back up and running. Use the system procedure sp_configure to generate a list of configuration information, as in the following example: exec sp_configure Following is the output: name minimum maximum config_value run_value -------------------- ----------- ----------- ------------ ----------- allow updates 0 1 0 0 backup buffer size 1 10 1 1 backup threads 0 32 5 5 cursor threshold -1 2147483647 -1 -1 database size 1 10000 2 2 default language 0 9999 0 0 default sortorder id 0 255 52 52 fill factor 0 100 0 0 free buffers 20 524288 204 204 hash buckets 4999 265003 7993 7993 language in cache 3 100 3 3 LE threshold maximum 2 500000 200 200 LE threshold minimum 2 500000 20 20 LE threshold percent 1 100 0 0 locks 5000 2147483647 5000 5000 logwrite sleep (ms) -1 500 0 0 max async IO 1 255 8 8 max lazywrite IO 1 255 8 8 max worker threads 10 1024 255 255 media retention 0 365 0 0 memory 1000 1048576 4096 8300 nested triggers 0 1 1 1 network packet size 512 32767 4096 4096 open databases 5 32767 20 20 open objects 100 2147483647 500 500 priority boost 0 1 0 0 procedure cache 1 99 30 30 RA cache hit limit 1 255 4 4 RA cache miss limit 1 255 3 3 RA delay 0 500 15 15 RA pre-fetches 1 1000 3 3 RA slots per thread 1 255 5 5 RA worker threads 0 255 3 3 recovery flags 0 1 0 0 recovery interval 1 32767 5 5 remote access 0 1 1 1 remote login timeout 0 2147483647 5 5 remote query timeout 0 2147483647 0 0 resource timeout 5 2147483647 10 10 set working set size 0 1 0 0 show advanced option 0 1 1 1 SMP concurrency -1 64 0 1 sort pages 64 511 64 64 spin counter 1 2147483647 10000 0 tempdb in ram (MB) 0 2044 0 0 user connections 5 32767 20 20 TIP: Use ISQL and sp_configure to save configuration information to a text file and then save the file as part of your nightly backup routine. The following example creates a file named sp_configure.txt that contains configuration information: isql -U sa -P -Q"sp_configure" -o sp_configure.txt Review the Number of Concurrent Users It is a good idea to periodically monitor the number of concurrent user connections. Doing so can prevent a surprise phone call from a user complaining that he or she cannot log in to the system because the maximum number of user connections has been exceeded. I recommend using the threshold feature of the Performance Monitor to track the number of active connections. If the threshold is exceeded, you can have an e-mail notification sent to the DBA. Manage Logins As a DBA, you should periodically review who has access to SQL Server. In large organizations, people frequently change jobs. This means that you may have several SQL Server accounts that are not actively being used. You should inactivate these accounts to prevent unauthorized access to SQL Server. Database Maintenance The following list summarizes the types of maintenance that should be performed at the database level: Back up database and transaction log Test your backup strategy Run essential DBCC commands Audit database access NOTE: Several of the tasks discussed in this chapter can be automated through the Database Maintenance Wizard included with SQL Server 6.5. For more information about the Database Maintenance Wizard, see Chapter 26, "Automating Database Administration Tasks." Back Up Database and Transaction Log To ensure database recovery, it is essential to frequently back up the database and transaction log. Devise a backup strategy that meets your needs and then periodically review this strategy to ensure that it satisfies your backup requirements (see Chapter 14, "Backups," for more information). Test Your Backup/Recovery Strategy Many DBAs back up SQL Server on a frequent basis, but only the good DBAs actually test their backup strategy by simulating database recovery. You should frequently test the integrity of your backups by actually performing a database recovery (see Chapter 15, "Recovery," for more information). Try to cover all the scenarios: dead server, lost drives, corrupt database, and so on. Do not put yourself in the position of having to be the one to tell the CEO that your backup strategy didn't work. Run Essential DBCC Commands It is important to frequently run key DBCC commands--and, if possible, to run the DBCC commands before you run your backup. These essential DBCC commands alert you to logical and/or physical errors. The reason you want to run these commands before you back up your database is that you may be unable to restore a database if it is corrupt, thus making your backup useless. TIP: Do not forget to include the master database in your list of databases inspected by DBCC. NOTE: Several of the preventive maintenance DBCC commands can be automatically scheduled through the Database Administration Wizard (see Chapter 26, "Automating Database Administration Tasks," for more information about this wizard). Following is a list of DBCC commands that should be run on a frequent basis. (Refer to Appendix E for a complete explanation of these commands.) DBCC CHECKDB checks all tables and indexes in a database for pointer and data page errors. DBCC NEWALLOC checks data and index pages for extent structure errors. DBCC CHECKCATALOG ensures consistency among system tables in a database. TIP: Use the @@error global variable to help automate nightly DBCC routines. Following is an example: dbcc checkdb(pubs) if @@error <> o run some error reporting routine (such as e-mail notification) Audit Database Access You should periodically perform a review of who has access to your production databases and what type of rights they possess. Doing so can prevent unauthorized access to production data. Table/Object Maintenance The following list summarizes the types of maintenance that should be performed at the table/object level: Use UPDATE STATISTICS Monitor the record count Audit object permissions Use UPDATE STATISTICS Keeping the statistics of an index up to date is crucial for maintaining performance. Frequently issue the UPDATE STATISTICS command for tables that contain indexes subject to frequent data modifications. TIP: Automate UPDATE STATISTICS by using SQL Server's scheduling feature or by using the Database Maintenance Wizard. See Chapter 26, "Automating Database Administration Tasks," for more information. Monitor Record Count In a transaction-oriented environment, it may be necessary to establish a limit on the number of records that should exist in your tables. Once the limit is exceeded, the records should be archived from the table. Doing so can ensure a consistent performance level. Audit Object Permissions Periodically review the types of permissions (SELECT, INSERT, UPDATE, DELETE, and EXECUTE) that each user has to your production data. Doing so can help prevent security violations. Windows NT Maintenance The following list summarizes the types of maintenance that should be performed at the Windows NT level: Monitor the Windows NT event log Back up the registry Keep the emergency repair disk current Run disk defragmentation utilities Monitor available disk space Monitor CPU and memory usage Monitor the Windows NT Event Log When it comes to monitoring the event log, you should look for two types of errors: system errors and application errors. System errors are hardware and operating-system specific. Examples include network errors, hardware problems, and driver errors. Application errors are those errors associated with the application as well as certain types of SQL Server errors. Examples include connection errors, abnormal termination errors, and database failure errors. Back Up the Registry The registry is vital to the Windows NT operating system. It stores operating system details, hardware information, software information, and user account information. If the registry is damaged, you may be able to restore it from a backup. To back up the registry, use the tape backup software provided with Windows NT or use REGBACK.EXE (REGBACK.EXE is part of the Windows NT resource kit). Keep the Emergency Repair Disk Current Whenever hardware and software configurations change, you should update the emergency repair disk. Use RDISK.EXE to keep your emergency repair disk current. Run Disk Defragmentation Utilities You should periodically run disk defragmentation utilities on your server's hard disks. A high degree of hard disk fragmentation can lead to decreased hard disk performance. An NTFS drive must be checked with a third-party product. A FAT drive can be checked with SCANDISK.EXE. Monitor Available Disk Space It's a good idea to have at least 25 percent of the server's hard disk space not in use. This leaves enough free space for temporary files such as database dumps, BCP imports/exports, script generation, and so on. Monitor CPU and Memory Usage The easiest way to monitor CPU and memory usage is to use the Performance Monitor (for more information on using the Performance Monitor, refer to Chapter 18, "Monitoring SQL Server"). If you see sustained spikes in CPU usage, it may be time to upgrade your CPU or redistribute the workload. Also keep an eye on memory usage and the number of free bytes. Insufficient memory leads to a high number of page faults, which degrades performance. Maintenance Checklist Here's a checklist of items for maintenance. Frequency of Execution Task Daily n Monitor error logs n Back up database and transaction log n Run essential DBCC commands n Use UPDATE STATISTICS n Monitor the Windows NT event log n Monitor CPU and memory usage Weekly n Monitor available disk space Monthly n Test your backup strategy n Monitor record count n Review the number of concurrent users n Manage logins n Audit database access n Audit object permissions As Needed n Record configuration information (*SQL Server configuration and **device allocation) n Back up the registry n Keep the emergency repair disk current n Run disk defragmentation utilities Summary Several types of tasks are required to maintain SQL Server. Many of these tasks can be automated through SQL Server's scheduler and Alert Manager. The next chapter, "Automating Database Administration Tasks," discusses how to automate common DBA tasks. DISCLAIMER To order books from QUE, call us at 800-716-0044 or 317-361-5400. For comments or technical support for our books and software, select Talk to Us. © 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.