Database Automation and Maintenance

Article
01/28/2010

Updated : July 19, 2001

Automation and maintenance go hand in hand. You can automate many routine database administration tasks, most of which have to do with maintenance issues, such as backing up databases or running consistency checks. Automation allows you to increase productivity, complete tasks while away from your computer, and more. You can configure the server to monitor processes and user activities, to check for errors, and to alert you when related events occur. If you configure alerts properly, Microsoft SQL Server 2000 can monitor itself and you can focus on other areas of administration. You can also schedule jobs to automate routine administration tasks. You can configure these jobs to run on a onetime basis or on a recurring basis, such as once a week or, say, the third Tuesday of every month.

Using SQL Server Agent

SQL Server Agent is the driving force behind database automation. It's responsible for processing alerts and running scheduled jobs. When alerts are triggered and when scheduled jobs fail, succeed, or complete, you can notify SQL Server operators. Operator notifications are also processed through SQL Server Agent.

Accessing Alerts, Operators, and Jobs

You can use Enterprise Manager to access resources related to SQL Server Agent by completing the following steps:

In Enterprise Manager, access the Management folder on the database server instance you want to work with.
Select the SQL Server Agent entry in the left pane.
You should see entries for Alerts, Operators, and Jobs. Select one of these entries in the left pane to display its properties in the right pane, as shown in Figure 12-1.
Double-click an alert, operator, or job entry to access its associated properties dialog box.

Figure 12-1: Summary entries for alerts provide an overview of each individual alert. You can also access summaries for operators and jobs.

Note: If you've configured replication on the server, you'll see many alerts and jobs that you configure to make it easier to monitor replication. To start these alerts or jobs, you need to enable them and set the appropriate property settings.

Configuring the SQL Server Agent Service

For SQL Server Agent to work properly, you should configure the SQL Server Agent service to run automatically. Start SQL Server Service Manager by double-clicking the SQL Server shortcut on the taskbar or by selecting Start, then Programs, then Microsoft SQL Server, and finally Service Manager. You can then configure the SQL Server Agent service with Service Manager by completing the following steps:

Use the Server selection list to choose an available server or enter the server name.
From the Services selection list, choose SQL Server Agent.
Choose Start to start the service, if necessary.
Choose Auto-Start Service When OS Starts. This will cause SQL Server Agent service to start automatically when the system boots.

Configuring SQL Server Agent

SQL Server Agent has different properties that you can configure to control how the service works. You configure Agent properties through the SQL Server Agent Properties dialog box shown in Figure 12-2. To access this dialog box, right-click the SQL Server Agent entry in Enterprise Manager's Management folder and then select Properties. The following sections of this chapter use a task-oriented approach to explore the most frequently used configuration options.

Configuring SQL Server Agent Startup Account

The startup account used by the SQL Server Agent service determines SQL Server Agent's access permissions. If the startup account doesn't have appropriate permissions, SQL Server Agent won't run properly. In most cases you'll want to use a Microsoft Windows domain account that's a member of the sysadmin role. This ensures that SQL Server Agent can generate alerts, run jobs, and restart services, as necessary.

You set the startup account by completing the following steps:

Click the General tab of the SQL Server Agent Properties dialog box.
Choose System Account to use the built-in system account as the startup account. Otherwise select the This Account option button and then type the username and password of a Windows domain account.

Figure 12-2: Configure SQL Server Agent properties using the available tabs and fields in the SQL Server Agent Properties dialog box.
If the startup account doesn't have appropriate permissions for making connections to SQL Server, access the Connection tab and then select an authentication method. With SQL Server authentication, you'll also need to specify a login account and password.
Click OK.

Setting the SQL Server Agent Mail Profile

SQL Server Agent sends e-mail and pager notifications through the SQL Server Agent service. In order for mail to work properly, you must set the name of the mail profile you want to use for sending these notifications. Type this profile name in the Mail Profile field of the SQL Server Agent Properties dialog box's General tab. For more information on configuring mail, see the section of Chapter 3 entitled "Managing SQL Mail and SQL Server Agent Mail."

Using SQL Server Agent to Restart Services Automatically

You can configure SQL Server Agent to automatically restart the SQL Server and SQL Server Agent services if they stop unexpectedly. Configuring automatic restart of these services is a good idea that'll keep you from getting paged at 3:00 A.M. on a Tuesday morning.

To configure automatic service restart, complete the following steps:

Access the Advanced tab of the SQL Server Agent Properties dialog box.
Select Auto Restart SQL Server If It Stops Unexpectedly. Select Auto Restart SQL Server Agent If It Stops Unexpectedly.
Click OK.

Viewing SQL Server Agent Logs

SQL Server Agent writes output to a separate log file. You can access this log file by completing the following steps:

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.
Right-click SQL Server Agent and then, from the shortcut menu, choose Display Error Log.

Managing Alerts

Using alerts, you can send e-mail, pager, or Net Send alerts when errors occur or when performance conditions are reached. For example, you can configure an alert to send a message when a Log File Is Full error occurs, or when the number of deadlocks per second is more than five. You can also execute a job on an alert event.

Default Alerts

Default alerts are configured when you install SQL Server and when you configure key features, such as replication. The names of alerts configured for a new installation of SQL Server begin with Demo: and include

Demo: Full tempdb An example alert triggered for a full log file in the tempdb database.
Demo: Full msdb log An example alert triggered for a full log file in the msdb database.
Demo: Sev. 19 errors An example alert triggered for an error with a severity of 19, which is the first level of fatal errors. Other alerts handle severity levels 20–25.

The demo alerts are enabled for use but don't have operators configured to receive the alerts. Because the alerts for fatal errors are the most useful, you'll probably want to edit their properties and configure them for use on the server.

The names of alerts configured when you set up replication begin with Replication:. The replication alerts include

Replication: Agent Success Tells you that the replication agent was successful
Replication: Agent Failure Tells you that the replication agent failed
Replication: Agent Retry Tells you that the replication agent failed and is retrying
Replication: Expired Subscription Dropped Tells you that an expired subscription was dropped, which means the subscriber won't be updated anymore
Replication: Subscriber Has Failed Data Validation Tells you that data in the subscriber's subscription couldn't be validated
Replication: Subscriber Has Passed Data Validation Tells you that data in the subscriber's subscription was validated
Replication: Subscriber Reinitialized After Validation Failure Tells you that data in the subscriber's subscription was reinitialized with a new snapshot

The replication alerts are disabled and don't have operators assigned either. So if you want to use these alerts, you'll need to enable them and assign operators.

Creating Error Message Alerts

Error message alerts are triggered when SQL Server generates an error message. You can create an error message alert by completing the steps on the following page.

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.
Select the SQL Server Agent entry in the left pane.
Right-click Alerts and then, from the shortcut menu, choose New Alert. This opens the New Alert Properties dialog box.
Type a short but descriptive name for the alert in the Name field, as shown in Figure 12-3.
In the Type selection list, choose SQL Server Event Alert. You can now set alerts according to the number or severity level of error messages.
To set alerts by error number, choose Error Number and then type an error number in the related field. To search for an error number, click the build button (…) and then use the Manage SQL Server Messages dialog box to find an appropriate message.
To set alerts by severity level, choose Severity and then use the related selection list to choose a severity level that triggers the alert. You'll usually want to configure alerts for severity levels 19–25.
Use the Database Name selection list to choose the database in which the error must occur in order to trigger the alert. To specify all databases on the server, select the (All Databases) option.
To restrict alerts to messages containing specific text strings, type the filter text in the Error Message Contains This Text field.

Figure 12-3: Use the New Alert Properties dialog box to configure new alerts for error messages.
Click Apply and then configure the alert response as explained in the next section of this chapter, "Handling Alert Responses."

Handling Alert Responses

In response to an alert, you can execute SQL Server jobs or notify operators of the alert, or both. To configure the alert response, complete the following steps:

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.
Select the SQL Server Agent entry in the left pane.
Select the Alerts entry in the left pane and then double-click the alert you want to configure. Then click the Response tab, as shown in Figure 12-4.
To execute a job in response to the alert, select Execute Job and then use the related selection list to select a job to execute. If you want to create a new job, choose the (New Job) entry and then configure the job as discussed in the section of this chapter entitled "Scheduling Jobs."
Operators configured to handle alerts and schedule jobs are shown in the Operators To Notify area. The available notification methods depend on how the operator account is configured. You can select E-Mail, Pager, or Net Send notification, or all three.

Figure 12-4: You can respond to alerts by executing jobs or notifying operators of the alert, or both.
Use the Include Alert Error Text In check boxes to specify whether error text is sent with the notification message. By default, error text is sent only with E-Mail and Net Send notifications.
Set an additional message to operators using the Additional Notification Message To Send text box.
Set the delay between responses for subsequent alert notifications using the Delay Between Responses fields labeled Minutes and Seconds.

