How SQL Server timed the job

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]-> box that is enabled is a tick-> the category can optionally also be available by default [Unclassified (local)]-> owner defaults to log on to SQL Server user [or other login]-> Description [Fill in the detailed description of this work];

[Steps to create a job classification: SQL Server Agent-> job-> 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 the stored procedure, fill in the 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; You can also insert, edit, and delete existing multiple steps.

4, the construction of job properties (scheduling)-> new schedule-> name [custom dispatch name]-> enabled box is a tick-> schedule-> recurring-> changes [schedule Schedule]-> determine (if you only save this job, Do not time to do can be enabled in the 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 in their own needs for a period of time. 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)

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 Originati