In the March issue of Windows NT Magazine, I explored the options available for integrating SQL Server with Windows NT's Performance Monitor, a tool that can provide a comprehensive overview of your SQL Server system's behavior (see "7 Tips for Highly Effective Monitoring"). I examined five predefined counters that track information when you start Performance Monitor from the SQL Server folder or group, and I looked at some common misunderstandings about using them.
You can also use Performance Monitor to track the size of your SQL Server database transaction logs and set up alerts to notify the server before the transaction log fills, thus averting problems that might otherwise seriously affect users and applications. But before you can monitor logs and set up alerts, you need to understand how transaction logs work.
SQL Server Transaction Logs
Every SQL Server database has a transaction log, in which SQL Server records every database change. SQL Server writes every row that you insert or delete to the log. When you update a row, SQL Server logs both the old and new values. SQL Server also records transactions such as creating new objects, changing permissions, adding users, and allocating space for tables. After a transaction log fills completely, you can't make any more changes to the database. As a result, available space in the transaction log is a critical resource.
You determine the size of your transaction log when you create your database. You can create a database that puts the data and the log on separate devices or on the same device. When the data and log are on one device, they compete for the same space. For example, suppose you are creating a 100MB database. You can create the database so that it reserves 80MB for data (including any indexes on the data) and 20MB for transaction log records. Or, you can create the database so that it reserves 100MB for either data or log records.
Most production application databases separate the transaction log from the data. Separating the data and the log provides several advantages. SQL Server can perform incremental backups that capture only those transactions processed since the last backup, instead of backing up the entire database. In addition, SQL Server can back up the transaction log even if the device containing the data has failed, providing up-to-the-minute recovery. This setup improves performance and lets you manage space more efficiently. If the database runs out of space, the generated error message will tell you whether the data storage area or the transaction log is full. If you combine the data and log, you won't know whether the data storage area or the transaction log is full.
When you keep the data and log separate and you run out of space for data, you'll get a Msg 1105, Level 17, State 1 error message stating that SQL Server can't allocate space because the default segment is full. When you run out of space for the transaction log, you'll get a Msg 1105, Level 17, State 2 error message stating that SQL Server can't allocate space because the log segment is full.
These two situations require different responses. If your data space is full, you might have acquired more than the anticipated number of product orders or new customers. You can archive the older data or increase the size of your database. If your log space is full, you can simply back up the transaction log using the NO_LOG option of the DUMP TRAN command. You can read about these features in Microsoft's SQL Server Books Online (BOL) documentation.
If the log fills up too quickly or too often, you need to tune SQL Server. For example, you can write queries, particularly update operations, to use less log space. You can use the SELECT INTO operation to move data from one table to another or the bulk copy program (bcp) utility to bulk-load data from an external file without excessive logging. Microsoft's BOL documentation and Knowledge Base articles can provide more information about performing these tasks.
Ideally, you need to clear the transaction log often enough to prevent it from filling completely. How fast the log fills depends on the size and frequency of your transactions. During the development and testing cycle of an application, you can determine a manageable log size. After you determine the ideal interval for backing up and clearing the transaction log, you can define a SQL Executive task to automatically run the DUMP TRAN command on a predefined schedule (e.g., once a day or every hour).
Monitoring Transaction Log Space
Performance Monitor can help you determine the ideal frequency of transaction log dumps by tracking transaction log growth. As Screen 1 shows, you can use four counters to monitor SQL Server logs. The Instance box lists the databases that have a separate transaction log.
One of the most valuable counters is Max Log Space Used. The Max Log Space Used counter retains the highest percent of allocated log space the transaction log uses. You can determine the ideal transaction log size by monitoring the Max Log Space Used counter as you adjust the size of the transaction log. Choose a transaction log size for which the Max Log Space Used counter never exceeds 75 percent or 80 percent during normal expected usage.
Setting Alerts
You use two types of alerts to alleviate potential problems before they affect users and applications. You set up Performance Monitor to alert SQL Server when a counter's value crosses a specific threshold. You use Performance Monitor's Alert View to define Performance Monitor alerts. You set up SQL Executive alerts to define the actions SQL Server takes to correct the problem. You use SQL Enterprise Manager's (EM's) Alert Manager to define SQL Executive alerts. You can use both types of alerts, for example, to set up a mechanism that will automatically truncate the transaction log if it fills more than 80 percent of its allocated space. Setting up this mechanism requires five steps.
1. Create a SQL Executive task. For the SQL Executive to clear out the transaction log, you must define a task to execute the DUMP TRAN command. Using the SQL EM, right-click the SQL Executive icon and select Manage Scheduled Tasks (or select Server, Scheduled Tasks from the SQL EM main menu). Then select New Task. Screen 2 shows the New Task dialog box.
Prev. page
[1]
2
next page 