Tip To limit the number of alert responses triggered, you'll probably want to set a delay response value of five minutes or more.
Click OK to complete the configuration.

Deleting, Enabling, and Disabling Alerts

Deleting an alert removes its entry from the alerts list. Because old alerts may be useful to you (or another DBA) in the future, you may want to disable them instead of deleting them. When an alert is disabled, no alerts are triggered if the related event occurs.

To delete, enable, or disable an alert, complete the following steps:

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.
Select the SQL Server Agent entry in the left pane and then select Alerts.
To enable or disable an alert, double-click its entry in the Alerts list to open the related properties dialog box. Then, in the General tab, select or clear the Enabled check box to enable or disable the alert.
To delete an alert, click it and then press Delete. When prompted to confirm the deletion, choose Yes.

Managing Operators

Operators are special accounts that can be notified when alerts are triggered and when scheduled jobs fail, succeed, or complete. Before operators become available, you need to register them. After you register operators, you can enable or disable them for notifications.

Registering Operators

You register operators by completing the following steps:

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.
Select the SQL Server Agent entry in the left pane.
Right-click the Operators entry in the left pane and then, from the shortcut menu, choose New Operator. This opens the New Operator Properties dialog box shown in Figure 12-5.
Type a name for the operator in the Name field.
Specify E-Mail, Pager, or Net Send accounts (or all three) to notify. If you can't remember the account you want to use, click the build button (...) to display the Contacts list from your address book, and then select the account to use.
Click the Notifications tab to specify existing alerts that the operator should receive (if any). Existing alerts are listed in the Alert Name column. If you find an alert that the operator should receive, select the corresponding check boxes in the E-Mail, Pager, and Net Send columns as appropriate.
Once you've configured the Notifications tab, you can click Send E-Mail to send the operator an e-mail that lists that operator's alert responsibilities.

Tip If you specify a pager account for the operator, you can set a duty schedule for the pager using the Pager On Duty Schedule area's fields and check boxes. This option is helpful if you have operators who should be notified only during working hours. To set default configuration settings for pagers, access the Alert System tab of the SQL Server Agent Properties dialog box.
Click OK to register the operator.

Figure 12-5: Operators can receive alert and job notifications. To associate the operator with multiple users, reference a group e-mail account.

Deleting and Disabling Notification for Operators

When DBAs leave the organization or go on vacation, you may want to delete or disable their associated operator accounts. To do this, complete the following steps:

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.
Select the SQL Server Agent entry in the left pane and then select Operators.
To disable an operator, double-click the operator entry in the right pane. This opens the Operator Properties dialog box. Now click the Notifications tab and then clear Operator Is Available To Receive Notifications.
To delete an operator, click its entry in the right pane and then press Delete. If the operator has been selected to receive alert or job notifications, you'll see the Delete Operator dialog box shown in Figure 12-6. To reassign notification duty, select a different operator using the Re-Assign To selection list and then click Reassign.

Figure 12-6: In the Delete Operator dialog box you can reassign notification duties when deleting an operator registration.

Configuring a Fail-Safe Operator

When things go wrong with notification, operators don't get notified and problems may not be corrected in a timely manner. To prevent this, you may want to designate a fail-safe operator. The fail-safe operator is notified when

SQL Server Agent can't access system tables in the msdb database, which is where operator definitions and notification lists are stored.
All pager notifications to designated operators have failed or the designated operators are off duty (as defined in the pager schedule).

Note: Using the fail-safe operator on pager notification failure may seem strange, but it's the way to go. E-mail and Net Send messages almost always reach their destination—whether someone is watching their mail or sitting at their computer to receive Net Send messages is another matter altogether.

To configure a fail-safe operator, complete the following steps:

Right-click the SQL Server Agent entry in Enterprise Manager's Management folder and then select Properties.
In the SQL Server Agent Properties dialog box, click the Alert System tab and then use the Operator selection list to choose an operator to designate as the fail-safe operator. You can reassign the fail-safe duty by selecting a different operator or disable the feature by selecting (No Fail-Safe Operator).
Use the Notify Using check boxes to determine how the fail-safe operator is notified.
Click Apply.

Scheduling Jobs

Job scheduling is a key part of database automation. You can configure SQL Server jobs to handle just about any database task.

Creating Jobs

You create jobs as a series of steps that contain actions in the sequence in which you want to execute them. When you schedule jobs in conjunction with other SQL Server facilities, such as database backups or data transformation services (DTS), the necessary commands are configured for you. Normally these commands are set as step 1, and all you need to do is set a run schedule for the job. You can add extra steps to these jobs and thus perform other tasks. For example, after importing data through DTS, you may want to back up the related database. In the DTS Wizard, you would schedule the import and then you would edit the associated job in Enterprise Manager to add an additional step for backing up the database. By coordinating the two processes, you ensure that the import operation is completed before starting the backup.

Another reason for editing a job created by another SQL Server facility is to add notifications based on success, failure, and completion of the job. In this way you can notify operators of certain conditions, and you don't have to search through logs to determine whether the job executed properly.

When you schedule jobs to execute for alerts, you configure the entire job process from start to finish. You

Create a job definition
Set steps to execute
Configure a job schedule
Handle completion, success, and failure notification messages

Assigning or Changing Job Definitions

Whether you're creating a new job or editing an existing job, the steps for working with job definitions are the same. Complete the following steps:

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.
Select the SQL Server Agent entry in the left pane and then double-click Jobs.
Existing jobs are shown in the right pane. Double-click a job to access its related properties dialog box, which is essentially the same as the New Job Properties dialog box shown in Figure 12-7.
To create a new job, right-click the Jobs entry and, from the shortcut menu, choose New Job. This displays the New Job Properties dialog box shown in Figure 12-7.
In the Name field, type a descriptive name for the job. The name can be up to 128 characters long. If you change the name of an existing job, the job is displayed with the new name. Any references to the old job name in logs or history files remain the same and aren't modified.
If job scheduling across multiple servers is configured, select the target server. The target server is the server on which the job runs. To run on the currently selected server, select Target Local Server. To run on multiple servers, select Target Multiple Servers and then choose the target servers.

Figure 12-7: Use the General tab of the New Job Properties dialog box to name, describe, and categorize the job.
Job categories help to organize jobs so they can be easily searched and differentiated. The default category is Uncategorized (Local). Use the Category selection list to choose a different category for the job.

Note: Job categories are created and managed through a separate process. To create a new job category or update an existing category, use the techniques described in the section of this chapter entitled "Managing Job Categories."
By default, the current user owns the job. Administrators can reassign jobs to other users. To do this, use the Owner selection list. You can use only predefined logons. If the logon you want to use isn't available, you'll need to create a logon for the account.
Type a description of the job in the Description text box. You can use up to 512 characters.
Click Apply to create the job definition. Then set Steps, Schedules, and Notifications as explained in the following sections.

Setting Steps to Execute

Jobs can have one or more steps. While SQL Server Agent always attempts to execute the Start step, additional steps can be executed conditionally, such as only when the Start step succeeds or fails. You work with steps using the Steps tab in the New Job Properties dialog box, as shown in Figure 12-8. The dialog box displays any existing steps for the job. You can use the fields and buttons in this dialog box as listed on the following page.

Figure 12-8: Using the Steps tab in the New Job Properties dialog box, you can execute steps conditionally and in any sequence you specify.

New Creates a new step.
Insert Inserts a step before the currently selected step.
Edit Allows edits to the selected step.
Delete Deletes the selected step.
Move Step Up/Down Changes the order of the selected step.
Start Step Sets which step is executed first. The green flag icon highlights the start step in the step list.

When you create or edit a step, you see a dialog box similar to the one shown in Figure 12-9. To configure this dialog box, complete the following steps:

Type a short but descriptive name for the step in the Step Name field.

Use the Type selection list to choose a step type. Each step can
- Execute Transact-SQL commands Type Transact-SQL commands in the Command area or load the statements from a Transact SQL script. To load commands from a script, click Open and then select the Transact-SQL script you want to use. The entire contents of the script are then stored with this step.
- Run ActiveX scripts You can write ActiveX scripts in VBScript, JScript, or another active scripting language configured for use on the system. Enter script statements directly into the Command area or load the statements from a script file. Again, the entire contents of the script are then stored with this step, and later changes to the script file aren't updated automatically.
- Execute operating system commands Enter the operating system commands on a separate line, making sure that you specify the full path to commands and in command parameters. Commands can run batch scripts, Windows scripts, command-line utilities, or applications.
  
  Figure 12-9: Set summary information and commands to execute for the step you're configuring using the fields in the General tab of the New Job Step dialog box.
