Chapter 26
Automating Database Administration Tasks



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.

Task Scheduler

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.

Using the Task Scheduler

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:

  1. Click the Managed Scheduled Tasks toolbar button in the Enterprise Manager. The Manage Scheduled Tasks dialog box appears (see Fig- ure 26.1).
  2. Click the New Task toolbar button to add a task. The New Task dialog box appears.

    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.1.
    The Manage Scheduled Tasks dialog box.

    Figure 26.2.
    The New Task dialog box.



    NOTE: Be sure that you select the appropriate database when running TSQL commands; otherwise, the command may fail.
  3. For this example, you want the stored procedure to run every night at 3:00 a.m. To do this, click the Change button. The Task Schedule dialog box appears.

  4. From the Task Schedule dialog box, enter the corresponding scheduling information and click the OK button (see Figure 26.3).


    Figure 26.3.
    The Task Schedule dialog box.

  5. Because you want to be notified by e-mail when the task is successfully executed, add the notification by clicking the Options button in the New Task dialog box. The Task Options dialog box appears.

  6. For this example, specify an e-mail operator (at this point, you can define a new e-mail operator by selecting the <New Operator> option from the E-mail Operator list box). Select the On Success Write To Windows NT Event Log checkbox (see Figure 26.4). Click OK to save the notification information.


    NOTE: To notify an operator by e-mail, SQL Mail must be running and connected to your e-mail service. Use the SQL Mail status indicator in the Enterprise Manager to validate that SQL Mail is successfully connected.


    Figure 26.4.
    The Task Options dialog box.



    TIP: Use the extended stored procedure xp_sendmail to test whether your e-mail service is properly configured, as in the following example:

    xp_sendmail `recipient_name', `this is a test'
  7. To save the task, click the Add button in New Task 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:

  1. From the Manage Scheduled Tasks dialog box, click the Run Task toolbar button. This action executes the task immediately.

  2. To update the task information, click the Refresh toolbar button. This action should always be done before clicking the Task History toolbar button. SQL Server's task dialog boxes are not automatically refreshed!

  3. To determine whether the task ran successfully, click the Task History toolbar button. The Task History dialog box appears (see Figure 26.5). From this dialog box, you can see that the task was successfully executed.

    Figure 26.5.
    The Task History dialog box.

  4. You also can see that this task was successfully executed by looking at your e-mail and NT's event log (see Figures 26.6 and 26.7).

Figure 26.6.
E-mail notification.


Figure 26.7.
Event log notification.

Extending the Task Scheduler

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)


Alert Manager

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:

Standard 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:

  1. From the Enterprise Manager, click the Manage Alerts and Operators toolbar button. The Manage Alerts and Operators dialog box appears (see Figure 26.9). From the Manage Alerts and Operators dialog box, you can add, delete, and edit alerts and manage operators.

    Figure 26.9.
    The Manage Alerts and Operators dialog box.

  2. From the Operators tab in the Manage Alerts and Operators dialog box, click the New Operator toolbar button. The New Operator dialog box appears.

  3. In the New Operator dialog box, type the ID and e-mail name of the person you want to notify of an alert condition (see Figure 26.10). You also can enter pager information. Click OK to save the operator information.

    Figure 26.10.
    The New Operator dialog box.

  4. Now that you have defined an operator to handle the alert, you must define the alert. Click the Alerts tab in the Manage Alerts and Operators dialog box to make that page active; click the New Alert toolbar button. The New Alert dialog box appears (see Figure 26.11).

    Figure 26.11.
    The New Alert dialog box.

  5. For this example, assume that you don't remember which error number is generated when the database is out of space. Click the Manage Error Messages button next to the Error Number field near the top of the New Alert dialog box. The Manage SQL Server Messages dialog box appears.

  6. With the Manage SQL Server Messages dialog box, you can find, add, delete, and edit error messages. To help you find the corresponding error message, enter the following message text:

    out of space

  7. Click the Find button to list all matching error messages (see Figure 26.12).

  8. You want to base your alert on error number 1105. Highlight the row that contains error number 1105 and click the Select button. The New Alert dialog box reappears, showing the selected error number.

  9. After you select an error number, enter the remaining alert information: alert name, alert definition, response definition, and operators to notify (see Figure 26.13).

    Figure 26.12.
    Finding an error message.


    Figure 26.13.
    Entering alert information.

  10. Click the OK button to save the new alert.

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.

Performance Monitor Alerts

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.

  1. From the Alert Manager dialog box, click the New Alert toolbar button. The New Alert dialog box appears (see Figure 26.15). For this example, you need to create a custom error. This error will be used by SQLALRTR.EXE.

  2. To create a custom alert, click the Manage Error Messages button. The Manage SQL Server Messages dialog box appears.

  3. From the Manage SQL Server Messages dialog box, click the New button. The New Message dialog box appears.

  4. In the New Message dialog box, enter the error number, severity level, and message text. For this example, use error number 50001 (see Figure 26.16). Be sure to select the Always Write To Windows NT Eventlog option; otherwise, the event is not recognized by the Alert Manager. Click the OK button to save the alert.

    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.

  5. Now that you have created a new error number and message, go back to the New Alert dialog box and fill in the remainder of the alert notification (see Figure 26.17). Click the OK button to save the alert.

    Figure 26.17.
    The alert for blocked users.

  6. To define the alert in the Performance Monitor, click the SQL Performance Monitor icon in the Microsoft SQL Server 6.5 (Common) group. The Performance Monitor dialog box appears.

  7. From the Performance Monitor dialog box, click the View Alerts toolbar button. The View Alerts dialog box appears.

  8. From the View Alerts dialog box, click the Add an Alert Entry toolbar button. The Add To Alert dialog box appears.

  9. For this example, select the SQLServer-Locks object and the Users Blocked counter.

  10. In the Alert If box, click the Over button and enter 1 for the alert threshold level. This means that when a user is blocked, an alert will be issued.

  11. From the Run Program on Alert box, enter the SQLALRTR.EXE command. For this example, you want to trigger error number 50001.

  12. To trigger error 50001, use the following syntax in SQL Server 6.5:

    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)]

  13. Use the following syntax in SQL Server 6.0:

    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.

  14. Click the Add button to save the alert (see Figure 26.18).

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.

Business Alerts

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:

  1. Defining the error number and message that corresponds to the alert.
  2. Setting the trigger on the event that executes the error that corresponds to the alert.
  3. Alert notification.

Step 1: Define the Error Number and Message Follow these steps to define the error number and message that corresponds to the alert:

  1. To define a custom error number, click the Manage Messages toolbar button in the Manage Alerts and Operators dialog box. The Manage SQL Server Messages dialog box appears.

  2. From the Manage SQL Server Messages dialog box, click the New button. The New Message dialog box appears.

  3. In the New Message dialog box, enter the error number, severity, and message text you want to associate with the low inventory message (see Figure 26.21). For this example, use error number 50002.

    Figure 26.21.
    Adding a new error message.

  4. To save the error message, click the OK button in the New Message dialog box and click the Close button in the Manage SQL Server Messages dialog box.

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.

  1. In the Manage Alerts and Operators dialog box, click the New Alert toolbar button. The New Alert dialog box appears.

  2. In the New Alert dialog box, enter the alert configuration information. Be sure to use the correct error number (in this example, 50002) for the Alert definition. Also enter the notification message and the operator you want to be notified when this condition arises (see Figure 26.22). Click the OK button to save the alert.

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.

Database Maintenance Wizard

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:

  1. From the Enterprise Manager, click the Database Maintenance Wizard toolbar button. The Database Maintenance Plan Wizard dialog box appears (see Figure 26.24).

    Figure 26.24.
    The Database Maintenance Plan Wizard dialog box.



    NOTE: Do not forget to include the following system databases when using the Database Maintenance Wizard to automate backups and DBCC tasks: master, model, msdb, tempdb (do not include tempdb in your backup plan). These system databases are actual databases, just as the pubs database or any other database used to store information is a database. It is just as important to back up and run DBCC commands for these databases as it is for any other database.

  2. From the Database Maintenance Plan Wizard dialog box, select the Target Database and click the Next button to continue. The About the Data In Your Database dialog box appears (see Figure 26.25).

    Figure 26.25.
    The About the Data In Your Database dialog box.

  3. From the About the Data In Your Database dialog box, select the appropriate responses for Data Volatility and Data Growth. These responses help the wizard develop a strategy that best suits your needs. Click the Next button to continue. The Data Verification dialog box appears (see Fig- ure 26.26).

    Figure 26.26.
    The Data Verification dialog box.

  4. From the Data Verification dialog box, select the appropriate Data Verification Tests and when you want to schedule the verification tests. Following are explanations of the Data Verification tests:


    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.

  5. After selecting the appropriate Data Verification tests and adding the verification to the maintenance plan, click the Next button to continue. The Data Optimization dialog box appears (see Figure 26.27).

    Figure 26.27.
    The Data Optimization dialog box.

  6. From the Data Optimization dialog box, select the appropriate Data Optimizations and when you want to schedule the optimization. Following are explanations of the Data Optimization options:
  7. After selecting the appropriate Data Optimizations and adding the verification to the maintenance plan, click the Next button to continue. The Data Protection dialog box appears (see Figure 26.28).

    Figure 26.28.
    The Data Protection dialog box.

  8. From the Data Protection dialog box, select the appropriate Backup Options (see Chapter 14, "Backups," for more information on backing up the database). Select the Only Do the Backup If option if you want the database to be backed up only if it is free from any errors. Click the Next button to continue. The Data Backup Destination dialog box appears (see Fig- ure 26.29).

    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.

  9. From the Backup Destination dialog box, select the appropriate Backup Destination. From this dialog box, you can specify whether you want to store the backup on disk or on tape. Click the Next button to continue. The When To Run, Who To Notify dialog box appears (see Figure 26.30).


    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.

  10. From the When To Run, Who To Notify dialog box, select the appropriate When To Run the Daily Plan, When To Run the Weekly Plan, and Generated Report options. Click the Next button to continue. The Wizard Complete! dialog box appears (see Figure 26.31).

    Figure 26.31.
    The Wizard Complete! dialog box.

  11. From the Wizard Complete! dialog box, review the plan summary information. If the summary information meets your needs, click the Done button to complete the task. The wizard then asks whether you want to run the plan(s) you have created. If possible, I recommend testing the plan at this point. Doing so helps verify that your backup strategy, e-mail notification, and so on are properly configured.

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.exe

Here 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]]}


Between the Lines

Following are some important notes to remember when automating database administration tasks:

Summary

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.


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.