Huge MSDB, cannot shrink

Question

Huge MSDB, cannot shrink

mark.mcnary

SSC Veteran

Points: 275
More actions
June 1, 2010 at 10:00 am

#235872

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-2 datetime
SET @getdate-2 = GETDATE()
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @getdate-2
(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
namerowsreserveddataindex_sizeunused
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sysssispackages144048 KB3968 KB24 KB56 KB
sp_spaceused sysjobhistory
namerowsreserveddataindex_sizeunused
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sysjobhistory2581432 KB864 KB168 KB400 KB
sp_spaceused on msdb gets me this:
database_namedatabase_sizeunallocated space
msdb 63616.25 MB6.82 MB
reserved data index_size unused
65134776 KB64540464 KB584704 KB9608 KB
I would prefer to avoid re-creating the msdb database, if possible.
Any thoughts?
Thanx,
-Mark McNary

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

Try an updatestats and rebuild your indexes. This may free up some of that space so you can shrink it back down.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mark.mcnary SSC Veteran Points: 275 More actions · Answer 2

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.

Steve Jones - SSC Editor SSC Guru Points: 738773 More actions · Answer 3

Are you doing a shrink file or shrink db? I've had the former work better.

mark.mcnary SSC Veteran Points: 275 More actions · Answer 4

mark.mcnary

SSC Veteran

Points: 275

June 1, 2010 at 10:39 am

#1174910

I've tried them both.

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 5

Run these commands and you will know more information. And if you can share the info here.

Select name, log_reuse_wait_desc from sys.databases

GO

DBCC LOGINFO(YourDBNAME) WITH TABLERESULTS

GO

SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]

FROM sys.database_files;

GO

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 6

Right-click on the msdb database in Object Explorer. Select Reports, Standard Reports and the report Disk Usage by Table.

See if you can identify the table taking up the space from this report. You might find that the table is a service broker queue which is not clearing.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mark.mcnary SSC Veteran Points: 275 More actions · Answer 7

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.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 8

Thanks for posting what you have found.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events