- Pass Transact-SQL commands to replication agents You can script the Distributor, Snapshot, Merge, and Transaction LogReader agents with Transact-SQL commands. To see examples, refer to the existing jobs that are configured to handle replication, distribution, and subscription processes on the server (if available).
Tip Subsequent changes to scripts aren't updated automatically. You'll need to edit the step properties and reload the script file. Additionally, you shouldn't edit existing replication jobs. Instead, modify the replication process as described in Chapter 9, "Configuring Snapshot, Merge, and Transactional Replication."
When executing Transact-SQL commands, use the Database selection list to set the database on which the commands are executed.
Click the Advanced tab as shown in Figure 12-10.

In the On Success Action selection list, set the action to take when the step succeeds. You can
- Go to the next step to continue sequential execution of the job
- Go to a different step to continue execution of the job on a different step
- Quit the job and report success or failure
Figure 12-10: Control the behavior and logging of the step using the fields in the Advanced tab.
By default, Retry Attempts is set to zero and SQL Server Agent doesn't try to execute steps again. You can change this behavior by setting the number of retry attempts and a retry interval. You do this by using the Retry Attempts and Retry Interval (Minutes) fields, respectively. The retry interval is the delay in minutes between retries.
If the job fails on all retry attempts (if any), the action set in the On Failure Action selection list is executed. The available options are the same as those for success.
If desired, configure a file for logging output from Transact-SQL and CmdExec commands. Type the filename and path in the Output File field or use the find file button (…) to search for an existing file. An alternative is to append output to the step history.

Tip You may want to create a central log file for the output of all jobs or only all jobs in a particular category. If you do this, be sure to select the Append option button rather than the Overwrite option button. This ensures that the output file doesn't get overwritten.
Set the login to use when executing commands using the Run As User selection list. By default, commands are run using the current login ID.
Choose Apply to complete the step configuration.

Configuring Job Schedules

You track schedules in the Schedules tab of the New Job Properties dialog box, as shown in Figure 12-11. Jobs can have one or more schedules associated with them, and just as you can enable or disable jobs and their individual steps, you can enable or disable individual schedules. This makes the job scheduling process very flexible. For example, you could set one schedule to execute the job on weekdays at 2 A.M., another to execute the job on Saturday and Sunday at 8 A.M., and another for ad hoc execution at 10 P.M.

Figure 12-11: Track schedules in the Schedules tab of the New Job Properties dialog box. Jobs can have multiple schedules associated with them.

Whether you're creating a new job or editing an existing job, you work with schedules in the Schedules tab as follows:

Create a new schedule Click New Schedule to configure a new schedule.
Edit a schedule Select an existing schedule and then click Edit to view or modify its properties.
Delete a schedule Select an existing schedule and then click Delete to remove the schedule.

You create or edit schedules by completing the following steps:

Click New Schedule to open the New Job Schedule dialog box, or click Edit to open the Edit Job Schedule dialog box. These dialog boxes are essentially the same except for the title. Figure 12-12 shows the New Job Schedule dialog box.

Type a name for the schedule and then select one of the following schedule types:
- Start Automatically When SQL Server Agent Starts Runs the job automatically whenever SQL Server Agent starts.
- Start Whenever The CPU(s) Become Idle Runs the job whenever the CPU is idle. CPU idle time is specified in the Advanced tab of the SQL Server Agent Properties dialog box.
  
  Figure 12-12: Use the New Job Schedule dialog box to set the schedule name and type.
- One Time Runs the job once at the date and time specified in the On Date and At Time fields.
- Recurring Runs the job according to the recurring schedule displayed.
Recurring jobs are the ones that need the most explanation. When you click Change, you'll see the Edit Recurring Job Schedule dialog box shown in Figure 12-13. You can schedule recurring jobs to run on a daily, weekly, or monthly basis.
To run the job on a daily basis, select the Daily option button. Then use the Every Day field to set when the job runs. Daily recurring jobs can run every day, every other day, or every Nth day.

To run the job on a weekly basis, select the Weekly option button. Then configure the job using these fields:
- Every Nth Week(s) Allows you to run the task every week, every other week, or every Nth week
- Day Of Week Sets the day(s) of the week when the task runs, such as on Monday or on Monday, Wednesday, and Friday
To run the job on a monthly basis, select the Monthly option button. Then configure the job using these fields:
- Day N Of Every Nth Month Sets the day of the month and on which months the job runs. For example, if you select Day 15 of every second month, the job runs on the 15th day of alternating months.
- The Nth Day Of Every Nth Month Sets the job to run on the Nth occurrence of a day in a given month, such as the second Monday of every month or the third Sunday of every other month.
Figure 12-13: Configure a recurring schedule in the Edit Recurring Job Schedule dialog box.
Set the Daily Frequency for the daily, weekly, or monthly job. You can configure jobs to run one or more times on their scheduled run date. To run the job once on a given date, select Occurs Once At and then set a time. To run the job several times on a given date, select Occurs Every and then set a time interval in hours or minutes. Afterward, set a start and end time, such as from 7:30 A.M. to 5:30 P.M.
By default, schedules begin on the current date and don't have a designated end date. To change this behavior, select the End Date option button and then use the Start Date and End Date fields to set a new duration for the schedule.
Click OK to close the Edit Recurring Job Schedule dialog box and then click OK again to close the New Job Schedule dialog box, which completes the schedule process.

Handling Notification Messages

Notification messages are generated when a job succeeds, fails, or completes. You can handle these messages in several ways. You can notify operators, log the related event, automatically delete the job, or do all three. To configure notification, complete the following steps:

Access the Notifications tab of the job you want to configure. This tab is shown in Figure 12-14.
You can notify operators by e-mail, pager, or Net Send message. Select the check box for the technique you want to use. Choose an operator to handle the notification. Then choose a notification type. Repeat this process to configure other notification methods.

Figure 12-14: Using the Notifications tab, you can send notification messages to operators, log them in the event log, or use them to automatically delete a job.
To log a particular type of notification message in the event log, select Write To Windows Application Event Log and then select the notification type to log. Usually, you'll want to log failure, so select When The Job Fails.
To delete a job upon notification, select Automatically Delete Job and then choose the notification type that triggers the deletion.
Choose Apply.

Managing Existing Jobs

In Enterprise Manager, you manage jobs with the SQL Server Agent. To do that, complete the following steps:

Access the SQL Server Agent window in the console root. You'll see an entry labeled Jobs.
Select Jobs in the left pane and you'll see existing jobs in the right pane.

You can now double-click a job entry to access its related properties dialog box or right-click a job entry to display a shortcut menu. Key options on the shortcut menu are
- Delete Deletes the job definition. Before deleting a complex job, you may want to create a script that can be used to recreate the job.
- Disable Job Disables the job so it won't run.
- Download If you've configured multiserver administration, use this option to post scheduled job instructions to a target server. Downloading jobs to a target allows you to immediately start execution of a job or to use the job on the target at a later date.
- Script Job Select All Tasks and then choose Script Job to access the Generate SQL Script dialog box. This dialog box enables you to generate a Transact-SQL script that you can use to recreate the job.
- Start Job Starts the selected job if it's not already running.
- Stop Job Stops the selected job if it's running.
- View Job History Displays the Job History dialog box. This dialog box enables you to view summary or detail information on the job execution.

Managing Job Categories

You use job categories to organize jobs into topical folders. When you install SQL Server, default job categories are created automatically. You can add new job categories and change the existing categories at any time.

Working with Job Categories

To create a new job category or update an existing category, complete the following steps:

In Enterprise Manager, access the SQL Server Agent window in the console root.
Right-click the Jobs node. Select All Tasks, and then choose Manage Job Categories. This displays the Job Categories dialog box.
You can delete a category by selecting it and then pressing Delete.
You can view the properties of a category by selecting it and then clicking Properties.
To add categories or to change the properties of a category, follow the steps outlined in the following sections, "Creating Job Categories" or "Updating Job Categories," respectively.

Creating Job Categories

You can create a new job category by completing the following steps:

Access the Job Categories dialog box as explained previously. Click Add to display the New Job Categories dialog box.
Type a name for the category in the Name field, and then select Show All Jobs.
All jobs defined on the current server should now be listed. Add a job to the new category by selecting the corresponding check box in the Member column. Remove a job from the new category by clearing the corresponding check box in the Member column.
Click OK when you're finished.

Updating Job Categories

You can update an existing job category by completing the following steps:

Access the Job Categories dialog box as explained previously. Click Properties to display a properties dialog box.
Select Show All Jobs. All jobs defined on the current server should now be listed.
Add a job to a new category by selecting the corresponding check box in the Member column. Remove a job from the category by clearing the corresponding check box in the Member column.
Click OK when you're finished.

