Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2009-09-24 : 13:44:18
|
| After doing a little testing, it appears that a scheduled Job does not log anything in msdb.dbo.sysjobshistory until after step 1 completes. That's fine and dandy...IF step 1 actually completes.But what if the job only has one step, and it takes a while (~1 hour) for that step to run, and then, somewhere while it is running, the SQL service is halted (don't ask...it was ugly but necessary). Now that the SQL service has been restarted and all appears well, is there anywhere in the system that I can verify that the job actually got started before the server went down? There is no evidence of it in sysjobhistory because step 1 did not complete.If the server hadn't gone down, I could have used sp_help_job to determine whether the job was executing, but now that the server has been stopped and restarted, that just shows the job as Idle, waiting for its next scheduled start.--------------------------------------------Brand yourself at EmeraldCityDomains.com |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-09-24 : 14:02:02
|
| Run Profiler, capture SP:Started and SP:StatementStarted, and filter it for your machine. Then check the job status through SSMS or Enterprise Manager, you'll capture the commands that poll job status. I believe it was an extended procedure in SQL 2000, I don't know about 2005+. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2009-09-24 : 14:46:18
|
Yes, actually did that to find out that what Enterprise Manager uses to refresh the job status is sp_help_job which in turn calls an extended procedure in master (xp_sqlagent_enum_jobs) but it appears that that only reports the current state, which appears to have been reset by stopping and restarting the service.I was hoping there would be a log file similar to sysjobhistory that would show the job started even if it never finished.I'm tempted to add a dummy step 1 to every job that does something like a WAITFOR DELAY '00:00:01' just to guarantee that something gets logged in history.Of course I'm only considering that because this has been a problem server...  --------------------------------------------Brand yourself at EmeraldCityDomains.com |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-09-24 : 14:55:23
|
| I've done stuff like that in the past, or added explicit logging to the job steps themselves. I'm really frustrated that Microsoft has dumped almost all of the Sybase heritage from SQL Server but retained it in their job system, like the date and time columns in sysjobschedules (WTF guys? Seriously) |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2009-09-24 : 16:10:52
|
| I agree! I ended up writing a function to parse and convert the job and step run_date and run_time to a real DateTime value for some queries I'm doing. Ridiculous!--------------------------------------------Brand yourself at EmeraldCityDomains.com |
|
|
|
|
|
|
|
|
Did the Job Start?