by Orryn Sledge
Virtually every organization can reduce administration effort by automating common DBA tasks. SQL Server 6.x provides two tools that can help automate common tasks: Task Scheduler and Alert Manager; SQL Server 6.5 provides the additional tool Database Maintenance Wizard.
The Task Scheduler is an easy-to-use and robust scheduler. In addition to being a scheduler, it includes other useful features, such as a history log and the capability to e-mail or page an operator when an event occurs.
Following are just a few of the types of tasks that can be automated with the Task Scheduler:
TIP: To call BCP from a stored procedure, use the extended stored procedure xp_cmdshell.
Now that you know the types of tasks that can be automated, let's go through a simple example of actually scheduling a task. For this example, assume that you want to schedule a stored procedure that removes any sales data more than seven days old. Also assume that you want the procedure to run on a nightly basis at 3:00 a.m. and that you want to be notified by e-mail that the procedure successfully ran.
The following is a sample procedure:
CREATE PROCEDURE usp_remove_old_data AS
/* remove transactions that are 7 or more days old */
DELETE
FROM sales
WHERE DATEDIFF(dd,sales_date,getdate()) > = 7
NOTE: The SQLExecutive service must be running for the Task Scheduler to work. To determine whether SQLExecutive is running, check the SQL Executive status indicator from the Enterprise Manager. The SQL Service Manager can also be used to check the status of the SQLExecutive. To open the SQL Service Manager, go to Program Manager and click the SQL Service Manager icon in the Microsoft SQL Server 6.5 (Common) group. The color of the traffic light indicates the status of the service.
To schedule the usp_remove_old_data stored procedure, follow these steps:
Enter the following task information: task name, task type, database, and command to execute (see Figure 26.2). From the New Task dialog box, you can execute the following types of commands:
TSQL | Executes Transact SQL statements. Examples include TRUNCATE TABLE authors, UPDATE authors SET au_id = 100, EXEC usp_my_proc, and so on. |
CmdExec | Execute a BAT, EXE, or CMD file. Examples include BCP.EXE, ISQL.EXE, CUSTOM.BAT files, and so on. |
Distribution | Used in conjunction with replication. Enables you to define replication distribution commands. |
LogReader | Used in conjunction with replication. Enables you to define replication log reader commands. |
Sync | Used in conjunction with replication. Enables you to define replication synchronization commands. |
Figure 26.3.
The Task Schedule dialog box.
After a task has been created, it is a good idea to test it by manually executing the task. Follow these steps to manually execute a task:
Figure 26.5.
The Task History dialog box.
Figure 26.6.
E-mail notification.
Figure 26.7.
Event log notification.
In the preceding section, you learned how to schedule a task and how to e-mail an operator when the task is complete. This section builds on what you learned in the previous section. For this example, assume that you want to schedule the same task, but you want the e-mail message to contain the number of rows deleted by the stored procedure.
No problem! However, you do have to shift the e-mail notification logic to the stored procedure rather than leave it with the Task Scheduler. The Task Scheduler can only send a success or failure e-mail message; it cannot return the number of rows deleted, updated, and so on.
To send an e-mail message that contains the number of rows deleted, you must make a few modifications to the usp_remove_old_data stored procedure. The biggest modification is the additional call to the xp_sendmail extended stored procedure. The xp_sendmail command enables you to e-mail a message that contains the number of rows deleted.
The following stored procedure contains the necessary modifications:
CREATE PROCEDURE usp_remove_old_data AS
declare @rows_deleted int
declare @e_mail_message varchar(255)
/* remove transactions that are 7 or more days old */
DELETE
FROM transaction_control
WHERE DATEDIFF(dd,transaction_date,getdate()) > = 7
/* store number of rows deleted to a variable */
SELECT @rows_deleted = @@rowcount
/* build message */
SELECT @e_mail_message = `Numbers of rows removed by usp_remove_old_data = ` +
CONVERT(varchar(20),@rows_deleted)
/* e-mail the results back to the operator */
EXEC master..xp_sendmail `OTS', @e_mail_message
NOTE: Whenever you call an extended stored procedure, you should include the master database in the statement (as in master..xp_sendmail). Otherwise, you must be in the master database to run an extended stored procedure.
Now when the Task Scheduler executes the usp_remove_old_data stored procedure, the number of rows deleted are included in the e-mail message to the operator (see Figure 26.8).
Figure 26.8.
An e-mail message stating the number of rows deleted.
TIP: SQL Server 6.x enables Transact SQL commands to be resolved at run time through the use of the EXEC command. This capability opens up an entire list of DBA functions that can be automated through the use of stored procedures. For example, the following stored procedure uses UPDATE STATISTICS for all tables that contain indexes:CREATE PROCEDURE usp_update_statistics AS
/* declare variables */
declare @table_name varchar(30)
/* declare a cursor that will contain a list of table */
/* names to be updated */
declare idx_cursor cursor
for select distinct a.name
from sysobjects a,sysindexes b
where a.type = `U'
and a.id = b.id
and b.indid > 0
/* open the cursor */
open idx_cursor
/* get the first row from the cursor */
fetch next from idx_cursor into @table_name
/* loop through the rows in the cursor */
while @@fetch_status = 0
begin
/* issue UPDATE STATISTICS */
EXEC ("UPDATE STATISTICS " + @table_name)
/* get next table name */
fetch next from idx_cursor into @table_name
end
/* close the cursor */
deallocate idx_cursor
TIP: After you create this stored procedure, you can schedule it to execute automatically on a recurring basis. This helps to ensure that your index statistics are up to date.
Stranger than Fiction!
The SQL Server 6.x documentation incorrectly shows how to use the EXEC statement to execute a command at run time. The documentation example does not include the concatenation symbol (+) between the command and the variable. Following is the incorrect syntax:
EXEC ("DROP TABLE " @tablename)Following is the correct syntax:
EXEC ("DROP TABLE " + @tablename)
The Alert Manager enables you to define alerts that are executed automatically on the occurrence of an event. When the alert is executed, an operator can be notified by e-mail or pager. An alert also can execute additional tasks, such as calling another transact SQL command or calling an external program in the form of a BAT, EXE, or CMD file. These features enable a DBA to be more proactive to conditions that require attention.
With the Alert Manager, you can create three types of alerts: standard alerts, Performance Monitor alerts, and business alerts.
Following are examples of standard alerts:
Following are examples of Performance Monitor alerts:
Following are examples of business alerts:
Now that you have an understanding of the different types of alerts that can be managed, this section runs through a simple example of how to configure the Alert Manager for a standard alert. For this example, assume that you want to define an alert that notifies an operator by e-mail when the pubs database is out of space.
NOTE: SQLExecutive must be running for the Alert Manager to work.
To create a sample alert that notifies an operator through e-mail, follow these steps:
Figure 26.9.
The Manage Alerts and Operators dialog box.
Figure 26.10.
The New Operator dialog box.
Figure 26.11.
The New Alert dialog box.
out of space
Figure 26.12.
Finding an error message.
Figure 26.13.
Entering alert information.
Congratulations! You just created an alert that will notify an operator when the pubs database is out of space. Figure 26.14 shows the e-mail message the operator receives when the pubs database is out of space.
Figure 26.14.
E-mail notification sent to the operator when the pubs database is out of
space.
Some types of alerts such as high CPU utilization, transaction log almost full, and blocking require the use of the Performance Monitor, SQLALRTR.EXE, and the Alert Manager. Any type of alert that can be created in the Performance Monitor can be passed to the SQL Server Alert Manager. In turn, the Alert Manager can e-mail or page an operator.
To explain how the Performance Monitor interacts with the Alert Manager, the following steps build an alert that notifies an operator when one or more users are blocked.
Figure 26.15.
The New Alert dialog box.
Figure 26.16.
Creating a new error number message.
NOTE: User-defined error messages must use an error number greater than 50000.
Figure 26.17.
The alert for blocked users.
c:\mssql\binn\sqlalrtr /E50001
sqlalrtr -? | -E error number
[-M parameters for error number]
[-S server name to fire alert on]
[-T (use trusted SA connection) | -P SA password]
[-D database name to fire alert from]
[-V severity of error (1 to 25)]
sqlalrtr -E error number
[-M parameters for error number]
[-S server_name]
[-T (use trusted SA connection) | -P SA password]
[-D database name to fire alert from]
[-V severity of error (1 to 25)]
NOTE: The -P and -T parameters of SQLALRTR.EXE are mutually exclusive--using them together will cause an error.
Figure 26.18.
The finished Performance Monitor alert.
NOTE: Current alert settings are discarded when the Performance Monitor is closed unless the settings are manually saved (choose Save Workspace from the File menu).
TIP: The Performance Monitor must be running for the Alert Manager to detect that a threshold has been exceeded. The proper way to automatically load the Performance Monitor during the startup of Windows NT is to run the Performance Monitor as a Windows NT Service (see the Windows NT 3.51 Resource Kit for more information).
This completes the Performance Monitor alert example. Figure 26.19 shows the e-mail message an operator would receive when this alert is triggered. Figure 26.20 shows the alert generated by the Performance Monitor.
Figure 26.19.
The e-mail message sent when the blocked user alert is triggered.
Figure 26.20.
The blocked user alert in the Performance Monitor.
In addition to handling SQL Server errors and thresholds, the Alert Manager can be used to alert operators to business conditions. Suppose that you own a used car dealership and you want to be alerted by e-mail whenever the number of cars on the lot is below 20.
Assume that the number of cars on your lot can be determined by counting the number of records in a table named cars. Also assume that each time a car is sold, it is deleted from the cars table.
The creation of this type of alert consists of three different steps:
Step 1: Define the Error Number and Message Follow these steps to define the error number and message that corresponds to the alert:
Figure 26.21.
Adding a new error message.
Step 2: Set Up the Event that Executes the Error Every time a car is sold, it is deleted from the cars table. This arrangement enables you to use a trigger that checks to see whether the number of cars on hand is below 20. (Remember that triggers are automatically executed when a DELETE, UPDATE, or INSERT event occurs.)
The following code shows how to create a trigger that automatically issues error number 50002 when fewer than 20 cars are on the lot. In turn, the Alert Manager detects error 50002 and automatically sends an e-mail message to the operator.
/* DELETE Trigger Example */
CREATE TRIGGER trg_delete_cars ON dbo.cars
FOR DELETE
AS
/* declare variables */
declare @car_count int
/* count the number of cars on hand */
SELECT @car_count = COUNT(*)
FROM cars
/* If quantity is less than < 20 */
/* issue error 50002 (user defined error message). This will */
/* fire an alert which will notify an operator */
IF @car_count < 20
BEGIN
/* RAISERROR parameter explanation: */
/* 50002 = low inventory message */
/* 16 = severity level (miscellaneous user error) */
/* -1 = error state */
RAISERROR(50002,16,-1)
END
Step 3: Build the Alert Notification Now that you have defined your error number and trigger, you must build the actual alert notification.
Now the low inventory alert is automatically executed whenever the number of cars on hand falls below 20. Figure 26.23 shows the e-mail message that an operator will receive when this alert is triggered.
Figure 26.22.
The low inventory alert.
Figure 26.23.
The low inventory e-mail message.
The Database Maintenance Wizard can automate many of the common tasks a DBA normally performs. Before this wizard was developed, many DBA tasks were script based. With this wizard, however, you can now graphically automate DBCC commands, UPDATE STATISTICS, and other administrative functions. A nice feature of the wizard is its ability to e-mail results to an operator.
The following steps explain how to use the Database Maintenance Wizard:
Figure 26.24.
The Database Maintenance Plan Wizard dialog box.
Figure 26.25.
The About the Data In Your Database dialog box.
Figure 26.26.
The Data Verification dialog box.
NOTE: If possible, you should execute the Check Data Linkage or the Check Index Linkage option only when the database is free from user activity. Ideally, the database should be in single-user mode before you run these options to ensure that the information reported by these commands is accurate. Also, the number of locks generated by these commands can lead to severe blocking and contention for resources if other users are in the database.
NOTE: If possible, you should execute Check Data Allocation or the Check Index Allocation option only when the database is free from user activity. Ideally, the database should be set in single-user mode before you run these options to ensure that the information reported by these commands is accurate.
Figure 26.27.
The Data Optimization dialog box.
Figure 26.28.
The Data Protection dialog box.
Figure 26.29.
The Backup Destination dialog box.
NOTE: The Database Administration Wizard does not provide a facility to back up the database log. Log backups must be manually scheduled or scheduled through the backup utility in the Enterprise Manager.
TIP: When working with a database that requires 24-hour access, you may not be able to perform an entire set of DBCC commands before you back up the database. An alternative to running DBCC commands before backing up is to dump the database, load the dump onto another server, and then run the DBCC commands.
TIP: An alternative to backing up directly to tape is to generate the backup to the hard drive and then use NT's backup software (or some third-party software) to back up the entire hard drive. This way, you have your most recent database dump in two places--on disk and on tape.
Figure 26.30.
The When To Run, Who To Notify dialog box.
Figure 26.31.
The Wizard Complete! dialog box.
Congratulations! You have completed the Database Administration Wizard task. If you need to modify or review the status of the task, you can go to the Manage Scheduled Tasks dialog box in the Enterprise Manager. From this dialog box, you can add, delete, modify, and enable/disable a task (see Figures 26.32 and 26.33).
Figure 26.32.
The Managed Scheduled Tasks dialog box.
Figure 26.33.
The Edit Task dialog box.
NOTE: The Database Administrator Wizard uses sqlmaint.exe to execute common tasks such as backups, DBCC commands, and so on. The utility can also be run from the command line by typing the following:
c:\mssql\binn\sqlmaint.exeHere is a listing of the command-line parameters available with sqlmaint.exe:
SQLMAINT.EXE /?
[{-D database_name -Rpt report_file}
[-S server]
[-U login_ID]
[-P password]
[-To operator_name]
[-CkAI | -CkDBNoldx]
[-CkTxtAI]
[-CkTxtAi]
[-CkCat]
[-UpdSts]
[-Rebldldx free_space]
[-BkUpDb backup_path | -BkUpLog backup_path]
[-BkUpType {TAPE | DISK}]
[-BkUpOnlyIfClean]
[-DelBkUps number_of_weeks]]}
Following are some important notes to remember when automating database administration tasks:
The Task Scheduler, Alert Manager, and Database Maintenance Wizard are three applications
that use the SQLExecutive service. In Chapter 27, "SQL
OLE Integration," you see how the SQLExecutive and OLE technology can further
extend the automation of common DBA tasks.
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.