Taking a Proactive Approach with SQL Server Agent

By : Frank Garcia Jan 10, 2004

Printer friendly

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts. It is also a Windows NT/2000 based service, manageable from Enterprise Manager or the SQL Service Manager. The SQL Server Agent is a tool that touches on other aspects of administration like "Performance Tuning", "Backup & Recovery", "Database Maintenance", etc.

First, I would like to cover one of the three main components of the SQL Server Agent: "Jobs". Jobs have played an important role in my duties as a full-time DBA. I believe strongly in a proactive approach when dealing with my SQL Servers, hence, "Jobs" has been a proven tool in this approach. These tasks are usually T-SQL scripts, ActiveX or VBS scripts, Windows Commands, Replication scripts, or executables. The advantage with using Jobs is that they can be scheduled, or they can be triggered based on predefined conditions. Upon the condition of a specific task (i.e., failure or success), jobs can be set to run specific logic to handle these conditions. Jobs on average are used for setting up maintenance plans and backups for databases.

One of the problems I have been able to alleviate with Jobs has been log overflows. With SQL 2000, logs can be set to grow to an unlimited size. I have been able to successfully manage this issue by setting up a T-SQL script to truncate and shrink the log file bringing it back to its original state on a regular basis. Here is that script:



Use Master

go

Backup Log [dbname] with truncate_only

go

Use [DBName]

go

DBCC shrinkfile ([name], [fileid])

go



You can find out the [name] and [fileid] of a database by running the following script:



use [DBName]

go

select * from sysfiles



The SQL Agent Job component collaborates with "Alerts" and "Operators" to notify an operator of an event that has occurred on SQL Server. Alerts are predicated on error numbers, severity levels, or performance counters. Performance alerts work well for problems that need to be discovered before they occur.

Another problem that I have encountered as a DBA is database file growth. Since my databases are set to grow to a certain percentage, I needed to set up an alert to let me know when my database would draw close to that threshold. I set up a performance alert that fired off when it reached 80% of that threshold. Here is an example of what I did:

1. Open Enterprise Manager and expand your server. Drill down until you get to SQLServerAgent and select Alerts.
    
2. Select New Alert from the menu.
    
3. Type the name of your alert in the Name box.
    
4. Select SQL Server Performance Condition Alert as type.
    
5. Select SQLServer:Databases under object.
    
6. Select Data File(s) Size (KB) under counter.
    
7. Type a [dbname] under instance.
    
8. Select "becomes equal to" as Alert if counter.
    
9. Type a value into the Value box.
    

The same performance alert can be set up for log overflows, or as a backup to the truncate and shrink script previously mentioned. These counters can be set up for performance as well as for event driven conditions. Alerts can be created to fire on any valid error number including error codes that are user defined. Thus, you can keep track of specific objects in your database(s) or your SQL Server's performance. Remember that "Performance Alerts" are proactive to issues on your SQL Server and "Event Alerts" are useful for issues after they occur.