MSDB database is up to 25gb!! - 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 2005 Forums

SQL Server Administration (2005)

MSDB database is up to 25gb!!

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-07-19 : 01:14:10

My server is close to running out of space and I was trying to find out why. Turns out, the largest culprit is that my sql server 2005's MSDB database is up to 25gb.

I'm not a dba, so I wasn't really sure how to resude the size. I went onto the internet, found a script to run, and executed it. It ran successfully however the odd thing now, is my msdb tables are all trunacted however, my total file size of the msdb database is still 25gb!

How can I reduce this size? Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-19 : 02:01:11

You have to now shrink it down. Deleting data does not cause the files to shrink, it just frees up space inside the file.

You can shrink it via the GUI or via DBCC SHRINKFILE.

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

Subscribe to my blog

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-07-21 : 09:32:12

Hi Tara. Thanks for the reply.

Unfortunately, shrinking my database didn't affect it all too much. I tried shrinking via both the gui & the dbcc shrinkfile, however it merely went from 25gb down to 24.5gb.

At this point, I'm dangerously low on space, so I was hoping to essentially knock this database down to zero, if possible. Is there any way to do that? If I were to simply go in and delete the entire msdb database, I'm guessing my sql server would probably experience problems right?

Thanks again.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-21 : 09:42:03

quote:
If I were to simply go in and delete the entire msdb database, I'm guessing my sql server would probably experience problems right

That's the understatement of the year. Don't do it.

First off, find out how large the data and log files are individually. You can look at this in the Properties tab of the database when you right-click, or use the Disk Usage report under the Reports option. You should also run the Disk Usage by Top Tables report and see which tables are largest.

If you've "truncated" all the msdb tables (also a no-no but seems to be too late) then you shouldn't have any trouble shrinking it. My guess is there's a mail or backup history table that still has a lot of data in it, or your msdb database is set to full recovery and the log needs to be backed up.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-21 : 09:59:27

Are you saving lots of ssis/dts packages there with their versions? Move them out to files.
How much job history are you holding?
How big is the log?
Has anyone created user tables?
Log shipping?

First step is probably to find out which tables are big.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-21 : 12:53:45

You'll need to tell us more about this:

quote:

I went onto the internet, found a script to run, and executed it.

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

Subscribe to my blog

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-07-26 : 16:35:40

Nigel, in response to your questions, please see below:

Q: Are you saving lots of ssis/dts packages there with their versions? Move them out to files.
A: I don't have any ssis/dts packages set on this database
Q:How much job history are you holding?
A: I'm not really sure. How would I find that?
Q: How big is the log?
A: I'm not really sure. How would I find that (I have very little dba experience)?
Q:Has anyone created user tables?
A: No
Q: Log shipping?
A: No

Q: First step is probably to find out which tables are big.
A:
The following are my msdb table sizes. The rows are very small so I'm baffled as to where these 25gb are hiding:
MSdbms_datatype_mapping 325
MSdbms_map 248
MSdbms_datatype 141
syssessions 45
syscategories 21
syssubsystems 11
MSdbms 7
sysmail_configuration 7
sysdtscategories 3
sysmail_log 2
sysdtspackagefolders90 2
sysmail_servertype 1
sysdbmaintplans 1
sysdownloadlist 0
sysdbmaintplan_databases 0
sysdbmaintplan_history 0
sysdbmaintplan_jobs 0
restorefile 0
restorefilegroup 0
restorehistory 0
sqlagent_info 0
suspect_pages 0
sysalerts 0
syscachedcredentials 0
backupfile 0
backupfilegroup 0
backupmediafamily 0
backupmediaset 0
backupset 0
log_shipping_monitor_alert 0
log_shipping_monitor_error_detail 0
log_shipping_monitor_history_detail 0
log_shipping_monitor_primary 0
log_shipping_monitor_secondary 0
log_shipping_primaries 0
log_shipping_primary_databases 0
log_shipping_primary_secondaries 0
log_shipping_secondaries 0
log_shipping_secondary 0
log_shipping_secondary_databases 0
logmarkhistory 0
sysmaintplan_log 0
sysmaintplan_logdetail 0
sysmaintplan_subplans 0
sysnotifications 0
sysoperators 0
sysoriginatingservers 0
sysproxies 0
sysproxylogin 0
sysproxysubsystem 0
sysschedules 0
sysmail_mailitems 0
sysmail_principalprofile 0
sysmail_profile 0
sysmail_profileaccount 0
sysmail_query_transfer 0
sysmail_send_retries 0
sysmail_server 0
sysdtspackagelog 0
sysdtspackages 0
sysdtspackages90 0
sysdtssteplog 0
sysdtstasklog 0
sysjobactivity 0
sysjobhistory 0
sysjobs 0
sysjobschedules 0
sysjobservers 0
sysjobsteps 0
sysjobstepslogs 0
sysmail_account 0
sysmail_attachments 0
sysmail_attachments_transfer 0
sysdtslog90 0
systargetservergroupmembers 0
systargetservergroups 0
systargetservers 0
systaskids 0

