Have you ever encountered a situation where your SQL Server system becomes unresponsive at a specific time every day? It can be frustrating and time-consuming to identify the root cause of such issues. In this blog post, we will discuss a real-life scenario where a customer faced a similar problem and how it was resolved using a simple script.
The customer approached us with a complaint that their system was going unresponsive every morning around a certain time. They were clueless about what was causing this issue and why it was happening consistently. As a SQL Server consultant, I was determined to find the cause of the problem.
After discussing the issue with the customer’s team and performing various troubleshooting techniques like using PerfMon and Profiler, I discovered that there was a background process running at the problematic time. I suspected that SQL Server Agent Jobs might be the culprit.
When I asked the team if there were any SQL Agent jobs running at that time, they were clueless and unaware of how to check for running jobs. This prompted me to share a handy script that I had in my collection. The script allows you to list SQL Agent jobs running at a specific time.
SELECT * FROM
(
SELECT JobName, RunStart, DATEADD(second, RunSeconds, RunStart) RunEnd, RunSeconds
FROM
(
SELECT j.name AS 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) AS 'RunStart',
((jh.run_duration/1000000)*86400)
+ (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600)
+ (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60)
+ (jh.run_duration-(jh.run_duration/100)*100) RunSeconds
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE jh.step_id=0 --The Summary Step
) AS H
) AS H2
WHERE '2016-05-19 10:16:10' BETWEEN RunStart AND RunEnd
ORDER BY JobName, RunEnd
By running this script with the desired timestamp, you can easily identify the SQL Agent jobs that were running at that specific time. In the case of our customer, it turned out that a batch process was mistakenly scheduled for 10 AM instead of 10 PM. This simple human error was causing the system to become unresponsive every morning.
This scenario serves as a reminder of the importance of double-checking configurations and schedules to avoid such issues. Sometimes, even the simplest tasks can take ages to solve if not approached correctly.
I hope you find this script useful in troubleshooting SQL Server Agent Jobs. Feel free to modify and extend it according to your needs. If you have any questions or suggestions, please share them in the comments section below.