Mssql Job, mssql

Mssql Job, mssql

1. What is a job?

A job is a series of specified operations performed by the SQL Server Agent in sequence. A job can execute various types of activities, including running Transact-SQL scripts, command prompt applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands, and query or copy tasks. Jobs can run repetitive or schedulable tasks, and then they can run

Alarm to automatically notify the user of the job status, which greatly simplifies SQL Server Management.

2. Let's first look at a commonly used job to dynamically back up the data warehouse through GUI.

1. Open SQL Server Management Studio 2, start SQL Server Agent 3, click job-> new job 4, "general" input Job name 5, create step, type select T-SQL, enter the following statement in the following command: DECLARE @ strPath NVARCHAR (200) set @ strPath = convert (NVARCHAR (20), getdate (), 120) set @ strPath = REPLACE (@ strPath ,':','. ') set @ strPath = 'e: \ DATA_db \ MSSQL.1 \ MSSQL \ Backup' + @ strPath + '. bak 'backup DATABASE [DATABASE name] to disk = @ strPath with noinit, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT 6 , Add a plan, set the frequency, time, and so on. After completing the above procedures, you can work properly.

3. Job system table

SELECT * FROM msdb. dbo. sysjobs -- stores the information of each scheduled job that will be executed by the SQL Server proxy SELECT * FROM msdb. dbo. sysjobschedules -- contains the plan information for the job executed by the SQL Server proxy SELECT * FROM msdb. dbo. sysjobactivity; -- record the activity and status of the current SQL Server proxy job SELECT * FROM msdb. dbo. sysjobservers -- stores the association or relationship between a specific job and one or more target servers. SELECT * FROM msdb. dbo. sysjobsteps; -- contains information about each step in the job to be executed by the SQL Server proxy SELECT * FROM msdb. dbo. sysjobstepslogs; -- SELECT * FROM msdb. dbo. sysjobs_view; -- SELECT * FROM msdb. dbo. sysjobhistory -- contains information about SQL Server proxy execution of scheduled jobs SELECT * FROM msdb. dbo. syscategories-contains categories used by SQL Server Management Studio to organize jobs, alarms, and operators

4. Create a job

The procedure for creating a job is as follows: Execute sp_add_job to create a job. Execute sp_add_jobstep to create one or more job steps. Execute sp_add_schedule to create a scheduler. Execute sp_attach_schedule to attach the plan to the job. Execute sp_add_jobserver to set the server of the job. Local Jobs are cached by the local SQL Server proxy. Therefore, any modification will implicitly force the SQL Server proxy to re-cache the job. The SQL Server Agent caches the job until sp_add_jobserver is called. Therefore, calling sp_add_jobserver is more effective.

Start a job

1. Start a job using SSMS [omitted]

2. run SQL commands to start a job.

Generally, sp_start_job is used to start a job. For more information about the syntax and operations, see the following.

Syntax:

Sp_start_job

{[@ Job_name =] 'job _ name'

| [@ Job_id =] job_id}

[, [@ Error_flag =] error_flag]

[, [@ Server_name =] 'server _ name']

[, [@ Step_name =] 'step _ name']

[, [@ Output_flag =] output_flag]

Example:

 

Exec msdb. dbo. sp_start_job @ job_name = 'job _ cycle_errorlog'

Stop a job

1. Stop a job using SSMS [omitted]

2: Stop a job using SQL commands

Syntax:

Sp_stop_job

[@ Job_name =] 'job _ name'

| [@ Job_id =] job_id

| [@ Originating_server =] 'master _ Server'

| [@ Server_name =] 'target _ Server'

Example:

Exec msdb. dbo. sp_stop_job @ job_name = 'job _ cycle_errorlog'

Enable or disable a job

1. Use SSMS to enable a job [omitted]

2. disable a job using SQL commands

Syntax:

Column: EXEC msdb. dbo. sp_update_job

@ Job_name = n' JOB _ cycle_errorlog ',

@ Enabled = 0; -- 0: Disable a job; 1: Enable a job.

GO

Delete a job

1. Use SSMS to delete a job [omitted]

2: use SQL commands to delete jobs

Example:

EXEC msdb. dbo. sp_delete_job @ job_name = 'job _ cycle_errorlog ';

5. view the T-SQL statement of the job

-- 1: view all jobs in a database. SELECT job. job_id AS JOB_ID, name AS JOB_NAME, enabled AS JOB_ENABLED, description AS JOB_DESCRIPTION, date_created AS DATE_CREATED, date_modified AS DATE_MODIFIED FROM msdb. dbo. sysjobs job WHERE job_id IN (SELECTjob_id FROM msdb. dbo. sysjobsteps WHERE database_name = 'databasename ') -- 2: view all jobs in a job category SELECT job. name AS Job_Name, job. description AS Job_Description, job. date_created AS Date_Created, job. date_modified AS Date_Modified, type. name AS Job_Class FROM msdb. dbo. sysjobs job left join msdb. dbo. syscategories type ON job. category_id = type. category_id WHERE type. name = '[Uncategorized (Local)]' -- 3: view the disabled/enabled job SELECT * FROM msdb. dbo. sysjobs WHERE enabled = 0 -- 0: Disable 1: Enable -- 4: View error job records -- 4.1: Query jobs that have failed today (if you want to query historical error jobs, remove the query time condition.) SELECT name AS JOB_NAME, description AS JOB_Description, date_created AS Date_Created, date_modified AS Date_Modified FROM msdb. dbo. sysjobs WHERE enabled = 1 AND job_id IN (SELECT job_id FROM Msdb. dbo. sysjobhistory WHERE run_status = 0 AND run_date = CAST (CONVERT (VARCHAR (8), GETDATE (), 112) as int) -- 4.2: View error details SELECT job. name AS JOB_NAME, h. step_id AS STEP_ID, h. step_name AS STEP_NAME, h. message AS ERR_MSG, h. run_date AS RUN_DATE, h. run_time AS RUN_TIME, msdb. dbo. agent_datetime (h. run_date, h. run_time) AS 'rundatetime', CAST (run_duration/10000 as varchar (2) + N 'hour '+ CAST (run_duration-run_duration/10000*10000) /100 as varchar (2) + N' min '+ SUBSTRING (CAST (run_duration as varchar (10), LEN (CAST (run_duration as varchar (10 ))) -1, 2) + N' s 'AS run_duration FROM msdb. dbo. sysjobhistory h left join msdb. dbo. sysjobs job ON h. job_id = job. job_id WHERE run_status = 0 AND run_date = CAST (CONVERT (VARCHAR (8), GETDATE (), 112) as int) -- 5: view the job execution time: -- 5.1: view the time when the job is successfully executed on the current day (view job Step information) SELECT job. name AS job_name, h. step_id AS step_id, h. step_name AS step_name, h. message AS Message, h. run_date AS Run_date, h. run_time AS run_time, msdb. dbo. agent_datetime (h. run_date, h. run_time) AS 'rundatetime', CAST (run_duration/10000 as varchar (2) + N 'hour '+ CAST (run_duration-run_duration/10000*10000) /100 as varchar (2) + N' min '+ SUBSTRING (CAST (run_duration as varchar (10), LEN (CAST (run_duration as varchar (10 ))) -1, 2) + N' s 'AS run_duration FROM msdb. dbo. sysjobhistory h left join msdb. dbo. sysjobs job ON h. job_id = job. job_id WHERE run_status = 1 AND run_date = CAST (CONVERT (VARCHAR (8), GETDATE (), 112) as int) order by run_duration DESC -- 5.2: query the execution time of each job and SELECT job in descending order of execution time. name AS JOB_NAME, h. run_date AS RUN_DATE, SUM (run_duration) AS SUM_DURATION FROM msdb. dbo. sysjobhistory h left join msdb. dbo. sysjobs job ON h. job_id = job. job_id WHERE run_status = 1 AND run_date = CAST (converter (VARCHAR (8), GETDATE (), 112) as int) group by name, run_date order by Sum_Duration DESC