SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Faster Backuphistory delete

By Doran Mackay,

Should be orders of magnitude faster than the default cleanup proc.

Mine was running for 25 minutes when I decided to kill it and add a small change.

The solution to the slowness was to replace all the table variables in the MS proc with temp tables.

Additionally, added clustered primary keys to the temp tables.

Choose how much history you want to keep and assign it as per below.

declare @olddate datetime
declare @var int = -120
set @olddate = DATEADD(day,@var,GETDATE())
exec [dbo].[faster_delete_backuphistory] @oldest_date = @olddate

Be careful as it may complete the task before you have a chance to stop it in SSMS, so test it first to see if it meets your needs.

Total article views: 257 | Views in the last 30 days: 4
 
Related Articles
FORUM

function help -

here are the errors i am getting Msg 102, Level 15, State 1, Procedure fn_DateTime, Line 8 Incor...

FORUM

difference

Difference

FORUM

Auto-roll date

I would like to take an action to move forward a specific date in a SQL table to the 'next business ...

FORUM

How to calculate Quarterly Dates?

Hi friends, How can i calculate quarters for the past 10 years. Thanks, Sam [quote][b]Sahas...

FORUM

xp_deletefile on linked server problem

Hails, I'm having a problem with the procedure 'master.dbo.xp_delete_file' and I'm receiving the ...

Tags
backuphistory    
 
Contribute