Automating Routine Server-to-Server Administration Tasks

Anytime you deploy multiple SQL Servers or multiple instances of SQL Server within an organization, you'll need a way to handle routine server-to-server administration tasks. For example, if you have a database on one server, you may need to copy or move the database to a different server. SQL Server 2000 allows you to automate routine server-to-server administration tasks using DTS packages. You can run the packages immediately, schedule them to run periodically, or save them for later use.

The server-to-server administration tasks you can automate include

Copying logins from one server to another
Copying scheduled jobs from one server to another
Copying shared stored procedures from one server to another
Copying user-defined error messages from one server to another
Copying or moving user-defined databases from one server to another

The sections that follow explain how you can create DTS packages to automate these administration tasks.

Copying Logins to Another Server

SQL Server uses two types of logins: standard SQL logins and Windows logins. Logins can be assigned default settings, server roles, and database access permissions. Rather than manually recreating login settings each time you want to use the same login on another server, you can copy logins and their settings to a target server. You copy logins with the Transfer Logins Task in DTS Designer as shown in the following steps:

Start Enterprise Manager and then access the server you want to work with.
Right-click the server's Data Transformation Services folder and then select New Package. This displays the DTS Designer window in Enterprise Manager.
In DTS Designer, choose Task and then click Transfer Logins Task. This adds the Transfer Logins Task icon to the DTS Designer window.
Double-click the Transfer Logins Task icon. This displays the Transfer Logins Properties dialog box shown in Figure 12-15.

Figure 12-15: Use the Transfer Logins Properties dialog box to configure the task options. You can select all logins for transfer or logins for selected databases.
In the Source tab, use the Source Server selection list to specify the SQL Server instance that contains the logins you want to transfer. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select Use SQL Server Authentication and then enter the user name and password of a login that's a member of the sysadmin role.
In the Destination tab, use the Destination Server selection list to specify the SQL Server instance to which you want to transfer logins. Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.

Click the Logins tab as shown in Figure 12-16. You can
- Copy all logins to the destination server by choosing All Server Logins Detected At Package Runtime.
- Copy only the logins for the databases you select by choosing Logins For Selected Databases and then selecting the databases to use.
Click OK to close the Transfer Logins Properties dialog box.
To run the package immediately, click the Execute button (the green arrow button located on the menu bar) or choose Package from the menu and then click Execute. If you want to save the package for later use or for scheduling, continue with the remaining steps. Otherwise skip the remaining steps.

Figure 12-16: Use the Logins tab to specify the logins to transfer.
To save the package for later use, choose Package and then click Save. You'll see the Save DTS Package dialog box as shown in Figure 12-17.
Type a name for the package in the Package Name field. The package name should be unique for the target location.
If you're saving the package to SQL Server, you can password-protect the package and prevent unauthorized users from working with it. Type an owner password and a user password in the fields provided. Anyone with the owner password can design, schedule, and execute the package. Anyone with the user password can only schedule or execute the package.

Use the Location field to specify where the package should be saved. The available locations are
- SQL Server Saves as a local package where the package is accessible for use on the designated server.
- SQL Server Meta Data Services Saves to the designated server's repository database, where the package can be shared with other servers through Meta Data Services.
- Structure Storage File Saves as a COM-structured file. You can add packages to the file as long as they have a different package name. You can then copy, move, or e-mail the file to a different location.
- Visual Basic File Saves as a Visual Basic file where the package can be used in Visual Basic programs.
The lower portion of the Save DTS Package dialog box changes according to the location selection. When you save to SQL Server or to SQL Server Meta Data Services, you must specify the target server and authentication information. When you save to a Structure Storage file or a Visual Basic file, you must specify the file name and path.

Figure 12-17: Use the Save DTS Package dialog box to configure the save options for the package.
Click OK to save the package to the specified location. Once you've saved the package, you can run or schedule the package for execution as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

Copying Scheduled Jobs to Another Server

You use scheduled jobs to automate routine administration tasks. If you've already created jobs on one server, there's no reason you can't reuse the jobs on another server. To do this, you would copy the jobs to the target server and then edit the job properties to ensure that they make sense for the target server. For example, if you created a set of jobs to periodically check the Support database and then added custom steps to handle various database states, you could copy these jobs to another server and then edit the job properties to apply the tasks to the Customer database on the target server.

You copy jobs from one server to another server with the Transfer Jobs Task in DTS Designer. To do that, complete the following steps:

Start Enterprise Manager and then access the server you want to work with.
Right-click the server's Data Transformation Services folder and then select New Package. This displays the DTS Designer window in Enterprise Manager.
In DTS Designer, choose Task and then click Transfer Jobs Task. This adds the Transfer Jobs Task icon to the DTS Designer window.
Double-click the Transfer Jobs Task icon. This displays the Transfer Msdb Jobs dialog box shown in Figure 12-18.

Figure 12-18: Use the Transfer Msdb Jobs dialog box to configure the task options. You can select all jobs for transfer or specify jobs to transfer individually.
In the Source tab, use the Source Server selection list to specify the SQL Server instance that contains the jobs you want to transfer. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select Use SQL Server Authentication and then enter the username and password of a login that's a member of the sysadmin role.
In the Destination tab, use the Destination Server selection list to specify the SQL Server instance to which you want to transfer jobs. Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.

Click the Jobs tab as shown previously in Figure 12-18. You can
- Copy all jobs to the destination server by choosing All Jobs Detected At Package Runtime.
- Copy only the jobs you select by choosing Selected Jobs and then choosing the jobs to transfer.
Click OK to close the Transfer Jobs dialog box.
To run the package immediately, click the Execute button (the green arrow button located on the menu bar) or choose Package and then click Execute. If you want to save the package for later use or for scheduling, continue with the remaining steps. Otherwise skip the remaining steps.
To save the package for later use, choose Package and then click Save. As shown previously in Figure 12-17, you'll see the Save DTS Package dialog box.
Type a name for the package in the Package Name field. The package name should be unique for the target location.
If you're saving the package to SQL Server, you can password-protect the package and prevent unauthorized users from working with it. Type an owner password and a user password in the fields provided. Anyone with the owner password can design, schedule, and execute the package. Anyone with the user password can only schedule or execute the package.

Use the Location field to specify where the package should be saved. The available locations are
- SQL Server Saves as a local package where the package is accessible for use on the designated server.
- SQL Server Meta Data Services Saves to the designated server's repository database where the package can be shared with other servers through Meta Data Services.
- Structure Storage File Saves as a COM-structured file. You can add packages to the file as long as they have a different package name. You can then copy, move, or e-mail the file to a different location.
- Visual Basic File Saves as a Visual Basic file where the package can be used in Visual Basic programs.
The lower portion of the Save DTS Package dialog box changes according to the location selection. When you save to SQL Server or to SQL Server Meta Data Services, you must specify the target server and authentication information. When you save to a Structure Storage file or a Visual Basic file, you must specify the file name and path.
Click OK to save the package to the specified location. Once you've saved the package, you can run or schedule the package for execution as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

Copying Shared Stored Procedures to Another Server

Shared stored procedures are stored in the master database. If you've created shared stored procedures on one server, you can transfer them to another server using the Transfer Master Stored Procedures Task in DTS Designer. To do that, complete the following steps:

Start Enterprise Manager and then access the server you want to work with.
Right-click the server's Data Transformation Services folder and then select New Package. This displays the DTS Designer window in Enterprise Manager.
In DTS Designer, choose Task and then click Transfer Master Stored Procedures Task. This adds the Transfer Master Stored Procedures Task icon to the DTS Designer window.
Double-click the Transfer Master Stored Procedures Task icon. This displays the Transfer Master Stored Procedures dialog box as shown in Figure 12-19.
In the Source tab, use the Source Server selection list to specify the SQL Server instance that contains the shared stored procedures you want to transfer. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select Use SQL Server Authentication and then enter the username and password of a login that's a member of the sysadmin role.
In the Destination tab, use the Destination Server selection list to specify the SQL Server instance to which you want to transfer shared stored procedures. Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Figure 12-19: Use the Transfer Master Stored Procedures dialog box to configure the task options. You can select all shared stored procedures for transfer or specify shared stored procedures to transfer individually.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.

Click the Stored Procedures tab as shown previously in Figure 12-19. You can
- Copy all shared stored procedures to the destination server by choosing All Stored Procedures Detected At Package Runtime.
- Copy only the shared stored procedures you select by choosing Selected Stored Procedures and then choosing the stored procedures to use.
Click OK to close the Transfer Master Stored Procedures dialog box.
To run the package immediately, click the Execute button (the green arrow button located on the menu bar) or choose Package and then click Execute. If you want to save the package for later use or for scheduling, continue with the remaining steps. Otherwise skip the remaining steps.
To save the package for later use, choose Package and then click Save. As shown previously in Figure 12-17, you'll see the Save DTS Package dialog box.
Type a name for the package in the Package Name field. The package name should be unique for the target location.
If you're saving the package to SQL Server, you can password-protect the package and prevent unauthorized users from working with it. Type an owner password and a user password in the fields provided. Anyone with the owner password can design, schedule, and execute the package. Anyone with the user password can only schedule or execute the package.

Use the Location field to specify where the package should be saved. The available locations are
- SQL Server Saves as a local package where the package is accessible for use on the designated server.
- SQL Server Meta Data Services Saves to the designated server's repository database, where the package can be shared with other servers through Meta Data Services.
- Structure Storage File Saves as a COM-structured file. You can add packages to the file as long as they have a different package name. You can then copy, move, or e-mail the file to a different location.
- Visual Basic File Saves as a Visual Basic file where the package can be used in Visual Basic programs.
The lower portion of the Save DTS Package dialog box changes according to the location selection. When you save to SQL Server or to SQL Server Meta Data Services, you must specify the target server and authentication information. When you save to a Structure Storage file or a Visual Basic file, you must specify the file name and path.
Click OK to save the package to the specified location. Once you've saved the package, you can run or schedule the package for execution as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

Copying User-Defined Error Messages to Another Server

You use user-defined error messages to create custom error messages for database applications. If you've created user-defined error messages on one server, you can transfer them to another server using the Transfer Error Messages Task in DTS Designer. To do that, complete the following steps:

Start Enterprise Manager and then access the server you want to work with.
Right-click the server's Data Transformation Services folder and then select New Package. This displays the DTS Designer window in Enterprise Manager.
In DTS Designer, choose Task and then click Transfer Error Messages Task. This adds the Transfer Error Messages Task icon to the DTS Designer window.
Double-click the Transfer Error Messages Task icon. This displays the Transfer Error Messages dialog box as shown in Figure 12-20.
In the Source tab, use the Source Server selection list to specify the SQL Server instance that contains the user-defined error messages you want to transfer. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select use SQL Server Authentication and then enter the user name and password of a login that's a member of the sysadmin role.

Figure 12-20: Use the Transfer Error Messages dialog box to configure the task options. You can select all user-defined error messages for transfer or specify user-defined error messages to transfer individually.
In the Destination tab, use the Destination Server selection list to specify the SQL Server instance to which you want to transfer user-defined error messages. Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.

Click the Error Messages tab as shown previously in Figure 12-20. You can
- Copy all user-defined error messages to the destination server by choosing All Error Messages Detected At Package Runtime.
- Copy only the user-defined error messages you select by choosing Selected Error Messages and then choosing the error messages to transfer.
Click OK to close the Transfer Error Messages dialog box.
To run the package immediately, click the Execute button (the green arrow button located on the menu bar) or choose Package and then click Execute. If you want to save the package for later use or for scheduling, continue with the remaining steps. Otherwise skip the remaining steps.
To save the package for later use, choose Package and then click Save. As shown in previously Figure 12-17, you'll see the Save DTS Package dialog box.
Type a name for the package in the Package Name field. The package name should be unique for the target location.
If you're saving the package to SQL Server, you can password-protect the package and prevent unauthorized users from working with it. Type an owner password and a user password in the fields provided. Anyone with the owner password can design, schedule, and execute the package. Anyone with the user password can only schedule or execute the package.

Use the Location field to specify where the package should be saved. The available locations are
- SQL Server Saves as a local package where the package is accessible for use on the designated server.
- SQL Server Meta Data Services Saves to the designated server's repository database where the package can be shared with other servers through Meta Data Services.
- Structure Storage File Saves as a COM-structured file. You can add packages to the file as long as they have a different package name. You can then copy, move, or e-mail the file to a different location.
- Visual Basic File Saves as a Visual Basic file where the package can be used in Visual Basic programs.
The lower portion of the Save DTS Package dialog box changes according to the location selection. When you save to SQL Server or to SQL Server Meta Data Services, you must specify the target server and authentication information. When you save to a Structure Storage file or a Visual Basic file, you must specify the file name and path.
Click OK to save the package to the specified location. Once you've saved the package, you can run or schedule the package for execution as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

Copying or Moving User-Defined Databases to Another Server

You can copy or move user-defined databases from one SQL Server to another using the Copy Database Wizard or through the DTS Designer. I recommend using the Copy Database Wizard. Both processes create a DTS package that you can run or save for later use, but only the wizard makes it easy to manage this complex process. Either way, you can only copy or move user-defined databases. You can't copy or move system databases. Further, you can't copy or move user- defined databases that already exist on the destination server or that are configured for replication with the destination server.

How Are Databases Copied or Moved?

The Copy Database Wizard creates a package that automates all the tasks that you'd have to perform manually to copy or move a database. The copy process follows the steps listed on the following page.

At the beginning of a copy, the wizard attempts to put the database in single-user mode. The wizard can only do this when there are no active sessions in the database.
Once the database is in single-user mode, the wizard detaches the source database, and then, if successful in detaching the database, the wizard copies the database files associated with the database to the destination server.
Once the files are copied to the destination server, the wizard attaches the database on the source and then attaches the database on the destination server.
The wizard then tests the destination database by logging in as the database administrator.

The move process is slightly different:

At the beginning of a move, the wizard attempts to put the database in single-user mode. The wizard can do this only when there are no active sessions in the database.
Once the database is in single-user mode, the wizard detaches the source database, and then, if successful in detaching the database, the wizard copies the database files associated with the database to the destination server.
Once the files are copied to the destination server, the wizard attaches the database on the destination server but doesn't reattach the database on the source server. The database files on the source still exist and could be used to reattach the database. Or you could manually delete the files.
The wizard then tests the destination database by logging in as the database administrator.

Copying or Moving Databases

You use the Copy Database Wizard to copy or move a user-defined database by completing the following steps:

Start Enterprise Manager and then access the server you want to work with. If the database you want to use has active user sessions, you'll need to terminate these sessions before continuing. The wizard won't complete the operation when there are active sessions.
Right-click the source server entry, point to All Tasks, and then choose Copy Database Wizard.
You'll see the Copy Database Wizard dialog box. Read the welcome page and then click Next.
Use the Source Server selection list to specify the SQL Server instance that contains the database(s) you want to copy or move. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select Use SQL Server Authentication and then enter the username and password of a login that's a member of the sysadmin role.
Click Next and then use the Destination Server selection list to specify the SQL Server instance to which you want to copy or move the selected database(s). Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.
Select the user-defined databases to move or copy as shown in Figure 12-21. You can't select system databases, and you can't select databases that already exist on the destination server.

Figure 12-21: Select the user-defined databases to move or copy.

As Figure 12-22 shows, the next dialog box summarizes the database file information for the copy and move operations you're performing. You use the key fields in the dialog box as follows:
- Files Specifies the type of database file.
- Destination Drives Specifies the destination drive on the target server.
- Size Specifies the total disk space the files will use on the destination drives.
- Status Specifies the file status. A green checkmark indicates the files are ready to move or copy. A red X indicates a problem.
  
  Figure 12-22: The Database File Location dialog box provides a summary for the database files you're working with.
- Modify Displays the Database Files dialog box, which you can use to get more detailed file information and to set new source file names and destinations for database files.
- Refresh Refreshes the file and drive usage information.
As shown in Figure 12-23, select the related objects that you want to copy or move with the selected database(s). The objects you can select are: Logins, Shared Stored Procedures, Jobs, and User-Defined Error Messages. All related objects are selected by default. To cancel an object, clear the related check box.

Figure 12-23: Select the related objects that you want to copy or move with the selected database(s).

The option buttons in the Select Related Objects dialog box control which objects of a particular type are copied or moved. Changing the options changes which of the related objects are used and may also cause additional dialog boxes to be displayed in later steps. The options are
- All Logins Detected At Package Runtime Copies all logins to the destination server
- Only Logins Used By The Selected Databases Copies only the logins for the databases you select in a subsequent dialog box
- All Stored Procedures Detected At Package Runtime Copies all shared stored procedures to the destination server
- User-Selected Stored Procedures Copies only the shared stored procedures you select in a subsequent dialog box
- All Jobs Detected At Package Runtime Copies all jobs to the destination server
- User-Selected Jobs Copies only the jobs you select in a subsequent dialog box
- All Error Messages Detected At Package Runtime Copies all user-defined error messages to the destination server
- User-Selected Error Messages Copies only the user-defined error messages you select in a subsequent dialog box
After you configure any special dialog boxes displayed as a result of object selections, you'll have the option of scheduling the DTS package to run. You can
- Run Immediately Run the package now
- Run Once Set the package to run once at a specified date and time
- Schedule DTS Package To Run Later Set a schedule for when the package should run
Click Next and then click Finish to complete the process. If you elected to run the package immediately, SQL Server runs the package. As each step is completed (or fails), the status is updated. If an error occurs, you can double-click its entry to view a detailed description of the error. Errors may halt execution of the package and if they do, you'll need to recreate the package. If you scheduled the package to run later, you can manipulate the package as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

Multiserver Administration

With multiserver administration you use one server to centrally manage alerts and job scheduling for other servers. You centrally manage alerts through event forwarding. You centrally manage job scheduling by designating master servers and target servers.

Event Forwarding

If you have multiple instances of SQL Server running on multiple systems throughout the network, event forwarding is a time and resource saver. With event forwarding, you can forward application log events to a central server and then process those events on this server. Thus, rather than having to configure alerts on 12 different server instances, you configure event forwarding on 11 servers and have one server handle all the incoming events. You could then use the application log's Computer field to determine the system on which the event occurred and take the appropriate corrective actions using scripts or remote procedure calls.

To configure event forwarding, complete the following steps:

Access the Advanced tab of the SQL Server Agent Properties dialog box, as shown in Figure 12-24.
Select Forward Events To A Different Server.
Use the Server selection list to choose the server that will handle the events. Choose <New Forwarding Server> to register a new server.
Set the type of events to forward by selecting Unhandled Events or All Events. An unhandled event is one that you haven't configured alerts for on the current server.

Figure 12-24: Event forwarding can be helpful in reducing the time you spend configuring alerts on individual servers.
In the If Event Has Severity Of Or Above selection list, determine the severity threshold for events that are forwarded.

Tip To reduce network traffic caused by event forwarding, set the severity threshold to a fairly high value. Fatal errors have a severity level of 19–25.
Click Apply.

Multiserver Job Scheduling

When you want to centrally manage job scheduling, you'll need to create a master server and one or more target servers. The SQL Server Agent running on the master server

Centrally manages jobs for the target servers. Then you create jobs on the master that runs on the targets. For details, see the section of this chapter entitled "Assigning or Changing Job Definitions."
Can also download jobs to a target. For details, see the "Managing Existing Jobs" section of this chapter.

Multiserver Scheduling Requirements

For the master/target relationship to work correctly, you must

Make sure that the master server and all target servers are running SQL Server 2000.
Use domain accounts, not local accounts, when configuring the master and target.
Make sure that SQL Server Agent is running on the master server and all target servers.

Configuring Master Servers

To create a master server, complete the following steps:

In Enterprise Manager, access the SQL Server Agent window on the server you want to configure as the master.
Right-click SQL Server Agent, point to Multiserver Administration, and then select Make This A Master. This starts the Make MSX Wizard.
Read the welcome dialog box and then click Next.
As shown in Figure 12-25, create a special operator to handle multiserver job notifications. This operator, called MSXOperator, is created on the master and all target servers that use this master. Set an e-mail, pager, and Net Send address, as appropriate. You can change this information later by editing the MSXOperator properties on the master server.
Select the target servers to associate with this master server. The process of associating target servers with a master is called enlisting. Later, you can remove the association by right-clicking SQL Server Agent in Enterprise Manager, selecting Multi Server Administration, and then selecting Manage Target Servers.

Figure 12-25: Use the Make MSX Wizard to configure the MSXOperator to handle multiserver job notifications.
Click Next and then click Finish. The wizard performs the necessary tasks and reports its progress. You'll be notified of any errors.

Configuring Target Servers

You can configure one or more target servers for each master server. You create target servers by completing the following steps:

In Enterprise Manager, access the SQL Server Agent window on the server you want to configure as the target.
Right-click SQL Server Agent and then select the Multiserver Administration option. From the shortcut menu, choose Make This A Target. This starts the Make TSX Wizard.
Read the welcome dialog box and then click Next.
Set a valid Windows domain account that can be used to access network resources. Type an account name and a password.
In the next dialog box, type the name of the master server for this target.
Click Next and then click Finish. The wizard performs the necessary tasks and reports its progress. You'll be notified of any errors, and you can click Pause to stop the operation.

Database Maintenance

Database maintenance involves different tasks. Because most of these tasks have been discussed in previous chapters, this section doesn't go into detail on the tasks already covered. Instead, it provides a checklist that you can use as a starting point for your maintenance efforts. Then it explains how to set up maintenance plans and run database consistency checks.

Database Maintenance Checklist

The following is a checklist for daily, weekly, and monthly maintenance tasks:

Daily
	Monitor application, server, and agent logs. Configure alerts for important errors that aren't configured for alert notification.
	Check for performance and error alert messages.
	Monitor job status, particularly jobs that back up databases and perform replication.
	Review the output from jobs in the job history or output file, or both.
	Back up databases and logs (as necessary and if not configured as automatic jobs).

Weekly
	Monitor available disk space on drives.
	Monitor the status of linked, remote, master, and target servers.
	Check the maintenance plan reports and history to determine the status of maintenance plan operations.
	Generate an updated record of configuration information by executing sp_configure.

Monthly
	Monitor server performance, tweaking performance parameters as necessary to improve response time.
	Manage logins and server roles.
	Audit server, database, and object permissions to ensure that only authorized users have access.
	Review alert, job, and operator configurations.

As Needed
	Back up the SQL Server Registry data.
	Update the Emergency Repair Disk.
	Run database integrity checks and update database statistics. (SQL Server 2000 handles this automatically in most cases.)

Using Maintenance Plans

Maintenance plans provide an automated way to optimize databases, check database integrity, create backups, and ship transaction logs to a standby server. You can run a maintenance plan against a single database or multiple databases. You can also generate report histories for maintenance plan execution.

You create maintenance plans with the Database Maintenance Plan Wizard. The wizard generates jobs to handle the maintenance tasks you select. You should manage these jobs through the Database Maintenance Plan dialog box, as discussed in the section of this chapter entitled "Viewing, Editing, and Deleting Maintenance Plans."

Creating Maintenance Plans

You can create a maintenance plan by completing the following steps:

In Enterprise Manager, access the Management folder on the server you want to work with.
Right-click Database Maintenance Plans and then select New Maintenance Plan. This starts the Database Maintenance Plan Wizard.
Read the welcome dialog box and then click Next.
As shown in Figure 12-26, select the databases that'll use this mainten- ance plan. You can use the maintenance plan with all databases, all system databases, all user databases, or a combination of one or more individual databases.

Tip For most installations, I recommend configuring separate maintenance plans for system and user databases. This gives you greater flexibility when it comes to how and when maintenance operations are performed. For large installations you may want to have separate maintenance plans for each database. This way you can work with different databases on different days or at different times of the day.

Figure 12-26: Using the Database Maintenance Plan Wizard, select the database(s) that'll use this maintenance plan.
Select database optimization operations to perform and then set a schedule for these operations, as shown in Figure 12-27.

If you prefer, select the Reorganize Data And Index Pages check box to drop and recreate current table indexes with a new fill factor. Then select one of the following options:
- Reorganize Pages With The Original Amount Of Free Space Recreates indexes with the original fill factor that was specified when the indexes were created.
- Change Free Space Per Page Percentage To Specifies a new fill factor. The higher the percentage, the more free space is reserved on the index pages and the larger the index grows. The default is 10 percent. Valid values are from 0 to 100.
Note: Fill factors are discussed in Chapter 2 in the section entitled "Setting the Index Fill." Reorganizing pages changes table indexes and thus invalidates existing statistics. You can't reorganize data and update statistics in the same plan, and you may want to create separate maintenance plans for handling each of these important tasks.
If you prefer, select the Update Statistics Used By Query Optimizer check box to resample the distribution statistics of each index created on user tables. SQL Server uses distribution statistics to optimize performance when executing statements on tables. Then set a percentage of the database to sample using the Sample % Of The Database field. A higher percentage generates more accurate statistics than a lower percentage. The default value is 10 percent. Valid values are from 1 through 100.

Figure 12-27: Choose optimization options and then set a schedule for execution.

Note: If you set the sample value to low, SQL Server automatically adjusts the sample size to ensure that a sufficient sample is collected.

If you prefer, select the Remove Unused Space From Database Files check box to remove any unused space from the database. Then set values for these fields:
- When It Grows Beyond Free space is removed only when its size exceeds this value. The default value is 50 MB, which means that if there is more than 50 MB of free space, SQL Server should shrink the database to the size specified in the next field.
- Amount Of Free Space To Remain After Shrink Sets the amount of unused space to remain after the database is reduced in size. The value is based on the percentage of the actual data in the database. The default value is 10 percent. Valid values are from 0 through 100.
Click Change and then set a schedule for the optimization operations you've selected. This schedule can be different from other schedules in the maintenance plan.
As shown in Figure 12-28, determine whether you want to perform integrity checks. Integrity checks can detect inconsistencies and errors in databases by running DBCC CHECKDB.

To run database integrity checks, select Check Database Integrity and then configure additional options as follows:
- Include Indexes Checks the data and index pages in the database during the integrity tests.
  
  Figure 12-28: Check and repair the database according to the integrity check schedule.
- Attempt To Repair Any Minor Problems Attempts to automatically correct minor problems detected during the database integrity checks. This option is recommended and is available only when you include indexes in the checks.
- Exclude Indexes Checks data pages only during integrity tests and doesn't check indexes. Because fewer database pages are tested, this option executes faster than the Include Indexes option.
Note: If you exclude indexes, you can't repair minor problems automatically; you'll need to handle this manually instead.
If you prefer, select the Perform These Tests Before Doing Backups check box to perform consistency checks before backing up databases or logs. This ensures that the databases or logs are backed up only if the integrity checks are normal. Otherwise, if the integrity tests detect inconsistencies, the affected databases or logs aren't backed up.
Click Change and then set a schedule for the integrity operations you've selected. This schedule can be different from other schedules in the maintenance plan.
Specify a database backup plan for the selected databases, as shown in Figure 12-29. If you select Backup The Database As Part Of The Maintenance Plan, complete backups are performed at the scheduled times. You can also verify the backup after completion.

Figure 12-29: Configure when complete backups of the database are performed.
Select the location of the backup as Tape or Disk. Then click Change to set a backup schedule.
After you configure the backup plan, you need to set storage options for the backups, as shown in Figure 12-30. With disk-based backups, you can set a specific backup directory and create subdirectories for each database being backed up. You can also set the file extension for the backups. The default extension is .bak.
An interesting option for backups is the ability to automatically remove old backups. Select the Remove Files Older Than check box and then set a specific time period in minutes, hours, days, weeks, or months. For example, you could remove backups more than three weeks or two months old.
Set backup options for the transaction logs associated with the selected databases. These options are basically the same as those shown in Figure 12-30.
Set storage options for transactions logs. These options are basically the same as those shown in Figure 12-30.
The remaining dialog boxes let you generate maintenance reports and a maintenance history. Maintenance reports are stored in a directory you designate, and you can specify that old reports should be automatically deleted. Maintenance history information is stored in msdb and by default is limited to 1000 rows of data. If you increase this value or create several plans, make sure that msdb is sized appropriately to handle this data.

After you've configured reporting and history, click Finish to complete the process and generate jobs to handle the designated maintenance tasks. These jobs are labeled
- Optimizations Job for DB Maintenance Plan <Plan Name>
  
  Figure 12-30: Configure the backup location and storage options.
- Integrity Checks Job for DB Maintenance Plan <Plan Name>
- DB Backup Job for DB Maintenance Plan <Plan Name>
- Transaction Log Backup Job for DB Maintenance Plan <Plan Name>

Creating Maintenance Plans For Log Shipping

You use log shipping to create a standby server that's automatically synchronized with a primary server. Log shipping is configured through maintenance plans and is available only with SQL Server 2000 Enterprise Edition. To get started, configure the standby server as described in the section of Chapter 11 entitled "Creating a Warm Standby" and then configure a separate log shipping plan for each database that you want to synchronize.

You create a database maintenance plan for log shipping by completing the following steps:

Log on to the primary server. The primary is the database server instance that you want to ship logs from.
Use Windows Explorer to create a folder for storing the transaction logs that you want to ship. Be sure the associated drive has ample free space.
Share the folder on the network and set the access permissions so that the SQL Server Agent domain account has access permissions.

Tip A good resource for creating shared folders and setting shared folder permissions is Microsoft Windows 2000 Administrator's Pocket Consultant (Microsoft Press, 2000). See the sections of Chapter 13 entitled "Creating Shared Folders" and "Managing Share Permissions."
Start Enterprise Manager. In Enterprise Manager, access the primary server's Management folder.
Right-click Database Maintenance Plans and then select New Maintenance Plan. This starts the Database Maintenance Plan Wizard.
Read the welcome dialog box and then click Next.
In the Select Databases dialog box of the Database Maintenance Plan Wizard, select These Databases and then check the database to log ship.

Caution: If you select more than one database, log shipping won't work, and the log shipping option won't be available. Also, you aren't allowed to select a database that's already configured for log shipping.
Check Ship The Transaction Logs To Other SQL Servers (Log Shipping).
If you have an existing maintenance plan for this database, continue through the wizard, skipping all the database maintenance options, until you get to the Specify the Transaction Log Share dialog box. Be sure to clear Back Up The Database As Part Of The Maintenance Plan in the Specify The Database Backup Plan dialog box.
If you don't have an existing maintenance plan for this database, continue through the wizard, setting appropriate database maintenance options, until you get to the Specify the Transaction Log Share dialog box.
Type the UNC path for the network share where the transaction logs are created on the primary server, such as \\ENGSQL\Data\Logs. Or click the build (…) button if you want to browse for a folder location.
In the Specify The Log Shipping Destinations dialog box, click Add to add a destination database. This displays the Add Destination Database dialog box shown in Figure 12-31.
Use the Server Name selection list to choose the destination server name. The server must be registered and running SQL Server 2000 Enterprise Edition to appear in the drop-down list.
The destination directory for the transaction logs you're shipping is set by default. To change the default location, type a new directory path or click the build (…) button and browse for an existing directory.

Figure 12-31: In the Add Destination Database dialog box, specify the destination database for log shipping.
If you want to enable the destination to become the primary in case of failure, select Allow Database To Assume Primary Role. If this box isn't checked, the destination database won't be able to assume the primary role in the future. Then use the Transaction Log Backup Directory field to specify the backup directory for logs on the destination server. You must set a value for this field.
If the source database doesn't exist on the destination server, select Create New Database (Initialize). The Database Name will default to the source database name. If you've chosen to allow the destination database to assume the primary role, you can't change the database name from the default. Otherwise you can specify a new name if you like.
If you've elected to create a new database, you must also specify the file directories for data and logs on the destination database. Use the For Data and For Log fields.
If the source database already exists on the destination server, select Use Existing Database (No Initialization) and then use the Database Name selection list to choose the database. The database must have been restored using the WITH STANDBY option to properly accept logs.
Set the Database Load State to either No Recovery Mode or Standby Mode and then click OK. Then click Next in the Specify The Log Shipping Destinations dialog box.
In the Initialize the Destination Databases dialog box, specify whether to Take Full Database Backup Now or to Use Most Recent Backup File to initialize the destination database. If the existing backup file is selected but isn't in the log shipping share, the file is copied to that location. Click Next when finished.
From the Log Shipping Schedules dialog box shown in Figure 12-32, view the default log shipping schedule. If you would like to alter the schedule, click Change and then set a new schedule.
Use the Copy/Load Frequency fields to set the frequency in minutes or hours that you want the destination servers to back up and restore the transaction logs from the source server. The default is 15 minutes.
Use the Load Delay fields to set the delay in minutes or hours that you want the destination database to wait before it restores the transaction log from the source server. By default there is no delay, which means the destination server should immediately restore any transaction log backups.
Use the File Retention Period fields to specify the length of time that must elapse before a transaction log can be deleted. The default is 24 hours. Click Next when you're finished.
In the Log Shipping Thresholds dialog box, set the Backup Alert Threshold. The backup alert threshold sets the maximum elapsed time since the last transaction log backup was made on the source server. If the elapsed time exceeds this threshold, the monitor server generates an alert.

Figure 12-32: Set log shipping schedules in the Log Shipping Schedules dialog box.
Set the Out of Sync Alert by specifying the maximum elapsed time between the last transaction log backup on the source server and the last transaction log restore on the destination server. If the elapsed time exceeds this threshold, the monitor server generates an alert. Click Next when you're finished.
In the Specify the Log Shipping Monitor Information dialog box, select the server that will monitor log shipping and then choose either Use Windows Authentication or Use SQL Server Authentication to connect to the monitor server. If you choose to use SQL Server authentication, you must specify a password. The log_shipping_monitor_probe login name is fixed and must be used to connect to the monitor server. If this is a new account, choose a new password and the account will be created. If the account already exists on the monitor server, you must specify the existing password.
The remaining dialog boxes let you generate maintenance reports and a maintenance history. Maintenance reports are stored in a directory you designate, and you can specify that old reports should be automatically deleted. Maintenance history information is stored in msdb and by default is limited to 1000 rows of data. If you increase this value or create several plans, make sure that msdb is sized appropriately to handle this data.
After you've configured reporting and history, click Finish to complete the process and generate jobs to handle the designated maintenance tasks.

