Chapter 5: Monitoring and Tuning SQL Server
|
|
Nothing is more frustrating and monotonous for a DBA than having to constantly monitor servers. My boss used to tell me, 'More administrators quit being a DBA because the monotony of DBA work.' This chapter focuses on being a proactive administrator, and provides techniques you can use to actually enjoy your weekends without being paged.
Managing Messages
Error messages generated by SQL Server are stored in the sysmessages table in the master database. You can view the messages or add your own by connecting to the server in Enterprise Manager and selecting Manage SQL Server Messages under the Tools menu. You can search for an existing message to modify under the Search tab. The Message Text Contains option performs a like query against all the messages for any message containing a given statement. You can also look for individual errors by typing the exact error number in the Error Number option. Lastly, you can search for all the errors that belong to a given severity group by selecting the error groups.
You can also narrow your search to only user-defined messages or logged messages by checking the corresponding options. After you type your search criteria, click the Find button to list the error messages that match your specifications. Double-click on any error to modify the message. You can also create a new user-defined message by clicking New.
When you create a new message, the error number auto-increments beginning at 50001. Error numbers before 50001 are reserved for the system. The Message Text option is the message that is sent to the client when the error is raised. If you check Always Write To Windows Event Log each time the error is raised, it is also written to the Windows 2000 or NT Application Event Log. The Severity option can be any positive number between 1 and 25. Severity levels 2 through 6 are also reserved. The higher the number, the more severe the message. You can also use the system-stored procedure sp_addmessage to add a stored procedure as shown here:
USE master EXEC sp_addmessage @msgnum = 50008, @severity = 10, @msgtext=N'Timeout expired, please resubmit your query.', @with_log = 'true'
| Note | If you log an error to the Windows 2000 or NT Application Event Log, the error is also logged to the SQL Server error log. |
You can also use parameters in your error message by using the %s option as shown in the following code. If you wish to overwrite an existing error message, use the @replace parameter as shown here:
USE master EXEC sp_addmessage @msgnum = 50008, @severity = 10, @msgtext=N'Timeout expired, please resubmit your query. Your timeout setting is set to %s.', @with_log = 'true', @replace = 'replace'
Error Message Severity Levels
You can create an error message with a severity level of 1 to 25. The higher the severity level, the more serious the error. Messages range from informational on the low end to notifications of hardware failures on the high end. The messages you're concerned with begin at severity level 10. Any error severity of 19 and greater is more serious and will stop the current batch from executing.
Severity Level 10: Informational Message
This group of messages contains information. They are generally not errors and usually contain information about a process completing. For example, messages about index rebuilds fall into this category.
Severity Levels 11-16: Correctable Errors
These errors can be corrected easily by fixing syntax or modifying a command. For example, error 113 is a severity 15 and means that a close comment (*/) was missing.
Severity Level 17: Insufficient Resources
These errors are received when resources are depleted. For instance, if the autogrow feature of SQL Server tries to expand a database, but no room exists on the hard drive, you receive an error with this severity.
Severity Level 18: Nonfatal Internal Error Detected
These are nonfatal errors with some internal software component of SQL Server, such as Query Optimizer. If you receive this error, you keep your connection and your query completes.
Severity Level 19: SQL Server Error in Resource
These errors should hardly ever occur. These are logged when a nonconfigurable option has presented a problem. For example, you may receive an error with this severity if your server has run out of memory.
Severity Level 20: SQL Server Fatal Error in Current Process
These fatal errors may result from an error in the statement. For example, if you call an objectID that is in the process of being deleted, you may receive an error from this group. These errors are rarely seen and generally do not result in corruption of the table or database.
Severity Level 21: SQL Server Fatal Error in Database (dbid) Processes
These errors indicate that an error occurred that affects all processes in your database. This error message may be received if a database could not be restored at startup.
Severity Level 22: SQL Server Fatal Error: Table Integrity Suspect
These errors tell you that possible table or index corruption has occurred. Object corruption has been very rare since SQL Server 7.0, but in the case of corruption, consider restarting SQL Server to clear any cache. Then run a DBCC CHECKDB to see if the corruption problem has spread. Sometimes dropping the object and re-creating it fixes the problem. For example, if your foreign key constraint is corrupt, drop it and rebuild it.
Severity Level 23: SQL Server Fatal Error: Database Integrity Suspect
Again, database corruption is rare in SQL Server 2000. When it does occur, it is normally caused by power outages. You can prevent the problem by adding a UPS device. If you receive an error with a severity code of 23, first restart SQL Server to see if the cause is a problem with the cache or disk. Then perform a DBCC CHECKDB to see the extent of the damage. You may also have to restore the database after encountering this error.
Severity Level 24: SQL Server Fatal Error: Hardware Error
These errors are a result of an I/O error caused by a hardware problem. When you receive one of these errors, you will usually have to restore your database.
Severity Level 25: Fatal Error
These errors indicate that a general fatal database error has occurred. These errors usually cause SQL Server to stop unexpectedly.
Raising an Error
SQL Server raises the system errors automatically and cannot be raised through T-SQL. To raise a user-defined error in your query, you can use the RAISERROR command. The syntax is:
RAISERROR(ERROR #, Severity Level, State)
The state option is any number between 1 and 127. For example, if you want to raise error 50001 as an informational message, you can use the following basic syntax:
RAISERROR (50001,10, 1)
If the error is logged to the Application Event Log, the type of error that Windows 2000 or NT interprets is based on the severity level. Table 5-1 shows you how Windows 2000 or NT interprets the severity levels.
| Severity Level | Event Log Type |
|---|---|
| Below 15 | Informational |
| 15 | Warning |
| Above 15 | Error |
Errors with a severity level of 19 and greater can only be added or raised by a sysadmin. To do this, the admin has to use the WITH LOG clause, which forces the error to be logged to the Application Event Log and SQL Server error log. To raise the same error as before with a severity above 18, use the following syntax:
RAISERROR (50001,20, 1) WITH LOG
| Caution | Any error with a severity level of 20 and greater is considered fatal. The user's connection is broken after the error is displayed. |
xp_logevent
You can also log directly to the Windows NT or Windows 2000 Application Event Log without reporting an error to the client. Do this by using the xp_logevent extended stored procedure. To use the xp_logevent, call it followed by the error message and the type of error. Valid types of messages include informational, warning, and error. For example, the following syntax will output the error number 50001 to the error log and flag it as an error. (The error number 50001 is not from sysmessages, but rather from an arbitrary number above 50000.)
EXEC master.xp_logevent 50001, 'The database ETL process did not complete', 'error'
This would return to the client:
The command(s) completed successfully.
| Note | By default, you must be a member of the sysadmin server role or the db_owner role in the master database to run xp_logevent. You can explicitly grant others permissions to run this procedure. |
| Tip | You can use xp_logevent to raise an alert without sending a message to the client as well. This will require a well-defined alert. |
|
|
