Huge MSDB, cannot shrink - 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 2008 Forums

SQL Server Administration (2008)

Huge MSDB, cannot shrink

Author

Topic

mmcnary
Starting Member

16 Posts

Posted - 2010-06-01 : 11:10:46

I inherited an instance with a 65GB (yes gigabyte!) msdb. This is sql2008. I have verified that I do not have excessive backup history:

select count(*) from backupfile
-----------
252

select count(*) from backupmediafamily
-----------
112

select count(*) from backupmediaset
-----------
112

select count(*) from backupset
-----------
112

I have verified that I do not have any email attachments taking up space:

DECLARE @GETDATE datetime
SET @GETDATE = GETDATE()
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE

(0 row(s) affected)

I ran sp_spaceused against all of the tables in msdb and the largest two are these, which take up 4MB and 1.4MB:

sp_spaceused sysssispackages
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
sysssispackages 14 4048 KB 3968 KB 24 KB 56 KB

sp_spaceused sysjobhistory
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
sysjobhistory 258 1432 KB 864 KB 168 KB 400 KB

sp_spaceused on msdb gets me this:

database_name database_size unallocated space
msdb 63616.25 MB 6.82 MB

reserved data index_size unused
65134776 KB 64540464 KB 584704 KB 9608 KB

I would prefer to avoid re-creating the msdb database, if possible.

Any thoughts?

Thanx,

-Mark McNary

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-01 : 12:40:58

Your subject says "cannot shrink", but I'm wondering what you mean by that. Do you get an error when you run DBCC SHRINKFILE?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mmcnary
Starting Member

16 Posts

Posted - 2010-06-01 : 12:46:34

Whether I run shrink database or shrink data file, it still stays the same.

I forgot to mention that I have done these steps also:

use msdb
go

exec sp_updatestats
go

dbcc updateusage('msdb') WITH COUNT_ROWS
go

I did not rebuild indexes.

Thanx,

-Mark McNary

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-01 : 12:55:11

Try running DBCC SHRINKFILE with just removing 1MB at a time to see if you are making any progress. You should be able to make progress with small increments. It can be very hard to make progress if you try to do the shrink in one go. If the 1MB (or similarly sized) shrinks are working, I'll send you a link with how to do it repeatedly until it reaches the target size.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mmcnary
Starting Member

16 Posts

Posted - 2010-06-01 : 16:41:28

I found this and it seems to be working:

http://www.simple-talk.com/community/blogs/rodney/default.aspx

Someone turned on service broker, and it has been logging error messages for over a year. I am running END CONVERSATION '{@conversation_handle}' WITH CLEANUP ; and it is getting rid of the data in the sys.sysxmitqueue table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources