Menu
In SQL Server Management Studio, when I "View History" for a SQL Job, I'm only shown the last 50 executions of the Job.
How can I view a full log of every execution of a SQL Job since it was created on the server?
384
SQL Server stores job information in the MSDB system tables. The table that stores historical job executions is msdb. dbo. sysjobhistory and the date/time information as well as the duration of the job execution is stored a little different than what you will see in most system tables.
Since we can only keep 10 history rows for any single job, the earlier execution gets truncated. SQL Server holds onto step 0 but deletes the history for steps 1 and 2. There is an also issue if you only want to keep an 8- or 12-hour rolling window of job history.
The SQL Server Job system limits the total number of job history entries both per job and over the whole system. This information is stored in the MSDB database.
Obviously you won't be able to go back and see information that has been since discarded, but you can change the SQL Server Agent properties and increase the number of entries that will be recorded from now on.
In the SQL Server Agent Properties:
It won't give you back your history, but it'll help with your future queries!
80
I'm pretty sure job history is stored somewhere in a dedicated database in SQL Server itself. If this is the case, you can use SQL Server Profiler to intercept SQL statements sent by SQL Server Management Studio and find out names of tables, etc.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
