Sql-server – How to find a SQL Job details in Production environment

scheduled-taskssql server

There is a stored procedure being executed daily which inserts a record in a specific table. Our Production database servers has lot of SQL jobs scheduled to run daily at different intervals.

How to find out which job is running this particular stored procedure? Is there a way to find using SQL scripts?

Best Answer

You can use this query directly to get all the names of jobs that include specific text (for example "MyText"

SELECT j.job_id, j.[name], s.command
FROM sysjobsteps s
right join dbo.sysjobs j on s.job_id = j.job_id
WHERE command LIKE '%MyText%'

** Execute query on multiple SQL Servers can be done using SSMS by creating a local server group or a Central Management Server and one or more server groups

Open SQL Server Management Studio -> View -> click Registered Servers In the "registered Servers" windows you can create groups and add servers. To execute query on a group simply right-click a server group, point to Connect, and then click New Query

** Execute query on multiple Azure SQL Databases can be done using Azure Elastic Job (you can search a recording of my lecture about Elastic Pool and Elastic Job).

Related Solutions

Sql-server – SSMS Object Explorer Details causes deadlock while job is running

In my case, I was able to solve the problem by reducing the size of the transaction in the stored procedure, ala the comment provided by @Aaron Bertrand. The entire stored procedure was wrapped in one begin/end transaction. As the stored procedure progressed, my management studio instance would lock up. Reducing the transaction to just the critical inserts/updates, and adding some error handling, eliminated the problems I was having with SSMS.

Sql-server – SQL Server Agent Jobs – Long running job delaying other jobs

An easy workaround solution would be to nudge either of the jobs to start earlier/later and to not run together with the other job.

E.g.

    Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
    Job 2 Schedule:                        01:00, .......

It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table (possibly: IX, IU) before the Job 1 is able to acquire its required locks on the same users table. Then Job 1 has to wait.

You are encountering blocking as previously observed by @MaxVernon. You could run the following script at the full hour to see if you can catch the statements involved:

SELECT sp.spid,
       sp.kpid,
       sp.blocked,
       sp.hostprocess,
       sp.waittype,
       sp.lastwaittype,
       DB_NAME(sp.dbid),
       sp.cpu,
       sp.physical_io,
       sp.memusage,
       CASE 
            WHEN sp.blocked = 0 THEN sp.spid
            WHEN sp.blocked != 0 THEN blocked
       END               AS chain,
       dest.text
FROM   sys.sysprocesses  AS sp
       OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS dest
WHERE  spid > 50
       AND spid <> @@spid
       AND (
               spid IN (SELECT blocked
                        FROM   sys.sysprocesses
                        WHERE  blocked != 0
                               AND spid > 50
                               AND spid != @@spid)
               OR blocked != 0
           )

The output will show statements that are blocking or blocked. You will probably find that the statements are from the Job 1 and Job 2.

Possible solutions:

Adding a NOLOCK hint can help, but should be avoided if possible.
Then you could also consider optimising the code so that the steps in Job 2 run faster.
Changing the schedules of the Job 1 and/or Job 2.

Best Answer

Related Solutions

Sql-server – SSMS Object Explorer Details causes deadlock while job is running

Sql-server – SQL Server Agent Jobs – Long running job delaying other jobs

Possible solutions:

Related Question