31. Manage job automation
SQL Server Agent Service
operation
Warning
SQL Server Agent Error Record file
Summary of this chapter
In Chapter 30, we have read several automated settings, and the Database options provided by SQL Server 2000, which help DBA reduced adjustments. The auxiliary tools provided by SQL Server will be used in this chapter to automate other management jobs in accordance with SQL Server Agent service. SQL Server Agent Services includes a specific job that automatically performs a database, as well as a problem or event, or an event, informs DBA or other specific person. The benefits of these functions make DBA do not need to manually or constantly monitor the database system to decide when to perform a specific job, so there is more time to use in a more difficult database issue, such as establishing and adjusting the index, optimizing the query, or For future growth pre-sample plans.
There are three main tools for automated management: homework, warnings, and operators. In this chapter, you will learn to build and use jobs, warnings, and operators on SQL Server Agent Services and how to use services. You can also find an error record for SQL Server Agent, which can be used to track the work of SQL Server Agent.
SQL Server Agent Service
The SQLServer agent executes the service from SQL Server, called the SQL Server Agent. This service is included in SQL Server 2000, but must be started separately, manually or automatically. Chapter 8 For the method of launching the SQLServer agent program. Once the service is started, you must prepare the job, warning, and operator you need.
Description
SQLServer Agent Services is called SQL Executive in Microsoft SQL Server 6.5. It can also be used as a review, such as chapter 26, 27, 28.
operation
The job (JOBS) is a management task, as long as it is defined, it can be implemented several times. You can manually perform your job, or arranged with SQL Server at a specific time, or execute regularly or warning (
The job can be executed on the local server, or you have multiple servo on the Internet, you can specify a main server, other as the target serve server. The main server stores the job definition for all the servers, and coordinates all job activities as the job cleanup. Each target server is constantly connected to the primary server. If there is a job change, update the job list, download a new job from the main server, then cut off the connection to execute new homework. When the target server completes the job, it will be connected to the primary server and report its end conditions.
Let's take a look at a hypothesis of establishing a job. Suppose you have a database of database information, saved the transaction record of each bank, such as deposits and transfer. Each record has a TIMESTAMP Data Office for the transaction. This information will grow continuously and require regular deletion of information. To remove a column from the data sheet, you can write a small pre-preserved program with the delete statement to delete the old data prior to two months (assuming the bank will only keep two months). Then you can build a job to perform this pre-depreciation, such as executing once every Sunday night. In this way, you can ensure that the data sheet will not grow without restriction. This can not only retain the disk space, but also improve the performance. The less information in the table is performed, the faster SQL Server can complete the query. Let us now look at the details of establishing a job. Description
SQL Server Agent Services must be executed in order to make your job work properly.
Establish a job
You can define jobs with Enterprise Manager, T-SQL instruction files, establish job wizards, or SQL-Distributed Management Objects (SQL-DMO). Because the SQL-DMO method is related to the program of the computer, it is not within the scope of this book. This chapter will learn from other three ways to establish a job.
relevant information
For information on using SQL-DMO, see
Enterprise Manager
Use Enterprise Manager to build a job. One of the most common methods is to implement the database backup (this can also be done as the maintenance plan elf as mentioned in Chapter 30). The following example is to establish a job backup MyDB database. It arranges backed up at 11:00 every night and the success or failure of the backup job in the Windows NT or Windows 2000 application event record and output files. Follow the steps below to establish a job named mydb_backup_job:
In the pane on the left of Enterprise Manager, expand a server folder, expand the management folder, and then expand the SQL Server Agent program folder. Right-click on the job and select a new job on the flash function. Show new job properties window, as shown in Figure 31-1.
Figure 31-1 New Job Properties Window
Set the following options in the General Terminal:
Name Type Job Name - This is "MYDB_BACKUP_JOB" in the text block. The job name can have up to 128 words. Each job on the server must be a unique name and determine the use of the descriptive name. Enabling this verification block specifies that the job should be started or deactivated. You may want to stop the job first to make a manual test to ensure normal operation. After the test, you can use this job to start the job, so that the job can be executed as scheduled. Homework categories Select Category for this job - In this example, we use the preset category without classification (native). You can choose from the established job category, which is created when installing SQL Server, or you can create your own category (want to learn how to create new categories, please refer to this chapter "Create New Category> section). The installed category has uncategorized (local), database maintenance plan, full-text search, web aid, and 10 categories of replies. Category is used to classify related homework. For example, you can use jobs for performing database maintenance to the same category, or classified, such as accounting, sales, and marketing. Category Let you track multiple jobs - you don't have to take a list of jobs for a small part of job. The owner builds the user. Only the sysadmin role can change who has homework or change the job owned by other users (SQL Server roles will be described in Chapter 34). All sysadmin roles and job owners can change the definition of the job or start or stop the job. In the owner drop-down menu, select the user who will perform the job. In this example, the operator and the establishment of the operator are the same person, so the owner will automatically select the correct setting, and you can make this set itself. Description In this text block, you must confirm what tasks to perform and the purpose of the job. You should provide a description that describes the functionality that makes other users quickly determines the job. The description can have up to 512 bits. The target is that this option is to select this option, and the job will only be executed on the native server. If there is a remote server connected to this server, you can use the option for multiple servers. Click this option to specify that this job is also executed on the remote server. The complete sample job General tab is shown in Figure 31-2. Figure 31-2 Fill in the general page sign
Click on the step check, then select the new increase, you will display the new job step dialog, as shown in Figure 31-3. Job Steps is an instruction or statement that defines job tasks. Each job must have at least one job step or multiple steps. Enter the following information in the general tab dialog in the new job step:
Type the step name in the step name word - in this example is to enter "MyDB_Backup". From the type drop-down menu, select a step type to execute. In this example, since the job is to be performed with a T-SQL instruction, the Transact-SQL command code (TSQL) is selected. Other options are ActiveX Script, job system commands, reproaders, reply transaction record readouts, recall mergers, and copy the column reader, and reply snapshots. Select the database name to perform the job from the Database drop-down menu. In this example, select the MYDB database. Type instructions that will be used as a job step in the command text block. In this example, the command is a T-SQL instruction to back up the MYDB database to a backup device called MYDB_BACKUP1. (This backup device must be established in advance, there is a detailed description of the establishment of the backup device in Chapter 32. Also, in this simple example, the database backup is written in the same file per night. In fact, you should be as 30 Correlated Use the Database Maintenance Plan to perform a backup, just like it will make you build a new backup device every day.) If there is a ready-to-order file, you want to use it as a job, you can also click Open Old gear to open the file. Click inathematical, check the syntax of the T-SQL step, then select the advanced tab and set the option, as shown in Figure 31-4. In this tab, you can choose the action after success or failure: End the job return success, end the job return failed, or jumps to the next step. You can also specify the number of times the job should retry when the job is not successful, and the interval between twice. If this job contains the T-SQL instruction or instruction code, you can select an output file, which will have a T-SQL output report. You can also overwrite the output files after each job is executed, or override the output files each time. Click on the view to view the contents of the output file. Choose to add an additional to the step history record to get the block to make the job output in the job history record table project. You can also specify the user who performs T-SQL. Figure 31-3 After the "New Job Step" dialogue is "General" Tag
Figure 31-4 "Advanced" page sign filled in the "New Job Step" dialogue
Insert a new step that needs to be executed before an existing step, select the existing steps you need to insert, and click to insert the Add Jump Jobs Dialog box, enter the step information you want to insert. To delete the step, select the step after selecting the step; you want to edit the steps, select the step after selecting the step. You can also move the steps in your list, select the steps after you select the up and down arrows on the right side of the move. Starting Steps Drop-pull menu allows you to choose which step in your job should be executed first. A green tag will appear next to the step ID number to be executed. Click the set to apply steps in the job. If any process logic between multiple steps can cause a step unforgettable, SQL Serve displays a warning message when you click to use it, and allow you to modify process logic. To create a schedule for a job, click the Scheduler. To find out the current time of the server, you must pay attention to: Target Server Current Date Time Delivery Platform Select the server name. Now click on a new schedule to display the new job scheduled dialogue, as shown in Figure 31-5. The scheduling will specify when the execution job can be executed at a certain point or can be performed regularly. If you want to manually perform your job in any time, you don't need a job schedule - you can do it when you want to perform jobs. Fill in the Screen Name MYDB_BACKUP_SCHEDULE and set options in the Schedule area (here you want to repeat it), then select Enable Nuclear Square, as shown in Figure 31-5. Enabling Nuclear Squares Here the functionality and it is the same in the new job properties window. Since we choose to repeat the schedule, you must set when the job will be performed when To do this, click the change display Edit Repeat the job schedule dialog. Type a new time and date, click OK to return to the new schedule. (Remember that we have to set a daily backup at 11 o'clock every night.) Click OK in the new schedule dialogue to accept your schedule and return to the new job properties window. To delete the schedule, select the schedule and click Delete; to edit the schedule, select the scheduling name and click Edit. Figure 31-5 New Screen Dialogue
Explain that you can also establish a new warning for this job. This chapter will mention more details of the warning later.
Click to tell the message page sign as shown in Figure 31-6. On this tab, you can set a notification program, then the operator (or specified user) receives notifications when the job is successful, failed, or completed. Notifications can be transmitted through an email, a call or with a NET Send command. You can write job status into a Windows NT or Windows 2000 event record, and you can automatically delete jobs after successful, failing, or completion. To set an operator notification, select the email operator, the call operator, and the network transfer operator, then select the operator name on the right drop-down menu. (See how the "Operator>" after this chapter will learn how to establish an operator.) Select the conditions for performing notifications in the leftmost drop-down menu. When the job is completed to write the results into an event record or automatically delete jobs, select the appropriate verification block, then select the condition of performing the action on the related drop-down menu. In this example, you want to select a write to the Windows Application event record file to get the block.
Figure 31-6 N new job properties window tells the message tab
Once the setting option is complete, click the set to create your job. Then click OK to exit the new job properties window Back to Enterprise Manager. Click job in the left pane of Enterprise Manager, you will see a list containing myDB_Backup_Job in the right pane. Creating a new category To create a new category, open a server in the left pane of Enterprise Manager, expand the management folder, select right button on the job, click all work on the quick function table, then select the management job category. The job category dialogue appears, as shown in Figure 31-7. You can join a category here, review existing categories and jobs in categories, and delete categories. Use the T-SQL T-SQL command to create jobs, add steps, and establish job scheduling, which are system pre-store sp_add_job, sp_add_jobstep, and sp_add_jobschedule. These pre-deposited programs have multiple selective parameters, as shown in the following program code. SQL Server assigns a preset value for each unspecified parameter. Establishing a job with Enterprise Manager is relatively simple, because its graphical user interface guides you to set job options to avoid you missing some parameters. With T-SQL you must contain values for all selective parameters, or determine that any preset parameter values you ignore can meet job needs. You should use Enterprise Manager instead of manual to execute the pre-store. You can generate a T-SQL instruction file, let Enterprise Manager build a job with the instruction file, right click on the job name, select all work in the quick function table, then select Production SQL instruction code. This technology allows you to re-establish your job with your instruction file.
Figure 31-7 Work Category Dialogue
To perform the pre-stored procedures just mentioned, you must use the MSDB database because this is where the storage program is. If you want to use these programs, you must first look at what parameters in the pre-depressed program are available. All pre-prediction programs in this chapter use the same general grammar. The syntax of the pre-store sp_add_job is as follows:
SP_ADD_JOB [@JOB_NAME =] 'JOB_NAME'
[, [@enabled =] enabled]
[, [@Description =] 'Description']
[, [@start_step_id =] step_id]
[@category_name =] 'category']
[, [@category_id =] category_id]
[, [@Ner_Login_Name =] 'login']
[, [@Notify_level_eventlog =] EventLog_level]
[, [@notify_level_email =] email_level]
[, [@Notify_Level_netSend =] NetSend_level]
[, [@Notify_Level_Page =] Page_Level]
[, [@Notify_Email_operator_name =] 'email_name']
[, [@Notify_netSend_operator_name =] 'netsend_name']
[, [@notify_page_operator_name =] 'Page_name'] [, [@Delete_level =] delete_level]
[, [@originating_server =] 'Server_name']
[, [@JOB_ID =] job_id output]
The syntax of sp_ADD_JOBSTEP is as follows:
SP_ADD_JOBSTEP [@Job_ID =] job_id | [@JOB_NAME =] 'JOB_NAME'
[, [@Step_id =] step_id]
{, [@Step_name =] 'step_name'}
[, [@subsystem =] 'subsystem']
[, [@Command =] 'Command']
[, [@additional_parameters =] 'parameters']
[, [@cmde =] code]
[, [@On_success_action =] success_action]
[, [@on_success_step_id =] success_step_id]
[, [@on_fail_action =] fail_action]
[, [@on_fail_step_id =] fail_step_id]
[, [@server =] 'Server']
[, [@Database_name =] 'Database']
[, [@Database_user_name =] 'user']
[, [@Retry_attempts =] retry_attempts]
[, [@retry_interval =] retry_interval]
[, [@OS_RUN_PRIORITY =] Run_Priority]
[, [@output_file_name =] 'file_name']
[, [@flags =] flags]
The syntax of sp_add_jobschedule is as follows:
sp_add_jobschedule [@JOB_ID =] Job_ID, | [@JOB_NAME =] 'JOB_NAME',
[@Name =] 'name'
[, [@enabled =] enabled]
[, [@freq_type =] freq_type]
[, [@freq_interval =] freq_interval]
[, [@freq_subday_type =] freq_subday_type]
[, [@freq_subday_interval =] freq_subday_interval]
[, [@Freq_RELATIVE_INTERVAL =] FREQ_RELATIVE_INTERVAL]
[@freq_recurrence_factor =] freq_recurrence_factor] [, [@Active_start_date =] Active_Start_Date]
[, [@Active_end_date =] Active_END_DATE]
[, [@Active_start_time =] Active_Start_time]
[, [@Active_end_time =] ACTIVE_END_TIME]
Related Information For each parameter and its preset value description, see the name of the pre-stored program related to the online series> index.
Explain that the pre-store mentioned here is like other pre-depreciation procedures related to establishing and managing jobs, operators, notifications, and warnings exist in the MSDB database. You must use this database to perform a pre-store.
Using the Establishing Elf Enterprise Manager provides a wizard, this wizard will guide you step by step, and the only restriction is that it limits the job that you can only establish a single step. However, it allows you to provide a job schedule and specify an operator that is notified. After establishing a job, you can use Enterprise Manager to modify your job and add more job steps. Establish a job with the following steps to establish a job:
Select the wizard from Enterprise Manager's tool drop-down menu, open the management data clip in the Select Elf dialogue, and select the job wizard, display the establishment of the job wizard welcome screen, as shown in Figure 31-8. Click Next to display the Select Job Command Type screen, as shown in Figure 31-9. This screen can specify the step type you want to build. In this example, select the Transact-SQL command.
Figure 31-8 "Establishing Wizard" Welcome Pictures
Figure 31-9 "Select Job Command Type" screen
Click Next, display the input Transact-SQL statement (Fig. 31-10). You must select the Database of the Instruction, and then type the statement for the job, or press the old gear to find or turn on the file containing the T-SQL command. Press the analysis and check the syntax of the T-SQL statement you entered. If you have already selected the job system shell command or Active Script as the instruction type, you must enter instructions for these types. For example, type the T-SQL instruction backup Master database to the pre-established Backup_Master_DEV device, as shown in Figure 31-10.
Figure 31-10 Entering Transact-SQL Statement Screen
Press Next, display the specified job schedule, as shown in Figure 31-11. You can specify when you execute your job here. Now the option specifies that the job will be executed when the wizel is completed. Other options look at it. In this example, select repeated execution, then select the scheduling to set the schedule. The editing duplicate work schedule is repeated, as shown in Figure 31-12. Use these options to establish the required database and press to determine the accept settings and return to the specified job schedule.
Figure 31-11 Specify job schedule
Figure 31-12 "Edit Repeat Implementation Job Scheduling" dialogue
Press Next, display the job notification screen, as shown in Figure 31-13. Select the operator of the notification job completion status on the network transmission or email drop-down menu. You must already define the operator for jobs displayed on the list. (Figure 31-13 shows an undefined operator.) If you want to notify the operator that has not been defined, please complete this elf and add an operator (later
Press Next, display the completion of the establishment of the job wizard screen, as shown in Figure 31-14. The job name text is allocated to the job to replace the preset value for the job. In this example, our job is named backup_master_job. Check the contents of the description block, determine the option you want, and press it to create a job. If the option you want is not appear, press the next step to make some corrections. If the job is successfully established, a message will appear. Press to determine the message block. After completing the establishment of the job wizard, the Enterprise Manager's job data clip will appear new homework.
Figure 31-14 Completing the establishment of a job wizard picture
Management homework
You can manage or edit your job through Enterprise Manager or using T-SQL. ENTERPRISE Manager may be easier to use because you don't have to worry about the syntax and preset value of the T-SQL pre-preserved program, and Enterprise Manager's GUI will boot your setting job properties.
Enterprise Manager
You can use Enterprise Manager to start, stop, deactivate, enable, edit, and establish T-SQL program. Each usage of these tasks is as follows:
To start a job, press the right button on the job name of the ENTERPRISE Manager, and select the startup job in the flash function. To stop the job being executed, and delete the set retry step, press the right button on the job name and select Stop Job in the quick function table. Deactivate jobs allows jobs to be tested within the scheduling time that is not allowed. Press the right button on the job name and select the deactivation job from the quick function table. Select Enable jobs can be enabled again. To edit the job, schedule, or other job properties, press the right click on the job name and select the content from the flash function, display the property window, the window contains 4 identical pages used to establish a job. sign. After doing a good fortification, press the set and press OK.
If you want to rebuild your job at any time without re-entering the statement, you have to create a T-SQL instruction code for your job. Press the right button on the job name. Select all the work in the quick function table, then select Generate SQL instructions. Code, display a SQL instruction code dialog. Type the file name, select the file format (Unicode, ANSI, or OEM text), press OK. You can also start, stop, start, deactivate, and edit a job with the following T-SQL pre-depicting program. When performing these programs, remember to use the MSDB database. Sp_start_job immediately starts the specified job. This program requires a job name or a job ID number. Sp_stop_job stops the job being executed. This program requires job name, job ID, or host server name. Sp_update_job allows you to enable, deactivate, and change the properties of your job. This program requires a job name or a job ID number.
Related Information To view the grammar of these pre-stored programs and options that can be used with, see
The job name is right in the job name of the ENTERPRISE Manager, select the view job history, display the job history dialog, as shown in Figure 31-15. Here you will see a line describe the information, operator notifications of each job, and the errors or messages received on SQL Server.
Figure 31-15 Operation History Record Dialogue
To view additional details of the job execution status, select the display procedure for details in the upper right corner of the dialog. Figure 31-16 shows the details of the MYDB backup.
Figure 31-16 shows the detailed steps in the job history record dialog
To clear all messages, please click all clear. To update the screen to view the job status (to turn on the new job executed after the job history dialogue), click Reason. To turn off the job history record dialog, please click Close. Use T-SQL to use T-SQL to view job history about job scheduling, execute pre-store sp_help_jobhistory at the MSDB Database. The syntax is as follows:
sp_help_jobhistory [[@JOB_ID =] job_id]
[, [@JOB_NAME =] 'job_name']
[, [@Step_id =] step_id]
[, [@SQL_MESSAGE_ID =] SQL_MESSAGE_ID]
[, [@SQL_SEVERITY =] SQL_SEVERITY]
[, [@start_run_date =] start_run_date]
[, [@END_RUN_DATE =] end_run_date]
[, [@Start_Run_time =] start_run_time]
[, [@end_run_time =] End_run_time]
[, [@Minimum_Run_DURATION =] minimum_run_duration]
[, [@run_status =] run_status]
[, [@Minimum_Retries =] minimum_retries]
[, [@eldest_first =] Oldest_first]
[, [@server =] 'Server']
[, [@Mode =] 'Mode']
If you don't use the parameter or if you do not use the Job ID or Job Name parameter, information about all job scheduling will be returned. The MODE parameter specifies whether to return all history information (full) or return a summary. The preset value is set to Summary.
Related Information For details on the pre-depreciation of other options, please refer to
The alert warning (ALERT) is an inaction that occurs on the server, an response action of an event or execution. The warning can notify the operator so that the specified job can be executed and the event is forwarded to another server. Event (event) is an error or message, which is written to the Windows NT or Windows 2000 Application event record (you can use Windows NT or Windows 2000 Event Viewer). Performance Condition is a feature of the system performance, which can be monitored with a Windows NT Efficacy Monitor or Windows 2000 System Monitor, such as a CPU utility or multiple locks used by SQL Server. In this chapter, we will focus on the Windows 2000 system monitors, even if the Usage of the Windows NT Efficiency Monitor is also very similar. When an event occurs, the SQL Server agent program will compare the event with the warning list you defined. If the alert has been defined by the event, the alert will start. When the defined performance threshold reaches the SQL Server object specified in the System Monitor, alerts that trigger performance conditions are like the counter User Connections under the General Statistics under the system monitor object. For example, you can set a warning when the value of the counter reaches 50. (Description of the System Monitor in Chapter 36.) Description In order to make your warning to work, you must perform SQL Server Agent service.
The event message record is before explaining how to establish alert to events, let's take a look at the event type that allows the message to send to the Windows NT or Windows 2000 application event. Only these events can be used to set alert. Event (or error) Important levels from 19 to 25 are automatically returned to Windows NT or Windows 2000 Application Event Record, thus you can start a warning. There is no record in the preset value of less than 19 in the preset value, so the warning cannot be launched in these events. To make those events can be recorded and can be activated, you must use sp_alterMessage, raiserror with log statements, or XP_LOGEVEN to change the state of the event or message. In this section, you will learn how to build the user-defined event message and learn how to issue a warning message to ensure that the event will be written to the application event record.
Note When the SQL Server message record is recorded in Windows NT or Windows 2000 Application event, it is also recorded in the SQL Server record. To view SQL Server records in Enterprise Manager, expand the management folder and then expand the SQL Server record file folder.
Establishing a user-defined event message system All information and user definition messages are stored in the sysmessages table of the Master database. To build a user-defined message, use the T-SQL system sp_addMessage pre-deposit, the syntax is as follows:
sp_addMessage [@msgnum =] msg_id,
[@severity =] severity,
[@msgtext =] 'msg'
[, [@LANG =] 'Language']
[, [@with_log =] 'with_log']
[, [@Replace =] 'Replace'] The user defines the message must have a message ID value (MSG_ID) to 50001 or more value. The Severity parameter is an important level of incorrect level, ranging from 1 to 25, the higher the number, the higher the important level of the error. Only the system administrator can set the important level to 19 to 25. The MSG_Text parameter is the content of the error message, and it is displayed in the application event record when the error occurs. Language parameters Specify which language written in the message, because SQL Server can install multiple languages. The WITH_LOG parameter can be set to true or false, specify whether the message is to be recorded in the Windows NT or Windows 2000 Application event record. The preset value is false so this setting is overwritten when using RaisError With Log. The Replace parameter specifies that a new message should replace a message with the same information ID number. Public role members allow SP_ADDMessage, but if you want to create a message important level or more or if you set with_log to True, you must be one of the Sysadmin role members. Let's take a look at an example of using sp_addMessage. The following statement establishes a new message recorded in an event record (because with_log is set to true):
SP_ADDMESSAGE 50001, 16, "Customer ID is out of range.", @ with_log = "true"
Go
The warning record setting of the event message is assumed to assume an existing message or the message you just created is not allowed (or you do not contain the with_log parameter), just like the following example:
SP_ADDMESSAGE 50001, 16, "Customer ID is out of range.", @ with_log = "true"
Go
If you want to log your message later, you must change the status of the message. In this case, use the sp_alterMessage to set the record, as described below:
SP_alterMessage 50001, with_log, "true"
Go
Alternatively, you can use the RaisError narrative in the with log option, return the message to the application, and return to the application event record and in the SQL Server record. For example, the following describes the use of important hierarchical hierarchical, status value, and status (state) is used to track numbers when the error occurs more than one place in the encoding:
Raiserror (50001, 16, 1) with log
Go
Related Information For more details on using raiserror, see Raiserror in the Upline Book> Index, select RaisError in the found topic dialogue.
To change the recording status of your message, you can use the extension pre-depreciation xp_logevent in the Master database. When using this program, the message will be transferred to the event record and the SQL Server record, but it is not transferred to the client's application. Below is an example of using this program:
Use master
Go
XP_LOGEVENT 50002, "Customer ID is out of Range.", WARNING
Go
There must be the first two parameters and must contain the user-defined message ID number (must be greater than 50000) and the message text that appears in the output record. The third parameter - Important level - is selective, can be one of the three strings of INFORMATIONAL, WARNING, or ERROR. The setting of important hierarchies determines what type of image that occurs after the message in Event Viewer, so that warnings or errors can be quickly identified. In Windows 2000, the information has a blue i image, a warning message has a yellow! Image, and the error message has a red x image. If there is no important level, the preset value is INFORMATIONAL. Establishing alert Now we are ready to build alert in events and performance conditions. To establish alert, you can use Enterprise Manager, T-SQL or SQL-DMO. We will only explain Enterprise Manager and T-SQL methods because SQL-DMO exceeds this book. Establishing an event warning using Enterprise Manager In this example, we will establish a warning on system messages with important levels 24. The message is recorded in the event record, and the user does not need to intervene to change its recording status. Please establish an event warning following steps: Expand a server folder in the left pane of Enterprise Manager, expand the management folder, expand the SQL Server Agent program folder. Press the right button on the warning and select a new alert on the flash function, and a new alert property window appears, as shown in Figure 31-17. Type the name of the alert on the general page sign (128 words). Type "IO_ERROR_ALERT" in this example. Enabling the Nuclear Square Allows you to enable or deactivate this alert. Deactivation alert will cause it to be not triggered, just like deactivation. In this example, you want to determine that the alert is enabled. Select the SQL Server event warning in the Type Danched menu, because we have to establish alert, and this alert will be released when an event occurs. (Another warning type option is S QL Server Value Condition Warning, an example of such alert type will be explained in the next section.) Our example is to establish a warning to be released when I / O error occurs.
Figure 31-17 "New Police Properties" Window "General" Tag
In the event alert definition area of the new alarm properties window, select the error number or importance to specify an event you want to start alert, then specify an error number or an important level. If the important level is specified, all errors of the important level will lead to alert. In this example, you have to select the error number, then click the ... button to look for numbers. The management SQL Server message dialog appears, as shown in Figure 31-18. To search for a specified error, select a category on the list of importance in the search page, and then press it. The found error message will be column on the message tab. There are two check squares at the bottom of the search page, which can be used to limit the search. Only messages containing only records allows search to retrieve information automatically recorded in event records. Only messages containing users can only search the user-defined message. For example, to find all serious hardware errors, choose 024- severe errors in the importance clearance: hardware error, then press Find. Error number 823 (important hierarchy 24) will appear on the message tab, as shown in Figure 31-19.
Figure 31-18 "Managing SQL Server Message" dialog box "Search" Type
Figure 31-19 "Management SQL Server Message" dialogue "message" tab
Press to determine this message and return to the new alarm property window. The Database Name drop-down menu allows you to specify that only when the event comes from the selected database, the warning will lead. Keep the preset value setting of all databases. The text text included in the error message allows you to type strings (can be 100 bit) to limit the error that will lead to alert, and these error text will display those strings just typed. If you let this text, you will not be restricted. Press the response page sign as shown in Figure 31-20. In this tab, you can specify what action should be taken when the alert occurs. Select the execution job verification, select a job name on the drop-down menu, allow your job to be executed when the alert occurs. Press a new operator to establish a new operator to notify. Existing operators will appear on the notification of the operator list. You can specify whether the operator should be notified by email, call, network transmission, or integrated the above method. Figure 31-20 "New Police Properties" Window "Response" Tag
If you specify an email to notify the operator, and also select the error text that will be alerted to the verified block, the error text will be transmitted to the operator in the alert message. Type additional text in the email to other information telling messages to be transmitted at the bottom of the tab. Additional text can have up to 512 bits. Figure 31-20 Display the operator Testoperator, select email notification. Additional messages are included. Note the response interval delay text block. These text block settings specify that the warning is repeated, and the operator will notify it once. Set 60 minutes to indicate that the operator is notified once every 60 minutes. To accept the alerts and responses you entered, please use it. Then click OK to close the window. Establishing the ENTERPRISE Manager to establish a Warning Warning When a certain performance condition occurs, we can use Enterprise Manager to establish a warning. Note that the SQLServer agent has polled a functional counter every 20 minutes, so if there is a high low situation between the polling, it is not possible to monitor. Establish a warning followed by the following steps:
Expand a server folder in the left pane of Enterprise Manager, expand the management folder, expand the SQL Server Agent program folder. Press the right button on the warning and select a new alert on the flash function. Type a name for the alert in the Name text block of the general tab (the name used in this example is user_alert). Specifies an effective condition warning to select the SQL Server performance condition warning in the Type Danched Menu. Define a control condition that will start a warning is defined in the performance condition warning definition area. Choose the SQL Server performance item you want to use, as a booting object in the object drop-down menu, then select the counter to be used in the counter drop order. Set the timing to issue alert to the counter reachable option, specify which case warning will be triggered. Finally, set a threshold, if exceeded this particular value, alert will be triggered. Figure 31-21 shows that this setting will cause alert when the SQL Server User Connections counter arrives at 100.
To complete the settings of the alert, set the option on the response page check, as described in step 5 above. Press the set and then press OK.
Figure 31-21 "New Police Properties" Window "General" Tag
Establish event warning or function conditions for use with T-SQL You can also use T-SQL to create alert, but you can remember if you build alert with Enterprise Manager, you can generate T-SQL instruction codes for those alerts. (To do this, press the right click on the warning in the SQL Server Agent program folder, click all the work on the function table, then select Produce SQL instruction code.) You may find alert to establish alert with Enterprise Manager It is easier, because the T-SQL method needs to learn and remember many option parameters and their preset values. To add a warning with T-SQL, use sp_add_alert pre-depreciation. This program is used if you want to establish an event warning or the effectiveness warning. The parameter option determines what type of warning to establish. The syntax of sp_add_alert is as follows: sp_add_alert [@name =] 'name'
[, [@Message_ID =] message_id]
[, [@severity =] severity]
[, [@enabled =] enabled]
[, [@DELAY_BETWEEN_RESPONSES =] DELAY_BETWEEN_RESPONSES]
[, [@Notification_Message =] 'notification_message']
[, [@include_event_description_in =] include_event_description_in]
[, [@Database_name =] 'Database']
[, [@Event_description_keyword =] 'Event_Description_keyword_pattern']
[, {[@JOB_ID =] job_id | [@Job_Name =] 'Job_name'}]
[, [@raise_snmp_trap =] raise_snmp_trap]
[@Performance_Condition =] 'Performance_Condition']]
[@category_name =] 'category']
The pre-depreciation program uses to modify the alert, check the alert information, and remove sp_update_lert, sp_help_alert, and sp_delete_lert's warning. Remember, all pre-depreciation programs can be found in the MSDB database.
Related Information For details of this section, please refer to
Operator operator (Operators) is a person who receives notifications from SQL Server when the job is completed or warned. The operator is a person responsible for maintaining the system that performs SQL Server. You have learned how to define a notification message transmitted to the operator. As mentioned earlier, there are three ways to contact the operator: transfer email messages, calls, and use network transfer instructions (transfer network messages into the operator's computer). The system must meet several necessary conditions to make these methods work smoothly. If you use email and call contact, you must install the MAPI-1-Compliant E-mail Client on the server, such as Outlook or Exchange Client, and must establish a mail file clip to the SQL Server agent program. With a call, you also need to install the three-party E-mail-to-Pager software on the mail server to handle the internal email messages and convert the message to a call message. Using network transfer, you must use Windows NT or Windows 2000 for job systems - network transmission does not support Windows 95/98. Related Information For how to set up a mail folder, see your email user software file. For more information on Pager-to-E-mail software, see your call service provider or call file.
Each operator must be defined on SQL Server. You can establish a not only one operator to share the responsibility. When you contact other operators, you can notify the fail-safe protection operator (for example, when the call fails). You can build an operator using Enterprise Manager, T-SQL, and SQL-DMO. This section will explain the method of Enterprise Manager and T-SQL, and SQL-DMO is not within the scope of this book. Establish an operator with Enterprise Manager followed by ENTERPRISE Manager:
Expand a server folder in the left pane of Enterprise Manager, expand the management folder, expand the SQL Server Agent program folder. Press the operator on the operator and select the new operator from the quick function table to display the new operator properties window, as shown in Figure 31-22. Type the name for the new operator in the general tab, then enter one or more: the operator's email name, the email name of the call, and the network transfer address. If you enter a call address, you can specify when you can call the operator during your call standby area. For example, if there is a not only an operator, you can assign a responsibility, let a operator call on Monday, 3, 5, and Sunday, and another operator is called on Tuesday, Four, and Saturday.
Press to inform the message page sign. If you select a warning (on the upper right corner of the label), the presented warning list is displayed, as shown in Figure 31-23. Select the verified block in the field, you can specify what alert will notify the operator and will use the contact method to notify the operator.
Figure 31-22 "New Operator Properties" Window "General" Tag
Figure 31-23 "New Operator Properties" Window's "Warning"
When you create a new operator, you will not be able to click on the job, because there is no job to notify the new operator, and the new operator does not exist. To avoid new operators receive notifications, clear the operator is ready to receive the message to tell the message. Deactivate this option to suspend the transfer notification to the operator - for example, when the operator vacation. Then when the operator came back, you can revoke the NGP and enable notifications. Press to transfer the email button to create a text message listed on the general tab to pass to the operator. (If you do not enter an email address, an error will appear.) You can send an email to describe the type of notification established as an operator. In order to inform the message page, you will see information about the most recently typed to notify the operator. Use the T-SQL to establish an operator T-SQL instruction to create an operator, modify operator information, check operator information, and delete an operator. In the MSDB database, the system's pre-departments are: sp_add_operator, sp_update_operator, sp_help_operator, and sp_delete_operator. You may reassure use Enterprise Manager is a relatively easy way. After establishing an operator with Enterprise Manager, you can generate a TSQL instruction code. Here is the syntax of sp_add_operator: sp_add_operator [@Name =] 'name'
[, [@enabled =] enabled]
[, [@EMAIL_ADDRESS =] 'email_address']
[, [@PAGER_ADDRESS =] 'Pager_Address']
[, [@WeekDay_Pager_Start_time =] Weekday_Pager_Start_Time]
[, [@WeekDay_Pager_end_time =] weekday_pager_end_time]
[, [@SATURDAY_PAGER_START_TIME =] SATURDAY_PAGER_START_TIME]
[, [@SATURDAY_PAGER_END_TIME =] SATURDAY_PAGER_END_TIME]
[, [@sunday_pager_start_time =] sunday_pager_start_time]
[, [@sunday_pager_end_time =] Sunday_Pager_END_TIME]
[, [@PAger_DAYS =] Pager_DAYS]
[, [@Netsend_address =] 'netsend_address']
[@category_name =] 'category']
Related Information The details of the
SQL Server Agent Error Record SQL Server Agent Service It has error records that records the startup and closing of the SQLServer agent, and any warnings, errors, and notification messages associated with S QLServer agent jobs or warnings. To use the SQL Server Agent error record file, please follow these steps:
Expand a server folder in the left pane of Enterprise Manager, expand the management folder, expand your SQL Server Agent's program folder, and select the display error record file from the quick function table, error record, such as Figure 31-24 shows. Type Danchline menu allows you to view error messages, warning messages, information messages or all messages (all types). Figure 31-25 shows that after the SQL Server agent is turned on, the error message looks like something. (Note that all types are selected in the type drop-down menu.)
Figure 31-24 "SQL Server Agent Error Record" dialog
Figure 31-25 "SQL Server Agent Error Record" dialog, display all types of messages
Each time you turn on the SQL Server agent program, the error message will restart, override the existing message in the record. You can search for information with a specific string, as long as the strings are included in the included text blocks, then press ENTER or point to select the filter criteria. Figures 31-26 Display the error record after the search for string CPUs.
Figure 31-26 Results of the String Strings in Error Record Message
Press two down on your message to see the SQL Server Agent Error Log Message dialogue, as shown in Figure 31-27.
Figure 31-27 "SQL Server Agent Error Record Message" dialogue
If the search result is not a message, you can convert a message with the previous step and the next button. If you only find a message, these buttons will not be available. If the operator is not in contact with some reason or the homework cannot be performed, the SQLServer Agent Error Record will receive an error message. You should occasionally check this error record and determine if any errors need to be processed. This chapter summarizes in this chapter, you learned how to use SQL Server Agent service to make management work automation through defining jobs and operators, setting notifications, and establish event warnings and performance warnings to enable management. You have learned the importance of incorrect hierarchy when establishing an event warning, and how to change the record status of an error message to enable record in Windows NT or Windows 2000 Application Event Record. You have also learned how to review the SQLServer agent module, file recorded information about SQL Server agent program, and errors and warnings that occur in warnings and homework. In Chapter 32, we will discuss backup SQL Server libraries.
