| Author |
 Topic  |
|
Kristen
Test
United Kingdom
22191 Posts |
 Posted - 04/23/2006 : 08:33:21
|
You could use my sproc above - kk_SP_DeleteBackupHistory
That has a parameter for the "cutoff date" - see the bit of code in the "test rig" for finding a suitable cutoff date, based on deleting 2,500 records at a go (if that is too slow do less, if very quick then do more!):
-- 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
Kristen |
 |
|
|
tomsve
Starting Member
2 Posts |
Posted - 05/04/2006 : 05:17:35
|
I used your sp and ran it with
EXEC dbo.kk_SP_DeleteBackupHistory
@intDaysToRetain = NULL,
@dtCutoff = '15-Apr-2005'
,@intDebug=0
ROLLBACK
After six days of running, it stopped:
Server: Msg 3903, Level 16, State 1, Line 7
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
This is the sp I use:
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
Any ideas how to make it run? |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/04/2006 : 07:33:59
|
Sorry if this sounds harsh, but did you actually read my post?
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)
"After six days of running, it stopped:"
Did you read this bit?
-- 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
and underneath that I posted a means of determining a sensible Cutoff Date assuming a batch size of 2,500 records. You might be able to do more, or less, in a sensible amount of time depending on your server's performance, so it makes sense to try initial batches of maybe 10, then 100, and if that runs really quickly then 500, 1,000 and so on up to the point at which its starting to take 10 minutes or so, and then stick at that batch size until you have caught up with a cutoff date that you are comfortable with - e.g. one month's history is what we retain.
-- 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
and then you should have plugged that date into the EXEC - did you run my sample above and it gave you '15-Apr-2005'?, or did you just pick that date because its a couple of weeks ago and that's what you want to retain?
Either way, it will (TTBOMK) have deleted all the history up '15-Apr-2005', which I imagine is what you want.
IF you did NOT do the 2,500 batch size test, and given that it took 6 days to run I imagine that's the case, then it will probably have generated an enormous LOG file and you will need to carry out the other steps I outlined which will a) check their size, b) fix the fragmentation in the indexes and C) shrink the database back to a more reasonable size.
Make sure you take a backup of MSDB before you do any of those.
You then need to run the SProc regularly (daily or weekly) using the @intDaysToRetain parameter (and leaving the @dtCutoff parameter NULL) to keep the database under control. Or just use the MS provided sp_delete_backuphistory - but don't use that unless & until you are sure the size is under control and all old/stale records have been deleted. As per my examples you can see the earliest date recorded in MSDN using:
-- Oldest backup record in MSDB
SELECT MIN(backup_start_date)
FROM msdb.dbo.backupset
Kristen |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 05/08/2007 : 15:39:40
|
Looks like MS fixed their cursor code in SQL Server 2005. You no longer need to run this type of custom code.
You can now just run msdb.dbo.sp_delete_backuphistory if you are using SQL Server 2005.
Tara Kizer
http://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/09/2007 : 08:54:47
|
| Excellent! |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 11/08/2007 : 15:17:34
|
Hello All,
I have been trolling here for a few weeks, and I need some sage advice.
My msdb database is 2-4gb depending on weather or not I shrink it.
My DBA ran this procedure,
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx
and reduced the number of backup rows from ~800,000 to approximately 20,000
We run close to 200 individual databases on this particular server.
My problem is that MSDB refuses to cooperate, and is still 2-4gb in size.
I read most of this thread and while its over my head, I believe these are the steps I skipped.
My question being what, and which do i need to run to try and reclaim the rest of the space in the table. and, briefly what will these actually do?
Any and all help is greatly appreciated.
This post by Mcrowley
"
dbcc updateusage (0)
go
select sum(reserved)/128 as "Size in MB", object_name(id) as "Object Name"
from sysindexes
where indid in (0, 1, 255)
group by id
order by 1 desc
"
This Post by Kristen
"
Ah, ... [later] ... some success with DBCC DBREINDEX, size has now dropped from 196MB (before reindex) to 96MB. That's quite a lot less than last week when MSDB was 775MB!
" |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 11/08/2007 : 15:22:47
|
What version of SQL Server are you using? What does sp_spaceused show for the msdb database?
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 11/08/2007 : 15:39:53
|
Thank you for the fast response.
SQL Server 2000 (SP3)
dbo.sp_spaceused
Reserved Data index_size unused
2318000 KB 2259224 KB 54384 KB 4392KB
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 11/08/2007 : 15:44:55
|
Run DBCC UPDATEUSAGE to fix inaccuracies in sysindexes which is what sp_spaceused.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 11/08/2007 : 16:07:08
|
I do indeed love you.
:)
So the 700 or so MB the backup history is no offically cleaered out, the DBCC Update usage, gave me a list of tables and thier pages.
Fantastic, I may not totally understand sql, but
APEXSQL_LOG_LOGIN is 700,000+ pages, and 10,242,027 rows.
Wow, and Ok.
I consider this resolved, and I have some more trolling to do.
Thank you so much.
Allen
**updated** Woot 81.00 MB used
|
Edited by - tripodal on 11/08/2007 16:30:13 |
|
|
|
cjeremy74
Starting Member
USA
2 Posts |
Posted - 11/20/2007 : 12:13:17
|
Thanks to all for this... this is far and away the best solution that I have seen. When working with this for the first time - I also encountered the FK issue (I used the very first version). Since I was doing a bit of exploring - I ran it piece meal and found that the getdate() was changing enough between runs to cause me to delete additional rows (log shipping on 10+ db's on 5 min increments). What I ended up doing changing the calculation to a datetime variable so that it was constant from top to bottom. Just a thought for others.
Thanks again!!!
- J |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 11/20/2007 : 12:20:35
|
Just remember that this custom code is only needed for SQL Server 2000. MS fixed their code for 2005, so just use the one that comes with the product rather than a custom one when you move to SQL Server 2005.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
|
|
|
uryy4me
Starting Member
3 Posts |
Posted - 12/29/2008 : 15:48:42
|
| Is there an easy way to clean ALL the MSDB tables like sysmaintplan_logdetail, sysmail_attachments, sysmail_mailitems etc..? I only want to create one job for all of these...? |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/10/2011 : 07:38:33
|
Struggling with this again ... this time under SQL 2008.
Trying to delete a backlog from a server that has had no maintenance set up. It does not have a huge number of backup history records - there are 100,000 rows in [backupset]. Trying to delete up-to-a-date which would delete about 2,500 rows (and the data in associated tables) is taking so long I'm having to abort it.
After aborting @@TRANCOUNT = 3 which I am not understanding, as there is only a single BEGIN TRANSACTION in the source for sp_delete_backuphistory (unless some more on Triggers somewhere ....). Maybe I'm doing something stupid  (and if I abort it in Query Analyser I would have thought that would cause an automatic rollback, but perhaps not?)
sp_delete_backuphistory still has some goofy code - I mean  who does this sort of stuff:
DELETE msdb.dbo.backupmediaset
FROM msdb.dbo.backupmediaset bms
WHERE bms.media_set_id IN (SELECT media_set_id
FROM @media_set_id)
AND ((SELECT COUNT(*)
FROM msdb.dbo.backupset
WHERE media_set_id = bms.media_set_id) = 0)
a NOT EXISTS has got to be faster, surely?
None of the temporary tables have PK, so all the IN tests have to do a "Sort(DISTINCT ORDER BY:([ID_Column] ASC))" and there are some significant indexes missing. For example: creating a temporary tables of the "exists" IDs, in lieu of an index, speeds the thing up considerably.
This may only be an issue where there is a backlog of data to be deleted, but it does seem poor that the provided SP is not more efficient. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/10/2011 : 10:11:36
|
Even with my "improved" Sproc deleting 5,000 rows in [backupfile] is taking up to 15 minutes, and reducing the backlog by about two weeks :(
I reckon a couple of Indexes would speed the thing up hugely. Not keen to add those to system database though. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7004 Posts |
Posted - 12/10/2011 : 15:54:14
|
If you haven't done it already, you should reindex the msdb database. When I have seen slow history, running a reindex seems to help.
CODO ERGO SUM |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/11/2011 : 16:22:11
|
Thanks. I did do that, but not sure whether it was before, or after, I ran the slow-delete process.
I built an Sproc that deleted "gently" and looped round with a WAITFOR DELAY. It ran for several hours and cleared the backlog, so I'm sorted now, and thus can't check if that would have solved the issue. |
|
|
|
Topic |
|
|
|
Massive msdbdata.mdf