The jobs on the primary server are labeled

Log Shipping Alert Job – Backup
Log Shipping Alert Job – Restore
Transaction Log Backup Job For DB Maintenance Plan <Plan Name>

The jobs on the destination server are labeled

Log Shipping Copy For <Server.Database>
Log Shipping Restore For <Server.Database>

Checking Maintenance Reports and History

Creating a maintenance plan is only the beginning. After you create the plan, you'll need to check the maintenance reports and history periodically. Maintenance reports are stored as text files in the designated directory. You can view these reports in a standard text editor or word processor. To access the maintenance history through Enterprise Manager, complete the following steps:

In Enterprise Manager, access the Management folder on the server you want to work with.
Right-click Database Maintenance Plans and then select Maintenance Plan History. This displays the Database Maintenance Plan History dialog box shown in Figure 12-33.
Use the Status selection list to view all jobs, only successful jobs, or only failed jobs.

Figure 12-33: Periodically check the Database Maintenance Plan History dialog box to ensure that maintenance jobs are successfully executing.

Viewing, Editing, and Deleting Maintenance Plans

You can view, edit, or delete maintenance plans by completing the steps on the following page.

In Enterprise Manager, access the Management folder on the server you want to work with.
Select Database Maintenance Plans in the left pane. You'll see the existing maintenance plans in the right pane.
To view or edit a maintenance plan, double-click the maintenance plan entry in the right pane. This displays the Database Maintenance Plan dialog box shown in Figure 12-34. The options of this dialog box are almost identical to those provided by the Database Maintenance Plan Wizard.
To delete a maintenance plan, select its entry and then press Delete. When prompted to confirm the deletion, click Yes.

Figure 12-34: Options in the Database Maintenance Plan dialog box are almost identical to those available in the Database Maintenance Plan Wizard.

Checking and Maintaining Database Integrity

With SQL Server 6.5 you had to perform database consistency checks frequently to ensure that databases didn't get corrupted. With the improved architecture of SQL Server 7.0 and SQL Server 2000, you rarely have to perform database integrity checks, and when you do, you can use maintenance plans to handle most of the work for you. On those rare occasions when you perform consistency checks manually, you'll use the DBCC command. DBCC stands for database consistency check. While there are many different DBCC commands, the ones you'll use most often to maintain a database are covered in the following sections.

Using DBCC CHECKDB

The DBCC CHECKDB command checks the consistency of the entire database and is the primary technique you'll use to check for database corruption. The command ensures that

Index and data pages are linked correctly
Indexes are up to date and sorted properly
Pointers are consistent
The data on each page is up to date
Page offsets are up to date

Sample 12-1 shows the syntax and usage for the DBCC CHECKDB command. When you run the command without a repair option, errors are reported but not corrected. To correct errors, you need to put the database in single-user mode and then set a repair option. After you repair the database, create a backup.

Sample 12-1 DBCC CHECKDB Syntax and Usage

Syntax

DBCC CHECKDB 
   ( 'database_name' 
      [ , NOINDEX 
         | { REPAIR_ALLOW_DATA_LOSS 
         | REPAIR_FAST 
         | REPAIR_REBUILD }
      ] 
   )                   [ WITH { [ ALL_ERRORMSGS ] 
                     [ , [ NO_INFOMSGS ] ] 
                     [ , [ TABLOCK ] ] 
                     [ , [ ESTIMATEONLY ] ] 
                     [ , [ PHYSICAL_ONLY ] ] } 
                  ]

Usage

DBCC CHECKDB ('customer', NOINDEX)
DBCC CHECKDB ('customer', REPAIR_REBUILD)

The REPAIR_FAST option performs minor repairs that don't consume a lot of time and won't result in data loss. The REPAIR_REBUILD option performs comprehensive error checking and correction that requires more time to complete but doesn't result in data loss. The REPAIR_ALLOW_DATA_LOSS option performs all the actions of REPAIR_REBUILD and adds new tasks that may result in data loss. These tasks include allocating and deallocating rows to correct structural problems and page errors as well as deleting corrupt text objects.

Tip When trying to fix database problems, start with REPAIR_FAST or REPAIR_REBUILD. If these options don't resolve the problem, use REPAIR_ALLOW_DATA_LOSS. Keep in mind that running the REPAIR_ALLOW_DATA_LOSS option may result in unacceptable loss of important data. To ensure that you can recover the database in its original state, place the DBCC command in a transaction. This way you can inspect the results and roll back the transaction, if necessary.

Using DBCC CHECKTABLE

To correct problems with individual tables, you can use the DBCC CHECKTABLE command. As Sample 12-2 shows, the syntax and usage for this command is almost the same as DBCC CHECKDB.

Sample 12-2 DBCC CHECKTABLE Syntax and Usage

Syntax

DBCC CHECKTABLE
   ( 'table_name' | 'view_name'
      [ , NOINDEX
         | index_id
         | { REPAIR_ALLOW_DATA_LOSS
            | REPAIR_FAST
            | REPAIR_REBUILD }
      ]
   )                [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
                     [ , [ TABLOCK ] ]
                     [ , [ ESTIMATEONLY ] ]
                     [ , [ PHYSICAL_ONLY ] ] }
                  ]

Usage

DBCC CHECKTABLE ('receipts')
DBCC CHECKTABLE ('receipts', REPAIR_REBUILD)

Using DBCC CHECKALLOC

To check the consistency of database pages, you can use DBCC CHECKALLOC. Again, the syntax for this command is nearly identical to the previous DBCC commands. One item worth noting is that although Sample 12-3 shows a NOINDEX option, it's maintained only for backward compatibility with previous SQL Server versions. The command always checks the consistency of page indexes.

Sample 12-3 DBCC CHECKALLOC Syntax and Usage

Syntax

DBCC CHECKALLOC
   ( 'database_name'
      [, NOINDEX
         |
         { REPAIR_ALLOW_DATA_LOSS
            | REPAIR_FAST
            | REPAIR_REBUILD   }       ]
   )                [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
                     [ , [ TABLOCK ] ]
                     [ , [ ESTIMATEONLY ] ] }
                  ]

Usage

DBCC CHECKALLOC ('customer')
DBCC CHECKALLOC ('customer', REPAIR_REBUILD)

Using DBCC CHECKCATALOG

Another useful DBCC command is CHECKCATALOG. You use this command to check the consistency of a database's systems tables. Sample 12-4 shows the syntax and usage of the command.

Sample 12-4 DBCC CHECKCATALOG Syntax and Usage

Syntax

DBCC CHECKCATALOG
   ( 'database_name'
   ) [WITH NO_INFOMSGS]

Usage

DBCC CHECKCATALOG ('customer')

Using DBCC DBREINDEX

To rebuild one or more indexes on a database, you can use DBCC DBREINDEX. Sample 12-5 shows the syntax and usage of the command.

Sample 12-5 DBCC DBREINDEX Syntax and Usage

Syntax

DBCC DBREINDEX
   ( ['database.owner.table_name'
         [, index_name
         [, fillfactor ]
         ]
      ]
   ) [WITH NO_INFOMSGS]

Usage

DBCC DBREINDEX ('customer.dbo.customers', PK_cust, 75)
DBCC DBREINDEX (customers, '', 85)

Click to order

Database Automation and Maintenance

On This Page

Using SQL Server Agent

Accessing Alerts, Operators, and Jobs

Configuring the SQL Server Agent Service

Configuring SQL Server Agent

Viewing SQL Server Agent Logs

Managing Alerts

Default Alerts

Creating Error Message Alerts

Handling Alert Responses

Deleting, Enabling, and Disabling Alerts

Managing Operators

Registering Operators

Deleting and Disabling Notification for Operators

Configuring a Fail-Safe Operator

Scheduling Jobs

Creating Jobs

Assigning or Changing Job Definitions

Setting Steps to Execute

Configuring Job Schedules

Handling Notification Messages

Managing Existing Jobs

Managing Job Categories

Automating Routine Server-to-Server Administration Tasks

Copying Logins to Another Server

Copying Scheduled Jobs to Another Server

Copying Shared Stored Procedures to Another Server

Copying User-Defined Error Messages to Another Server

Copying or Moving User-Defined Databases to Another Server

Multiserver Administration

Event Forwarding

Multiserver Job Scheduling

Database Maintenance

Database Maintenance Checklist

Using Maintenance Plans

Checking and Maintaining Database Integrity

Additional resources