July 5, 2011 at 9:04 am
Hello all,
So, I'm working on a Client's system and one of the thing that I am seeing from the application side of things is that is lost connection to the Database server around 02:00 in the monring. In looking at the SQL Error Log, there are no events between 00:00 and 05:30 when SQL was restarted.
There was a Maintenance Plan configured to rebuild Indexes at 03:00, this would be between the last logged event in the SQL Error Log at 00:00 and the next logged event (new log) at 05:30. However, when I look at the Windows Event viewer there is an event indicating that a job had been invoked, but there is not a corresponding event recorded in the SQL Error Log.
I thought that all Maintenance transactions were recorded in Windows Events as well as the SQL Error Log or at the very least in the SQL Error Log. Am I wrong?
Regards, Irish
July 5, 2011 at 10:57 am
You should see a 12288 Event ID (I believe) of packages or jobs that the SQL Server Agent is firing.
The SQL Server ERRORLOG has some system events written to it and the user-defined events written to it are usually ones you configure to. The best place that I know of to see what jobs have fired would be the Windows Application Event Log.
You could also query the sysjobhistory table in msdb to get some more inforamtion as well. There are some nice scripts on SSC and Google that can pull all the information in a pretty format if you search around.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 5, 2011 at 11:05 am
Shawn Melton (7/5/2011)
You should see a 12288 Event ID (I believe) of packages or jobs that the SQL Server Agent is firing.The SQL Server ERRORLOG has some system events written to it and the user-defined events written to it are usually ones you configure to. The best place that I know of to see what jobs have fired would be the Windows Application Event Log.
You could also query the sysjobhistory table in msdb to get some more inforamtion as well. There are some nice scripts on SSC and Google that can pull all the information in a pretty format if you search around.
Thank you Shawn! Mostly I was looking for confirmation to ensure that I had not lost my mind.
I did check the sysjobhistory and found that there is history for one item set to run biweekly, but the monthly job (set to run the first Saturday of every 1 month) did not run. Near as I can tell, it did not even fire and there is no error.
Something else happened as a number of other Servers connected to that database indicated that the Database could not be contacted prior to the maintenance at 03:00. However, of the two jobs mentioned above, the Index Rebuild was more important that the biweekly History cleanup (which did run).
Regards, Irish
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply