Massive msdbdata.mdf - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Massive msdbdata.mdf

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-06-11 : 12:25:35

Just checked database filesizes on a server that is getting rather full.

msdbdata.mdf is 750MB

There are 63 .MDF files on this server, total size (including msdbdata.mdf) is 1,353MB so the MSDB looks uncharacteristically large to me.

I had a look in EM at View : Taskpad and then [Table Info] and all the tables and indexes show only a few KB - it doesn't add to more than a couple of MB, let alone 750MB !!

All Tasks : Shrink Database suggests it can get back only 1.13MB

Log file is 3MB

MSDB is set to Simple / Auto update stats / Auto create stats / Torn page detection / Cross DB chaining

Its never had a DBCC re-index or anysuch.

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 12:39:23

DBCC UPDATEUSAGE after DBCC SHRINKFILE.

Tara

Kristen
Test

22859 Posts

Posted - 2004-06-11 : 12:59:52

(Thanks Tara, I did the DBCC's but no change)

Ah ... View : Taskpad : [Table info] does not include System tables :-(

I did some COUNT(*) instead:

backupfile 874731
backupmediafamily 836259
backupmediaset 836259
backupset 836349

something isn't clearing those beauties down.

Backup files are being deleted (by SQL Server) after the couple of weeks they are configured for. So I presumably need something else to purge this crud?

I'm not sure I need details of every transaction log back to April 2002 !

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 13:01:50

sp_delete_backuphistory is a system stored procedure that can delete the old backup history.

Tara

Kristen
Test

22859 Posts

Posted - 2004-06-11 : 13:23:14

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 13:26:02

I have never added any indexes on any system tables nor will I ever. I've read MS articles that have recommended not doing it either. I'm not saying that it wouldn't be safe, but I just don't see any point to it. We run sp_delete_backuphistory once per week in our production environment to keep the size down. The size gets pretty large due to our backing up the transaction log every 15 minutes. So as long as you delete this data occassionally, you shouldn't have a performance problem on the delete.

Tara

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2004-06-11 : 13:38:44

Another place to check would be previous (outdated) versions of DTS packages. The little things are notorious for soaking up space unobtrusively. In Enterprise Manager, right click a package, and check versions.

If it turns out to be the backupset table, how long has this server been alive? And how many backups per day?

Kristen
Test

22859 Posts

Posted - 2004-06-11 : 13:45:20

Yup, I'm happy with that once I've got it down to a manageable size, its just how long it will take to get there - and what the impact on the server might be whilst its busy clearing it down.

The table is marked as TYPE='U' in sysobjects, but I suppose that's splitting hairs!

At current speed it's going to take 5 days, running flat out, to clear the backlog :-(

I suppose there is some good reason why sp_delete_backuphistory is creating a cursor to step through each backup, and then delete the associated rows from three, or so, other tables - rather than just deleting everything using a few JOINs. I mean ... you guys told me cursors were the work of the devil, right?!

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 13:47:59

Let me pull out what I've actually got scheduled in production. I lied when I said I run sp_delete_backuphistory each week. I actually pulled out some of the code from the system stored procedure and created my own.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 13:50:05

[code]

CREATE PROC isp_DeleteBackupHistory
(@DaysToRetain INT)
AS

SET NOCOUNT ON

DELETE FROM msdb..restorefile
FROM msdb..restorefile rf
INNER JOIN msdb..restorehistory rh ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs on rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg
INNER JOIN msdb..restorehistory rh ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs on rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh
INNER JOIN msdb..backupset bs on rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..backupfile
FROM msdb..backupfile bf
INNER JOIN msdb..backupset bs on bf.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SELECT media_set_id, backup_finish_date
INTO #Temp
FROM msdb..backupset
WHERE backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..backupset
WHERE backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..backupmediafamily
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupmediaset bms ON bmf.media_set_id = bms.media_set_id
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id

DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id

DROP TABLE #Temp

SET NOCOUNT OFF

GO

[/code]

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-11 : 15:20:03

I'm just curious...why would msdb be so big?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 16:00:45

Did you read the whole thread? He's got lots of data in the msdb..backup tables. And mcrowley points to old versions of DTS packages possibly consuming a lot of space too. We had so much data in the backup history tables that we had to implement the stored procedure that I posted. We run it once a week.

Tara

Kristen
Test

22859 Posts

Posted - 2004-06-12 : 03:47:58

quote:
Originally posted by X002548

I'm just curious...why would msdb be so big?

30-ish databases, transaction backup ever hour (thank goodness we didn't go to 10 minutes yet!), server has been running (including SQL version upgrades) since 2001. That's 24*365 * 3years = 26,000 backups per database; Times 30 databases = 800,000 rows.

MS saw fit to use 4 tables to store this stuff, so their SProc to purge the old ones uses a cursor and takes forever - apart from the fact that I've only just stumbled across the need to do this housekeeping on MSDB :-(

Their SProc takes 5 minutes to delete EACH DAY'S records; that's 24*30 rows in each of 4 tables. I mean, come on guys, if I posted something of that quality here you lot would roast me!

But of course I'm also embarrased that I never spotted this little blighter growing merrily :-( but I'm looking forward to the consultancy fees in knowing how to fix this for any walk-in-customers!

That reminds me, I must change our documentation to require transaction backups every 10 mintes. No, that's not right - every minute ... maybe even every couple of milliseconds ... !!

Kristen

Kristen
Test

22859 Posts

Posted - 2004-06-12 : 04:49:24

quote:
Originally posted by tduggan

Let me pull out what I've actually got scheduled in production. I lied when I said I run sp_delete_backuphistory each week. I actually pulled out some of the code from the system stored procedure and created my own.

That's what the MS Stuff should be doing - a bunch of JOINs instead of that horrendous cursor.

Many thanks Tara, I'll give it a whirl.

(Would you advise a transaction block to force the deletes to only happen as-one in case something goes wrong?)

Kristen

Edit: P.S.
Delete 10 days log the MS

way 2 hours 5 minutes.
Delete 10 days log the Tara

way 32 seconds.

Kristen
Test

22859 Posts

Posted - 2004-06-12 : 11:43:56

quote:
Originally posted by tduggan

[code]

CREATE PROC isp_DeleteBackupHistory

Tara, I had to make some changes because I was getting FK violation.

I moved the delete on "backupfile" to be after creating the #TEMP table and changed "backupfile" and "backupset" to delete based on a join to #TEMP.

Without this the last delete, on "backupmediaset", had some parent stuff left in those two tables; I can't figure out hww this could behave differently to what you had, but maybe there is/was some duff data in my MSDB tables.

You guys got any other regular maintenance stuff that I could learn about?

Kristen

Kristen
Test

22859 Posts

Posted - 2004-06-14 : 14:51:48

Thanks to your help, Tara, I've castrated msdb, but its still a bit disappointing. I've purged MSDB [from 2001] to 1st April 2004, record count in the BACKUP tables has fallen from


12-Jun-2004:
backupfile        871264
backupmediafamily 832946
backupmediaset    832946
backupset         833034


14-Jun-2004:
backupfile         88045
backupmediafamily  84191
backupmediaset     84191
backupset          84191

but size has only fallen (have just SHRUNK the DB):


11/06/2004  18:16          774,307,840 msdbdata.mdf
14/06/2004  19:28          192,086,016 msdbdata.mdf

There doesn't appear to be any other [significant] busy table in msdb :(

Kristen

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2004-06-14 : 16:39:06

See what happens when you run the following:

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-14 : 20:18:29

Kristen, shrink each file seperately. Run each one about 3 times consecutively.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-06-14 : 23:34:05

Well, I've done that. The SHRINKFILE gave me about a 15% saving. The "size in MB" said that the biggest table, backupset, was 76MB. I hand calculated the size (based on the actual nvarchar SUM(DATALENGTH(EachColumn)) and that came to 33MB, so I reckon there is still some space in that table ... perhaps I should defragment/reindex it now I've deleted so much stuff (it has a CLUSTERED PK)

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!

I really ought to get the hang of which DBCC thingies I should be doing routinely ...

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-14 : 23:37:50

No you should be scheduling it to be done routinely. Then, you can just sit back and enjoy the roses.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-16 : 12:28:02

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources