Change MSDB size

Question

Change MSDB size

SQLSlammer

Ten Centuries

Points: 1045
More actions
August 6, 2009 at 8:02 am

#156070

Hi there
How can I change the size of the MSDB? the previous DBA set an initial size of 10gb, but the database is reporting that its only using 200mb of this 10gb (sp_spaceused).
Due to the initial size, when I try to change it I cant because I would be changing to a size smaller than the inital size! shrinking also doesn't work
So I am stuck with 10gb of disk space wasted on the MSDB.
I have tried starting in single user mode, still wouldn't let me change it.
Any ideas?

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

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

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

Umm off the top of my head, not sure.

If anyone has the answer this would be useful. I have a 5gb msdb under sql2000. Not sure how it got that way but the thoughts are it filled with job histories (bad plans) and now the jobs are gone the data is orphaned.

Its my one bug bear of the dbs i inherited but cant sort.

As for you my friend, if you take a backup and then follow the MS guide to recreating msdb (but with a sensible initial size 3mb for eg), that should sort you.

Adam Zacks-------------------------------------------Be Nice, Or Leave

JamieX Hall of Fame Points: 3763 More actions · Answer 2

Your data file, correct? not your log file?

what happens when your run

DBCC SHRINKFILE (N'MSDBData' , 250)

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 3

Not the log, the data

Hmmm ok well that worked...

Strange I had tried that a few times and it wasn't shrinking it, I wonder if the service re-start I did this morning and manual msdb backup has helped it release the space (dev server). Admitedly after service re-start I didn't try a DBCC SHRINK

Oh well I am not going to dig any further, I got my 10gb back so I am happy.

Thanks for the help

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 4

However I was using a smaller figure than 250mb for the dbcc shrink would this have affected it?

JamieX Hall of Fame Points: 3763 More actions · Answer 5

well, if you were just shrinking without reorg, it probably wouldn't claim much back. And the number has to be a valid number bigger than the current data size used.

SanjayAttray SSChampion Points: 13157 More actions · Answer 6

Schadenfreude-Mei (8/6/2009)
If anyone has the answer this would be useful. I have a 5gb msdb under sql2000. Not sure how it got that way but the thoughts are it filled with job histories (bad plans) and now the jobs are gone the data is orphaned.

5GB data of Jobs history does not look good here. By default SQL Server 2000 will have only 1000 records in sysjobhistory table and it definitely won't take that much space. If you have hundreds of DTS packages than MSDB database size will increase, as every version of DTS package is stored in sysdtspackages table. If some one had modified ABC.dts package 9 times then this dts package will have 9 rows for each version and that takes a lot of space.

But not sysjobhistory table. Correct me if my assumptions are wrong here.

But never seen msdb database even grow more than a Gig.

SQL DBA.

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 7

SanjayAttray (8/6/2009)
Schadenfreude-Mei (8/6/2009)
If anyone has the answer this would be useful. I have a 5gb msdb under sql2000. Not sure how it got that way but the thoughts are it filled with job histories (bad plans) and now the jobs are gone the data is orphaned.
5GB data of Jobs history does not look good here. By default SQL Server 2000 will have only 1000 records in sysjobhistory table and it definitely won't take that much space. If you have hundreds of DTS packages than MSDB database size will increase, as every version of DTS package is stored in sysdtspackages table. If some one had modified ABC.dts package 9 times then this dts package will have 9 rows for each version and that takes a lot of space.
But not sysjobhistory table. Correct me if my assumptions are wrong here.
But never seen msdb database even grow more than a Gig.

But you could easily have the tables related to backups filling up, such as dbo.backupset, dbo.backupfile...those records don't get deleted. Especially if you had many databases on one server with very frequent log shipping.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 8

Thanks Slammer,

So what should I do/check to verify?

Adam Zacks-------------------------------------------Be Nice, Or Leave