How SQL Server timed jobs

If you need to regularly execute a stored procedure or SQL statements of less than 3200 characters in SQLServer, you can use SQLServer proxy-job. 1. Manage-SQLServer proxy-job (right-click)-create job-2. Create job properties (General) -Name [customize the job name]-in the enabled box, yes

If you need to regularly execute a stored procedure or SQL statements of less than 3200 characters at intervals in SQL Server, you can use the management-SQL Server proxy-job. 1. Manage-SQL Server proxy-job (right-click)-create job-2. Create job properties (General) -Name [customize the job name]-in the enabled box, yes

If you need to regularly execute a stored procedure or SQL statements of less than 3200 characters at intervals in SQL Server, you can use management> SQL Server proxy> job.

1. Choose "manage"> "SQL Server proxy"> "job" (right-click)> "create job">

2. Create job attributes (General) -> name [customize the name of this job]-> the Enable box contains a check mark-> the default [unclassified (local)] can be selected or available in the Category field. -> the owner logs on to SQL Server by default. [Other Logon options are also available]-> description [fill in the detailed description of this job];

[Procedure for creating a job category: SQL Server proxy-> job-> right-click all jobs-> Add, modify, and delete jobs]

3. Create a job property (STEP)-> New-> step name [custom Step 1 name]-> type [Transact-SQL (TSQL) script]-> database [database to be operated]-> Command [if it is a simple SQL statement, write it directly. You can also open the button and enter a written *. SQL File

To execute the stored procedure, enter exec p_procedure_name v_parameter1, [v_parameter2... V_parameterN]-> OK (if there are multiple steps, you can call the new button below again, or you can insert, edit, or delete multiple existing steps );

4. Create job attributes (scheduling) -> Create scheduling-> name [custom scheduling name]-> enable the box with a check mark-> scheduling-> repeatedly appear-> change [scheduling schedule]-> OK (if you only need to save this job, if you do not do this regularly, you can remove the check mark in the enabled box );

5. Create job attributes (notifications)-> use the default notification method [write Windows application system logs when a job fails]-> OK.

Some SQL Server knowledge related to job execution:

The SQLSERVERAGENT service must run properly. The NT login user who starts it must be consistent with the NT login user who starts the SQL Server database.

Right-click a job to view the history of the job execution, or immediately start and stop the job.

Recently, when viewing the job history, we found that some jobs have many historical records and some have fewer historical records.

How can some jobs be retained for a period of time based on their own needs. For example, keep the history of a month.

I read the online help document of SQL Server, which introduces:

Choose "manage"> "SQL Server proxy"> "right-click" properties ">" job system ">" limit the size of job history logs ">

The maximum job history log size (number of rows) is 1000 by default. If a machine has a large number of jobs, you must increase it, for example, 100000

The maximum number of lines in the history logs of each job is 100 by default. if the job is executed twice a day, you need to keep the logs for one month, which can be set to 60.

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

If the SQL Server has changed its machine name, it may encounter

Error 14274: unable to add, update, or delete a job initiated on the MSX server (or its steps or scheduling)

In SQL Server 2000, The originating_server field in msdb .. sysjobs stores the name of the original Server.

The 24x7 system cannot change the name as described in the Microsoft document above.

So I thought, could msdb... sysjobs update originating_server field to the new server name currently in use?

Use msdb

Select * from sysjobs

Find the originating_server field or the job_id of the old server, and then execute the update statement:

Update sysjobs set originati