How SQL Server timed jobs

If you need to execute a stored procedure or 3200-character SQL statement in SQL Server at regular intervals or at intervals, you can use the admin->sql Server Agent-> job.

1. Manage->sql Server Agent-> Job (right mouse button)-> new Job->

2. New Job Properties (General)-> name [Customize the name of this job]-> the box that is enabled is a tick->

The classification can optionally also be available by default [Unclassified (local)]-> owner defaults to log on to SQL Server users [or other logins]->

Description [Fill in the detailed description of this work];

[Steps to create a job classification:

SQL Server Agent-> job-> Right Select All Tasks-> Add, modify, delete

3. New Job Properties (step)-> New-> step name [Customize first step name]-> type [Transact-SQL (TSQL) script]->

Database [Database]-> command to manipulate

[If simple SQL is written directly, you can also use the Open button to enter a written *.sql file.]

If you want to execute a stored procedure, fill in

EXEC p_procedure_name v_parameter1,[V_parameter2...v_parametern]

]

-> OK

(If you have more than one step, you can call the new button below again, or you can insert, edit, delete) the existing multiple steps;

4, the construction of job properties (scheduling)-> new schedule-> name [custom dispatch name]-> enabled box is tick-> schedule-> recurrence->

Change Schedule Schedule]-> OK

(If you want to save this job, do not regularly do can be enabled box is the tick off);

5. Construction Job Properties (notification)-> the default notification method. Write to the Windows Application System log when a job fails-> OK.

Some SQL Server knowledge related to job execution:

The SQLServerAgent service must function correctly, and the NT logged-on user that started it will be consistent with the NT Logged-on user who started the SQL Server database.

The point job right-click to view the history of the job execution, or start the job immediately and stop the job.

When I looked at the history of the job recently, I found that there are more records of the job records, and some jobs record less history.

How to make certain jobs remain for a period of time according to their own needs. For example, keep a one-month history record.

Read the SQL Server online Help documentation, which says:

In the admin->sql Server Agent-> Right-click Properties-> Job System-> limit the size of the job history log->

The maximum size (number of rows) of the job history log defaults to 1000 if a machine has a large number of jobs, be sure to improve it, for example 100000

The maximum number of rows per job history log defaults to 100 if the job executes two times a day, you need to keep a one-month log, which can be set to 60

There is a mutual constraint between them, we can change according to their own needs.

If the SQL Server server changed the machine name, the job that was established when managing the old name might encounter

Error 14274: Unable to add, update, or delete a job originating from an MSX server (or its steps or schedules)

Read the documentation for Microsoft: http://support.microsoft.com/default.aspx?scid=kb;en-us;281642

Say msdb in SQL Server 2000 system. The name of the original server is stored in the Originating_server field in Sysjobs.

The 24x7 system must not change the name back to the previous Microsoft document.

So I thought, "msdb." Can sysjobs update originating_server word Cheng The new server name now in use?

Use msdb

SELECT * FROM Sysjobs

Locate the Originating_server field or the job_id of the old server, and then execute the UPDATE statement:

Update sysjobs set originating_server= ' New_server_name '

where job_id= ' B23bbebe-a3c1-4874-a4ab-0e2b7cd01e14 '

(The number of rows affected is 1 rows)

This allows you to add, update, or delete jobs that have made error 14274.

If you want to migrate your job from one machine to another, you can keep the script to create the job and run it on another machine.

To export the Create script action steps for all jobs:

Manage the->sql Server Agent-> Job (right mouse button)-> All Tasks-> Generate SQL Script-> save to an SQL file under the operating system

To export the Create script action steps for a job:

Manage->sql Server Agent-> jobs-> Select jobs to be transferred (right mouse button)-> All Tasks-> Generate SQL Script-> save to a SQL file under the OS

Then run the SQL script for the create job that you just saved on the destination server.

An error occurs if the user who built the job or the user who alerted does not exist;

We need to set up the relevant Windows user or SQL Server database login on the destination server, or modify the script to create the job, and replace the nonexistent user on the destination server with the existing user.

If the physical file directory that generated the log does not exist, you should also make related modifications, such as D:\ area to F:\ area. In addition, there is an error in the @command command for the string, which can be removed.