As a DBA I was asked to list out all job in a production SQL Server with respect to the database. Approx 200+ jobes were running and 20+ databases were online. Task seems not so hard, but as I am not so good in query writing. Google was my first and last hope. After a couple of searches, I found two wonderful scripts from a blog. Below both the script will list out jobs associated with the database in a SQL Server.
Below query becomes very handy when it is required to list all the jobs residing on a specific database.
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @db_name VARCHAR(100); SET @db_name = 'OurTechIdeas.com'; -- Change your database name here SELECT database_name [Database Name], name [Job Name], js.job_id [Job ID] FROM msdb.dbo.sysjobsteps js INNER JOIN msdb.dbo.sysjobs_view jv ON js.job_id = jv.job_id WHERE database_name = @db_name; |
If it is required to get the complete list of jobs on a server along with the database they reside on, below query helpful.
1 2 3 4 5 6 7 |
SELECT DISTINCT database_name [Database Name], name [Job Name], js.job_id [Job ID] FROM msdb.dbo.sysjobsteps js INNER JOIN msdb.dbo.sysjobs_view jv ON js.job_id = jv.job_id ORDER BY database_name; |
Source: http://j.gs/DGar
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…