Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Store More SQL Agent Job History Than The Default

From Wiki

Jump to: navigation, search

By default only a 1000 rows are stored in the jobs history table, so if you have a lot of jobs running you might be missing history. I had for example jobs that ran once a minute and I had several of those. Looking in the history I noticed that the history was missing; if you have the same problem, here is how to fix it. Right click on the SQ Server Agent icon, select properties and then click on history. You should see the following window.

history.PNG


As you can see you can now uncheck Limit size of job history log or you can increase the row value from 1000 to something bigger.


Of course you can also do this with T-SQL

Unlimited history

  1. USE [msdb]
  2. GO
  3. EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=-1,
  4.         @jobhistory_max_rows_per_job=-1
  5. GO


History capped at 100000 rows

  1. USE [msdb]
  2. GO
  3. EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=100000
  4. GO


History capped at 50000 rows

  1. USE [msdb]
  2. GO
  3. EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=50000
  4. GO


Contributed by: --SQLDenis 19:31, 10 May 2010 (GMT)

Part of SQL Server Admin Hacks

769 Rating: 2.6/5 (41 votes cast)