Need Script/Info On How to Delete Old Backup History Records

Question

Post reply

Need Script/Info On How to Delete Old Backup History Records

JC-3113

SSCrazy Eights

Points: 8371
More actions
November 12, 2009 at 3:17 pm

#217614

Hi Folks
can someone point me in the right direction as to where there might be a script that will clean out/delete all the backup history records from all the different tables based on a specified date ?
Looks like there are multiple tables involved
I have a maint plan that i generated to delete records older than 7 days and it runs once a week , but it looks like it does not work even though the history of the plan shows successful the T-SQL is as follows:
declare @dt datetime select @dt = cast(N'2009-11-05T14:09:09' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt
GO
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2009-11-05T14:09:09'
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,'2009-11-05T14:09:09'
Report Output:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4035
Report was generated on "NSAB-SS80-SQL-N".
Maintenance Plan: MP-Maintenance-Cleanup
Duration: 00:00:01
Status: Succeeded.
Details:
Execute T-SQL Statement Task (NSAB-SS80-SQL-N)
Execute TSQL on Local server connection
Execution time out: 0
Task start: 2009-11-12T14:15:53.
Task end: 2009-11-12T14:15:54.
Success
Thanks
Jim

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

There's a history cleanup task in the maintenance plans that you can use if you like. It runs this SQL:

declare @dt datetime select @dt = cast(N'2009-10-15T18:28:54' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt

GO

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2009-10-15T18:28:54'

GO

EXECUTE msdb..sp_maintplan_delete_log null,null,'2009-10-15T18:28:54'

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Here's what I use to cleanup MSDB backup and mail history:

DECLARE @oldest_date DATETIME

SELECT

@oldest_date = DATEADD(MONTH, -6, MAX(Backup_finish_date))

FROM

msdb.dbo.backupset;

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest_date;

SELECT

@oldest_date = DATEADD(MONTH, -6, MAX(send_request_date))

FROM

msdb.dbo.sysmail_allitems AS SA;

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @oldest_date;

SELECT

@oldest_date = DATEADD(MONTH, -6, MAX(SL.log_date))

FROM

msdb.dbo.sysmail_log AS SL;

EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @oldest_date;

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

I like to keep the backup history information. I find it useful for analyzing database growth over time. It doesn't take up much space, why not leave it there ?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Homebrew01,

How long do you need that for? The backup history is never cleaned up unless you do it. Here's one person's reason why, http://feedproxy.google.com/~r/BrentOzar-SqlServerDba/~3/NPNxT9sz2uE/

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

You can also delete the history with a maintenance plan.

There is a pre-defined task just for that, the History Cleanup Task.

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

Jack Corbett (11/13/2009)
Homebrew01,
How long do you need that for? The backup history is never cleaned up unless you do it. Here's one person's reason why, http://feedproxy.google.com/~r/BrentOzar-SqlServerDba/~3/NPNxT9sz2uE/

Interesting ! Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

No problem. Until fairly recently I didn't even know you had to consider it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

Jack Corbett (11/13/2009)
Homebrew01,
How long do you need that for? The backup history is never cleaned up unless you do it. Here's one person's reason why, http://feedproxy.google.com/~r/BrentOzar-SqlServerDba/~3/NPNxT9sz2uE/

Since t-log backups are most of the history, it's quite easy to make a user version of sp_delete_backuphistory to delete all the log backup history, but keep the full history. I just created usp_delete_backuphistory_Log & may try it out.

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 9

Hi Folks

thanks to you all for the feedback

This is a developer server and i only keep backupsi for three days so no need to keep the history

I run reports every night to look at growth

Also i had used the Maint Plan and that what was not working until I re-did it

It works now, so I am not sure what the issue was

It sees everytime a database is deleted, my maint plans crap out, probably because i select the databases in my maint plans andSQL SEVER does not seem smart enough to remove them from the plans after deletion

Anyway, thanks again

Jim