Thanks again.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-26 : 16:45:08

Run the following query and post the results:

select size/128 MB, name, physical_name from sys.master_files where database_id=db_id('msdb')

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-07-27 : 00:07:05

The results are as follows:
MB | Name | Physical_Name
-------------------------------------------------------------
25255 | MSDBData | C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf

0 | MSDBLog | C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf
-------------------------------------------------------------

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-27 : 07:20:12

This should fix the problem:

USE msdb
DBCC SHRINKFILE(1,256)

After you shrink the file, run the following:

EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'

That will defragment the tables in msdb, which will become fragmented from the shrink operation.

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-07-27 : 09:25:23

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-27 : 09:31:49

You may have to run it several times. Also try alternating with this:

DBCC SHRINKFILE(1, TRUNCATEONLY)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2011-07-28 : 06:21:32

You can try running this script, which will tell you how much data is being used by each table.


USE msdb

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
DECLARE @UserName VARCHAR(100)    --For storing values in the cursor
DECLARE @FullName VARCHAR(201)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 

select o.[name] AS TableName, u.[name] as UserName
from dbo.sysobjects o
INNER JOIN dbo.sysusers u
	ON o.uid = u.uid
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
ORDER BY o.Name
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    userName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName, @UserName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
	SET @FullName = '[' + @UserName + '].[' + @TableName + ']'

	PRINT @UserName + ' - ' + @TableName

    INSERT  #TempTable (tableName, numberofRows, reservedSize, dataSize, indexSize, unusedSize)
        EXEC sp_spaceused @FullName 

	UPDATE #TempTable 
	SET userName = @UserName 
	WHERE userName IS NULL

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName, @UserName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT * 
FROM #TempTable
ORDER BY CAST(numberofRows AS BIGINT) DESC

--Final cleanup!
DROP TABLE #TempTable

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-07-28 : 16:30:14

Robvolk,
Unfortunately, that didn't work either. I tried running the dbccshrinkfile (both versions you gave me), alternating between the two versions about a dozen times but still no luck. My database is still listed as 25gb

Any other ideas? Thanks again, I really appreciate your help!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 17:01:18

Honestly, I think you should contact Microsoft support, because something is not right if it won't shrink with so little data in it.

Have you run DBCC CHECKDB on msdb? Maybe there's some corruption or other error that's behind it.

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-08-03 : 00:38:14

Robvolk, thanks for the suggestion of running dbcc checkdb, but unfortunately, after about 10 minutes of running this, I get an error stating my hard drive is out of space, and terminates the query.

As another option, if I were to simply copy and paste the 2 databases I care about (only about 100mb of data in total) to create backups of them, then uninstall sql server, reinstall it, and finally paste the 2 databases back in again, would this be a viable alternative?

Thanks again.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-03 : 06:19:04

No, that's not a viable option. Never copy database files, use the SQL Server BACKUP command to make backups, and RESTORE to restore them to a new server. You can also try detaching and attaching the databases, but make backups first.

I still recommend contacting Microsoft Support.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2011-08-03 : 09:28:27

quote:
Originally posted by Goalie35

Robvolk, thanks for the suggestion of running dbcc checkdb, but unfortunately, after about 10 minutes of running this, I get an error stating my hard drive is out of space, and terminates the query.

As another option, if I were to simply copy and paste the 2 databases I care about (only about 100mb of data in total) to create backups of them, then uninstall sql server, reinstall it, and finally paste the 2 databases back in again, would this be a viable alternative?

Thanks again.

Did you run the script I posted earlier?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-08-04 : 08:51:55

Hi,
Take your msdb database backup exit sql server management studio stop the instant of sql server and restart the instance of sql server in masteer-only recovery mode by using command prompt
and change your msdb physical location by using alter database command and restor your msdb backup to that new location.
after restor exit sql server management studio stop the instant of sql server and restart the instance of sql server normaly

Manju

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-04 : 09:34:22

quote:
Originally posted by manju3606

Hi,
Take your msdb database backup exit sql server management studio stop the instant of sql server and restart the instance of sql server in masteer-only recovery mode by using command prompt
and change your msdb physical location by using alter database command and restor your msdb backup to that new location.
after restor exit sql server management studio stop the instant of sql server and restart the instance of sql server normaly

Ummmm, no. Backing up and restoring will not change the file sizes, and there's no need to stop/restart the SQL Server to do any of this. You can RESTORE DATABASE...WITH MOVE to move it to a new location, which doesn't sound like a viable option unless the OP can install additional drives...which also solves the problem.

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-08-04 : 16:39:13

Hi,
Goalie35 please tell me,what is your msdb full backup size?

Manju

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources