Job history

Question

Job history

Mark Shvarts 1

SSCertifiable

Points: 5702
More actions
October 16, 2006 at 9:16 am

#170103

SQL Server appears to have only 100 records in msdb.dbo.sysjobhistory for each job step. Is there a configuration setting to remove this limitation ?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Yes. For example when you create a maintenance plan you should specify the number of records in sysdbmaintplan_history. The general settings for sysjobhistory are under Agent properties Job System tab.

Regards,Yelena Varsha

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 2

Thanks Yelena, this is good, I did not know about this.

Thanks

Ally MacKenzie Right there with Babe Points: 799 More actions · Answer 3

You can alter this setting in the Job system tab of SQLServerAgent properties.

Default is 100 job history rows per job.

Alasdair

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

Don't forget this one... it will delete old db backup history. Otherwise your backup history will get quite large and slow down to a crawl when you want to restore a db...

run like this..

sp_delete_backuphistory '08/01/06' the date is delete older than..

BEGIN TRANSACTION

DECLARE @JobID BINARY(16)

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)

SELECT @JobID = job_id

FROM msdb.dbo.sysjobs

WHERE (name = N'Cleanup Old backup History')

IF (@JobID IS NOT NULL)

BEGIN

-- Check if the job is a multi-server job

IF (EXISTS (SELECT *

FROM msdb.dbo.sysjobservers

WHERE (job_id = @JobID) AND (server_id <> 0)))

BEGIN

-- There is, so abort the script

RAISERROR (N'Unable to import job ''Cleanup Old backup History'' since there is already a multi-server job with this name.', 16, 1)

GOTO QuitWithRollback

END

ELSE

-- Delete the [local] job

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Cleanup Old backup History'

SELECT @JobID = NULL

END

BEGIN

-- Add the job

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Cleanup Old backup History', @owner_login_name = N'dbadm02', @description = N'deletes old job backup/restore history older than date supplied with SP execution. 11/09/2005 ms', @category_name = N'[Uncategorized (Local)]', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'STEP01', @command = N'sp_delete_backuphistory ''09/01/05''', @database_name = N'msdb', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'E:\delete_old_backup_history.txt', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 5

Thanks Markus, This is good script. I will use it in our environment.