SQL Server Full Job History

SQL Server history information is stored in the MSDB database and for ease SQL Server Job system limits the total number of job history entries both per job and over the whole system.

But this could be issue if one of my job is running every 10 mins and other is running weekly. You  must not want to loose history for any job.  Sql server gives you a way to fix this

Go to SSMS --> right click on sql server agent --> Property --> History

 


If you see above screen shot , SQL server gives us way to set over all history in no. of rows and no. of rows per job. This will help us to keep history for all jobs

SYNTAX :

Job history max rows:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=<No. of rows int>,@email_save_in_sent_folder=1
GO

Job history max rows per job:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows_per_job=<No. of rows int>, @email_save_in_sent_folder=1
GO