Hello:

In SQL Server 2000 with service pack 4, I am running the script at the end
of this posting to delete backup history.

The reason that I am running this huge script against the msdb database in
the first place is because the msdb database has been approaching 8GB in size
and has grown by a sizable margin daily. I need to reduce its size.

I started running this script at 2:30 PM EST, and it is still running at the
time of this posting (almost 8 hours)! I am aware that this script is
supposed to take a long time, and there are 86 databases in this environment.
Not to mention, there are over two years of backup history that has never
been previously deleted.

But, is it suppposed to take this long? I really hope that this finishes
before Monday AM.

I noticed that, while this has been running, the size of msdb has remained
unchanged. Is that normal?

In order to speed up this process, can I run the "EXEC
SP_DELETE_BACKUPHISTORY @backup_date" script based on a short-term date
range? Or, would that not do any good?

Currently, I have disabled all backup jobs since no one is working in the
system this weekend anyway. If this script is still running come Monday, I
suppose that I will let it run, change the recovery model for all databases
to Simple, and keep the backup jobs including transaction log backup jobs
disabled. There's no harm in having this script continue to run while pepole
are in it, is there? Or, should I stop it if it keeps running like this?

SQL Programmer (it's just a name)


set nocount on

declare @purge_date datetime

declare @cutoff_date datetime



-- Set purge cutoff to 30 days back

set @cutoff_date = dateadd(dd,datediff(dd,0,getdate())-30,0)



print 'Purge backup history before Cutoff Date = ' +

convert(varchar(10),@cutoff_date ,121)



while 1 = 1

begin



set @purge_date = null



-- Find date of oldest backup set

select

@purge_date =
dateadd(dd,datediff(dd,0,min(backup_finish_date))+ 1,0)

from

msdb.dbo.backupset

where

backup_finish_date <= @cutoff_date



if @purge_date is null or @purge_date > @cutoff_date

begin

print 'Purge backup history complete through '+

convert(varchar(10),@cutoff_date ,121)

break

end



print char(10)+char(13)+'Purging backup history before ' +

convert(varchar(10),@purge_date,121) +char(10)+char(13)



select

[Backup Sets to be Deleted Count ] = count(*)

from

msdb.dbo.backupset

where

backup_finish_date < @purge_date



exec msdb.dbo.sp_delete_backuphistory @purge_date



end -- End While

Well, it's still running as of 7:30AM on Sunday.

I guess, now, I need to know if there is any harm in hitting the stop button
in Query Analyzer on that script that is running and just run the following a
little at a time:

USE msdb
EXEC sp_delete_backuphistory 'oldest date'

SQL Programmer (it's just a name)

"SQL Programmer" wrote:

I am not sure where you got that script but it seems quite complex for such
a simple task. If you had 2 years worth of backup history and specified a
date of say 2 weeks ago it would indeed take a very long time. But the
problem is if you stop the delete it will roll back all the changes and you
are back where you started. There are a couple issues with this in SQL
2000. One is that they did not index the tables and that caused the deletes
to be very slow. The other is the fact you have so much history to begin
with it will also add to the time it takes. In any case you need to start
off with deleting them in smaller batches. Have a look at this blog from
Geoff that explains how to add the indexes if they are not there. Then call
the sp_delete_backuphistory sp with the date parameter such that you delete
say a few months worth at a time. Once you get it to just a few weeks this
sp call will only take a second. Then setup a job and run this once a week
or so. But even when you delete the history you will need to reclaim that
free space by running DBCC SHRINKDATABASE or DBCC SHRINKFILE on MSDB.

http://weblogs.sqlteam.com/geoffh/ar...ce-Tuning.aspx

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"SQL Programmer" <SQLProgrammer (AT) discussions (DOT) microsoft.com> wrote

disregard....I went ahead and stopped that big script as it was taking too
long.

I am now running EXEC SP_DELETE_BACKUPHISTORY 'oldest_date' one month at a
time.

Disappointed that I did not get any responses here....usually the SQL
newsgroups is pretty "quick".


"SQL Programmer" wrote:

SQL Programmer (SQLProgrammer (AT) discussions (DOT) microsoft.com) writes:
Judging from the times you specify in your articles, you are located in
India or similar. Many of us answering these questions are in Europe or
North America, where we celebrate this thing we call Christmas, so the
activity here is a little low. :-)


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks, Andrew! The advice that you gave me worked perfectly!!!

SQL Programmer (it's just a name)

"Andrew J. Kelly" wrote: