SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Massive msdbdata.mdf
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/17/2004 :  06:36:25  Show Profile  Reply with Quote
quote:
Originally posted by tduggan

Could you post the modified code that you used?


Tara, to stop the thing going off the edge of the page I've broken some lines, but some of those are comments, so please reconnect lines that start with !!

Because of the mess that my MSDB was in I allow for deleting up-to a cutoff date, which allowed me to remove batches of around 2,500 backups bit-by-bit until I got within 60 days of today's date, and then I scheduled the task using the Retain Days parameter instead.

There's a bunch of bits&bobs in the TEST RIG at the bottom if ever you have to sort out someone elses MSDB mess!

Kristen


--
PRINT 'Create procedure kk_SP_DeleteBackupHistory'
GO
IF EXISTS (SELECT * FROM sysobjects 
           WHERE     id = object_id(N'[dbo].[kk_SP_DeleteBackupHistory]') 
                 AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
        DROP PROCEDURE dbo.kk_SP_DeleteBackupHistory
GO

CREATE PROCEDURE dbo.kk_SP_DeleteBackupHistory
        @intDaysToRetain        int=NULL,	-- Used for routine, daily, scheduled maintenance
	@dtCutoff		datetime=NULL,	-- Used to purge "old records" in batches

        @intDebug               int=0   -- 0=Off, 1=On

/* WITH ENCRYPTION */
AS

/*
 * kk_SP_DeleteBackupHistory    Purge backup history in MSDB database
 *
 *   Use the CutoffDate to remove batches from large MSDB bit-by-bit
 *   then schedule as a task using RetainDays to keep recent records
 *   See TEST RIG, below, for details
 *
 * Returns:
 *
 *      Nothing
 *
 * ERRORS:
 *
 * -1   No rows found
 * -2   Multiple rows found
 *
 * HISTORY:
 *
 * 12-Jun-2004 KBM  Started (From code provided by Tara Duggan 
 *                  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201 )
 */

SET NOCOUNT ON
SET XACT_ABORT ON

-- Local Variables
DECLARE	@intErrNo	int,
	@intRowCount	int

	-- Remove TIME
	SELECT	@dtCutoff = CONVERT(varchar(11), 
		COALESCE(@dtCutoff, DATEADD(day, -@intDaysToRetain, GetDate())), 113)

	IF @intDebug >= 1 SELECT [@intDaysToRetain]=@intDaysToRetain, 
				[@dtCutoff]=@dtCutoff

	BEGIN TRANSACTION kk_SP_DeleteBackupHistory_01

	DELETE FROM msdb..restorefile
	FROM	msdb..restorefile RF
		JOIN msdb..restorehistory RH
			ON RF.restore_history_id = RH.restore_history_id
		JOIN msdb..backupset BS
			ON RH.backup_set_id = BS.backup_set_id
	WHERE	BS.backup_finish_date < @dtCutoff
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	IF @intDebug >= 1 SELECT 'restorefile', @intErrNo, @intRowCount
	IF @intErrNo <> 0 GOTO kk_SP_DeleteBackupHistory_Abort

	DELETE FROM msdb..restorefilegroup
	FROM	msdb..restorefilegroup RFG
		JOIN msdb..restorehistory RH
			ON RFG.restore_history_id = RH.restore_history_id
		JOIN msdb..backupset BS
			ON RH.backup_set_id = BS.backup_set_id
	WHERE	BS.backup_finish_date < @dtCutoff
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	IF @intDebug >= 1 SELECT 'restorefilegroup', @intErrNo, @intRowCount
	IF @intErrNo <> 0 GOTO kk_SP_DeleteBackupHistory_Abort
	
	DELETE FROM msdb..restorehistory
	FROM	msdb..restorehistory RH
		JOIN msdb..backupset BS
			ON RH.backup_set_id = BS.backup_set_id
	WHERE	BS.backup_finish_date < @dtCutoff
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	IF @intDebug >= 1 SELECT 'restorehistory', @intErrNo, @intRowCount
	IF @intErrNo <> 0 GOTO kk_SP_DeleteBackupHistory_Abort

-- This bit moved further down
-- 	DELETE FROM msdb..backupfile
-- 	FROM	msdb..backupfile BF
-- 		JOIN msdb..backupset BS
-- 			ON BF.backup_set_id = BS.backup_set_id
-- 	WHERE	BS.backup_finish_date < @dtCutoff
-- 	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
-- 	IF @intDebug >= 1 SELECT 'backupfile', @intErrNo, @intRowCount
-- 	IF @intErrNo <> 0 GOTO kk_SP_DeleteBackupHistory_Abort
	
	SELECT media_set_id, backup_finish_date
	INTO	#Temp
	FROM	msdb..backupset BS
	WHERE backup_finish_date < @dtCutoff
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	IF @intDebug >= 1 SELECT '#Temp', @intErrNo, @intRowCount
	IF @intErrNo <> 0 GOTO kk_SP_DeleteBackupHistory_Abort

	DELETE FROM msdb..backupfile
	FROM	msdb..backupfile BF
		JOIN msdb..backupset BS
			ON BF.backup_set_id = BS.backup_set_id
		JOIN #Temp T
			ON BS.media_set_id = T.media_set_id
--	Changed to use JOIN instead of WHERE because some records were left behind
--	WHERE	BS.backup_finish_date < @dtCutoff
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	IF @intDebug >= 1 SELECT 'backupfile', @intErrNo, @intRowCount
	IF @intErrNo <> 0 GOTO kk_SP_DeleteBackupHistory_Abort

	DELETE FROM msdb..backupset
	FROM	msdb..backupset BS
		JOIN #Temp T
			ON BS.media_set_id = T.media_set_id
--	Changed to use JOIN instead of WHERE because some records were left behind
--	WHERE	backup_finish_date < @dtCutoff
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	IF @intDebug >= 1 SELECT 'backupset', @intErrNo, @intRowCount
	IF @intErrNo <> 0 GOTO kk_SP_DeleteBackupHistory_Abort
	
	DELETE FROM msdb..backupmediafamily
	FROM	msdb..backupmediafamily BMF
		JOIN msdb..backupmediaset BMS
			ON BMF.media_set_id = BMS.media_set_id
		JOIN #Temp T
			ON BMS.media_set_id = T.media_set_id
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	IF @intDebug >= 1 SELECT 'backupmediafamily', @intErrNo, @intRowCount
	IF @intErrNo <> 0 GOTO kk_SP_DeleteBackupHistory_Abort

	DELETE FROM msdb..backupmediaset
	FROM	msdb..backupmediaset BMS
		JOIN #Temp T
			ON BMS.media_set_id = T.media_set_id
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	IF @intDebug >= 1 SELECT 'backupmediaset', @intErrNo, @intRowCount
	IF @intErrNo <> 0 GOTO kk_SP_DeleteBackupHistory_Abort

	
kk_SP_DeleteBackupHistory_Abort:

	IF @intErrNo <> 0
	BEGIN
		ROLLBACK
		IF @intDebug >= 1 SELECT 'ROLLBACK'
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION kk_SP_DeleteBackupHistory_01
		IF @intDebug >= 1 SELECT 'COMMIT'
	END

	DROP TABLE #Temp

kk_SP_DeleteBackupHistory_Exit:

	SET NOCOUNT OFF

	RETURN @intErrNo

/* TEST RIG (Highlight a section and run in Query Analyser)

-- Oldest backup record in MSDB
SELECT	MIN(backup_start_date)
FROM	msdb.dbo.backupset

-- To sort out an existing MSDB old records must be deleted in batches
-- It takes a while to delete a large backlog - I did about 2,500 backups per batch

-- Find cutoff date - based on deleting 2,500 backups
SELECT	MAX(backup_start_date)
FROM	
(
	SELECT TOP 2500 backup_start_date
	FROM	msdb.dbo.backupset
	ORDER BY backup_start_date ASC
) X

-- Now set the BACKUP date (from above) to delete the oldest batch
EXEC dbo.kk_SP_DeleteBackupHistory
        @intDaysToRetain = NULL,
	@dtCutoff	 = '01-Jan-2004'
	,@intDebug=1
ROLLBACK	-- Just in case SP failed half way through 
-- (otherwise will leave a hanging transaction, ignore error if SProc was OK)

-- Move the date forward and repeat until up to the "Retain Days" from today


-- Other stuff that is useful in sorting out the problem:

-- Check out the size of the problem:
SELECT [Table]='backupfile', [Count]=COUNT(*) FROM msdb.dbo.backupfile
UNION ALL SELECT 'backupmediafamily', COUNT(*) FROM msdb.dbo.backupmediafamily
UNION ALL SELECT 'backupmediaset', COUNT(*) FROM msdb.dbo.backupmediaset
UNION ALL SELECT 'backupset', COUNT(*) FROM msdb.dbo.backupset
ORDER BY 1

-- Check size of objects in MSDB
SELECT	[Size in MB] = SUM(IDX.reserved)/128,
	[Object Name] = OBJ.name
FROM	msdb.dbo.sysindexes IDX
	JOIN msdb.dbo.sysobjects OBJ
		ON OBJ.id = IDX.id
WHERE	IDX.indid IN (0, 1, 255)
GROUP BY IDX.id, OBJ.name
ORDER BY 1 DESC

-- Traditional microsoft method 
--    (Dog slow, don't attempt more than a couple of hundred deletions)
-- USE msdb
-- EXEC sp_delete_backuphistory @oldest_date = '16-Jan-2002'

-- Check File sizes
EXEC master.dbo.xp_cmdshell 'DIR D:\MSSQL\DATA\msdb*.* /ON'

-- Check fragmentation
USE MSDB
DBCC SHOWCONTIG (backupset) WITH ALL_INDEXES

-- Fix!! fragmentation
DBCC DBREINDEX (backupset) 
DBCC DBREINDEX (backupmediafamily) 
DBCC DBREINDEX (backupfile) 
DBCC DBREINDEX (backupmediaset) 

-- Shrink DB - NOTE: May need repeated use to do full shrink
USE MSDB
SELECT [--SQL]='DBCC SHRINKFILE (', fileid, ') --', size, name, CHAR(13)+CHAR(10)+'GO'
FROM	msdb.dbo.sysfiles

-- Fix up Usage Stats on MSDB
USE MSDB
DBCC UPDATEUSAGE (0)

 */
--================== kk_SP_DeleteBackupHistory ==================--
GO

PRINT 'Create procedure kk_SP_DeleteBackupHistory DONE'
GO


Kristen

Edited by - Kristen on 04/23/2006 08:34:43
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37161 Posts

Posted - 06/17/2004 :  13:42:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
Thanks!

Tara
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/17/2004 :  14:04:21  Show Profile  Reply with Quote
My pleasure

Kristen
Go to Top of Page

cjsuarez
Starting Member

USA
1 Posts

Posted - 07/01/2004 :  12:58:51  Show Profile  Reply with Quote
totally safe.
Just run this statement in msdb database and the execution of "sp_delete_backuphistory" will fly:
create index tmp_backupset_CJS on backupset (media_set_id)

you can drop the index at the end of the clean up if you want to.

quote:
Originally posted by Kristen

That'll be the ticket, thanks Tara. Each time this sort of thing comes up I just think of the number of unsuspecting SQL users out there who have neither DBA nor SQLTeam ...

OK, I set it running from the date of the 100th oldest record. It took 3 minutes - there will be 8,000 batches to run at 100 a go :-(

I saw something on GOOGLE about setting an Index on backupset(media_set_id) speeding up the rate from 4 mins PER ROW to 5,000 rows PER MINUTE. Safe to do that do you think?

Kristen

Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/02/2004 :  14:08:14  Show Profile  Reply with Quote
nice. This is a sproc that I always wanted to re-code but just never got around to it.

Thanks Tara and Kristen for doing such an excellent job. btw, this is definitely something that every SQL Server DBA should schedule to run on a weekly basis.


-ec
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37161 Posts

Posted - 07/02/2004 :  15:19:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
And I blogged on it:

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx


And here's a list of all the dba routines I blogged:

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx


Tara
Go to Top of Page

shay
Starting Member

USA
1 Posts

Posted - 05/06/2005 :  14:51:49  Show Profile  Reply with Quote
This procedure is great. I was running log shipping for two years with 52 tables at 5-180mins each (mostly 5) I nearly 2 million rows in my backup tables. I noticed when the server started crashing.

running sp_delete_backuphistory took 3.5hours per day deleted whereas isp_deletebackuphistory took 1.5 mins per day (about 140 times faster!)

Anyways I'm writing because I also had the foreign key problems and had to fix them. The reason they exist is occassionally sql server will reuse a media set id at a later date, but the procedure is assuming all media set id's used before the specified date should be deleted and this causes foreign key problems.

The fixes for this are to add the following lines to the end of the queries

line 75
where bs.backup_finish_date < ( getdate()-@DaysToRetain)

lines 89 and 103
where not exists
(select media_set_id from msdb..backupset bs
where bs.media_set_id=t.media_set_id and bs.backup_finish_date> (getdate()-@DaysToRetain))


These lines keep you from delete media_set_ids that are still in use by other backupsets

Edited by - shay on 05/06/2005 14:52:48
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/06/2005 :  16:50:23  Show Profile  Reply with Quote
If it helps, I always setup the following code in a job to run once per days to keep the backup history down to 30 days:


set nocount on
declare @purge_date datetime

while exists (	select top 1 backup_finish_date from msdb.dbo.backupset
		where datediff(dd,backup_finish_date,getdate()) > 30)
	begin

	select
		@purge_date = 
		convert(char(10),dateadd(dd,1,min(backup_finish_date)),101)
	from
		msdb.dbo.backupset
	where
		datediff(dd,backup_finish_date,getdate()) > 30
	
	print 'Purging backup history before ' + 
		convert(char,@purge_date) +char(10) +char(13)
	
	select
		count(*) Backup_Set_Count
	from
		msdb.dbo.backupset
	where
		backup_finish_date < @purge_date
	
	exec msdb.dbo.sp_delete_backuphistory @purge_date

	end




CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37161 Posts

Posted - 05/06/2005 :  16:54:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
Michael,

Take a look at the code inside sp_delete_backuphistory. It's horrible! Now look at the set-based solution: isp_deletebackuphistory. Compare the performance. You'll see a huge gain with my version.

Of course it does occassionally get these FK errors as mentioned here and also in the blog comments, but I never had time to figure out why so I just setup my job to retry twice as the FK errors went away after the second run. Now shay has posted a solution to the FK problem.

Tara
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/06/2005 :  17:11:35  Show Profile  Reply with Quote
Tara,

I am aware that of the problems with the performance of that stored procedure. That is why I set it up to run once per day. I just looked, and it runs 5 seconds or less on all of our servers, so if you don't let it get out of hand, performance is not a problem.

If you are purging off a couple of years worth of backup info, you will be waiting for a while for it to complete.

I made this a part on my standard SQL Server maintenance jobs a couple of years ago, and haven't had to worry about it since.





CODO ERGO SUM

Edited by - Michael Valentine Jones on 05/06/2005 17:16:59
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/27/2005 :  00:13:43  Show Profile  Reply with Quote
I've just revisited this for another post. Point to add: We increase the "File Growth" on MSDB Data and Log to 10MB - the default is 1MB, and for a database which is probably going to be 100MB or so that's a lot of fragmentation!

Kristen
Go to Top of Page

jncoign
Starting Member

1 Posts

Posted - 11/14/2005 :  16:54:51  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Could you post the modified code that you used? We occassionally get the FK error as well, but we never found the time to go back and modify it. The next time it ran, it always worked and since this process wasn't that important to us, we just left it as is.

Tara

Go to Top of Page

Westley
Posting Yak Master

229 Posts

Posted - 11/17/2005 :  01:17:16  Show Profile  Reply with Quote
Guys,
I did add the index on the msdb sys tables (dbo.backupset) just to speed up the delete (although I already have a weekly task for it), just wondering what will be the down side of it? All I can think of is new releases that might overwrite your index, apart from that, can't see any reason not to. Any ideas?
Thanks
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/17/2005 :  01:41:10  Show Profile  Reply with Quote
When I update a client's database my scripts do not do anything to anticipate that they have changed the structure of the database, and may very well fail because of it.

A CONSTRAINT or somesuch would definitely be high on the list of fail points. An Index is pretty benign.

At the very least, in your scenario, I would drop the index before installing any service pack/hotfix/etc. consider whether it was still needed, and reapply it if appropriate.

Question is will whoever installs the SP know/remember to do this?

Kristen
Go to Top of Page

DMcCallie
Yak Posting Veteran

USA
62 Posts

Posted - 11/17/2005 :  13:57:04  Show Profile  Reply with Quote
I use the following in a scheduled job that runs once a week to delete backup history older than 1 month:

use msdb
go

Declare @date datetime
Set @date = dateadd (mm, -1, getdate())
Exec sp_delete_backuphistory @date
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/17/2005 :  14:04:43  Show Profile  Reply with Quote
DMcCallie:

The problem is that that is desperately slow (must have been written by a Summer Student!). If you have a half-decent number of databases on your server, and take TLog backups every 10 minutes or so, running it once a month will be painful!

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37161 Posts

Posted - 11/17/2005 :  14:06:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
Did DMcCallie even read this whole thread?!!! We already discussed sp_delete_backuphistory!

Tara Kizer
aka tduggan
Go to Top of Page

pmickolpa
Starting Member

1 Posts

Posted - 03/29/2006 :  10:52:22  Show Profile  Reply with Quote
quote:
Originally posted by shay

This procedure is great. I was running log shipping for two years with 52 tables at 5-180mins each (mostly 5) I nearly 2 million rows in my backup tables. I noticed when the server started crashing.

running sp_delete_backuphistory took 3.5hours per day deleted whereas isp_deletebackuphistory took 1.5 mins per day (about 140 times faster!)

Anyways I'm writing because I also had the foreign key problems and had to fix them. The reason they exist is occassionally sql server will reuse a media set id at a later date, but the procedure is assuming all media set id's used before the specified date should be deleted and this causes foreign key problems.

The fixes for this are to add the following lines to the end of the queries

line 75
where bs.backup_finish_date < ( getdate()-@DaysToRetain)

lines 89 and 103
where not exists
(select media_set_id from msdb..backupset bs
where bs.media_set_id=t.media_set_id and bs.backup_finish_date> (getdate()-@DaysToRetain))


These lines keep you from delete media_set_ids that are still in use by other backupsets

Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 04/06/2006 :  13:28:35  Show Profile  Reply with Quote
Shay, I do not fully understand your instructions to modify the sproc. Could you post the modified code with the updates? I am also getting the foreign key errors. Thanks Kristen, Tara and Shay! This is great!
Go to Top of Page

tomsve
Starting Member

2 Posts

Posted - 04/23/2006 :  05:53:44  Show Profile  Reply with Quote
I also have a massive msdb. Currently it's 7,03 GB now so i'm trying to use the sp you wrote about in this thread to make it smaller.

It's something weird about my msdb...


SELECT count(backup_start_date)
FROM msdb.dbo.backupset

The query above have been running for 96 hours and is still running, "Executing query batch...".


exec isp_deletebackuphistory 373
Took just a couple of seconds to run.

exec isp_deletebackuphistory 372
Took just a couple of seconds to run.

exec isp_deletebackuphistory 371
Took just a couple of seconds to run.

exec isp_deletebackuphistory 370
Have been running for 107 hours and is still running.

Any ideas or suggestions what I can do do make it smaller?
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000