sp_purge_jobhistory
We can use this procedure to delete old history for jobs.
sp_purge_jobhistory
[ @job_name
or [ @job_id ,
@oldest_date
We can specify either job_name or job_id but not both.
We can get job_id from msdb.dbo.sysjobs table . job_id is uniqueidentifier.
If we specify @oldest_date then it deletes all his troy before this date else it will delete all job history.
Example
To delete all history for a specific job
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_name = N'Test job' ;
To delete history for a specific job upto specific date
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_name = N'Test job' ,
@oldest_date = '2008-10-01'
To delete history for all jobs
EXEC dbo.sp_purge_jobhistory
We can use this procedure to delete old history for jobs.
sp_purge_jobhistory
[ @job_name
or [ @job_id ,
@oldest_date
We can specify either job_name or job_id but not both.
We can get job_id from msdb.dbo.sysjobs table . job_id is uniqueidentifier.
If we specify @oldest_date then it deletes all his troy before this date else it will delete all job history.
Example
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_name = N'Test job' ;
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_name = N'Test job' ,
@oldest_date = '2008-10-01'
EXEC dbo.sp_purge_jobhistory
2 comments:
Thanks for that just what I was looking for today. Studio has decided to choke when trying to clear a job history
You can build a dynamic script which then can be executed from w Windows schedule using "sqlcmd":
SELECT 'use msdb' + char(13) + char(10) +
'EXEC dbo.sp_purge_jobhistory @job_name = N' + '''' + name + '''' + char(13) + char(10) +
', @oldest_date = ' + '''' + convert(varchar, DATEADD(dd, -16, GETDATE())) + ''''
FROM msdb.dbo.sysjobs_view
where name like 'Backup%'
go
Save it in an .SQL script, then run:
sqlcmd /h-1 -i "D:\DBA Scripts\gen_clr_job_hist.sql" -o "D:\DBA Scripts\gen_clr_job_hist.lst"
then execute:
D:\DBA Scripts>sqlcmd -i "D:\DBA Scripts\gen_clr_job_hist.lst"
Changed database context to 'msdb'.
0 history entries purged.
Changed database context to 'msdb'.
2 history entries purged.
Changed database context to 'msdb'.
0 history entries purged.
Post a Comment