SQL Server Job Question

Question

In our project, we have two SQL Server Jobs- one to run on every night 12 AM (let’s call it “JobA”) and another one to run on every month ( 1st of every month 12AM. And let’s call it “JobB”). These job creation scripts were run on April 8.

We have a log table that logs each successful run of the jobs (It logs failures happened during execution also). When I checked the log it is found that the “JobA” has run successfully from April 9 to April 30. Then it has run May 3rd to till date. And there is no log for “JobB”

I am assuming that the server would have gone down on May 1st and May 2nd, that’s why I don’t see any log for “JobB” in our logging database.

However, how do I prove it?

• Can I use any properties from the job to prove this?
• Can I use any server logs to prove it?

Please share your thoughts.

Answer 1

If you have access to the server's event logs, check them for activity during that timeframe, there should at least be an entry at noon every day with the system uptime. Check the SQL Agent logs as well.

The properties on the job should also have a Last Executed date/time on it, or you can

select * from msdb.dbo.sysjobsteps

to see some info that may help

Answer 2

Look for the start and close time for SQL Server.

SELECT Create_Date,[Name] FROM sys.databases  WHERE [Name]='tempdb'

SELECT Start_Time,Command FROM sys.dm_exec_requests WHERE Command IN ('LAZY WRITER','RESOURCE MONITOR')

SELECT Login_Time,LastWaitType FROM master..sysprocesses WHERE lastwaittype = 'LAZYWRITER_SLEEP'

By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. Typically, SQL Server retains backups of the previous six logs and gives the most recent log backup the extension .1, the second most recent the extension .2, and so on. The current error log has no extension.