MSDB is too big

Question

My MSDB is too big [about 14 GB]. When I closely looked all tables, here is what I found -

 

Top 3 biggest tables are -

sysmaintplan_logdetail - rows- 124923  - data - 13375456 KB
DTA_Tuninglog - 181658 rows - data - 163248 KB
sysmail_mailitems - records - 14227      data - 126208 KB

 

I am not a DBA, so if any one can explain in simple terms how I can cleanup this maintenance plan log detail without increasing the size of tempdb? How can I stop this database growing in the future?

 

I have only a gig of space left on that drive.

 

Thanks in advance

Answer 1

You can use this procedure: sp_maintplan_delete_log

 

Check out: http://www.elsasoft.org/SUMMER.msdb/sp_dbospmaintplandeletelog.htm

Answer 2

Pls check the below link, http://www.mssqltips.com/tip.asp?tip=1231and ensure that the job history log is'nt huge enough. Also refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201. Change the file growth pattern to MB instead of %. Also change the recovery model of msdb to simple if it was full.

- Deepak

Answer 3

Alex,

 

I tried to use sp_maintplan_delete_log, but after running for close to an hour, it's erroring out complaining about the lack of hard disk space [I've only 700 MB left on C drive]. I believe either MSDB log or TEMPDB is growing when I tried to do the delete. So I may have to adopt a different method. If I can wipe out entire MSDB without logging anything into any other databases, that is the method I'm looking for.

 

If you know of anything that I can do, please let me know.

 

Thanks

-Binoy

Answer 4

Deepak,

 

I read these posts before, but thought that they were mostly talking about SQL server 2000. My database is on SQL server 2005. Even then, I may try some of what they were talking about. I will try all avenues to get this resolved ASAP.

 

Thanks

-Binoy

Answer 5

try this code

 

before it's recommended to backup MSDB database.

 

Code Snippet

ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];

ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];

 

truncate table msdb.dbo.sysmaintplan_logdetail;

truncate table msdb.dbo.sysmaintplan_log;

 

ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])

REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);

 

ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])

REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

 

 

Answer 6

Alex,

 

Great! That was a fast response.

 

Since this is a production server, I will try it tonight and will let you know.

Answer 7

Alex,

 

I tried these, still msdbdata.mdf is showing 14.5 GB.

 

sp_spaceused reports-

msdb         14339.63 MB        13660.91 MB

 

For msdb, recovery mode is 'simple' and it grows by MB (not %) still sysmaintplan_logdetail is 45 MB after truncating it an hour back.

 

I looked at all tables in msdb. Largest one is DTA_tuninglog at 165 MB.

 

Am I missing anything?

 

Thanks

-Binoy

Answer 8

k... do one thing... run the following command and see which table is taking more space and post back the result

 

select 'Exec SP_SpaceUsed  '+Name from sysobjects where xtype='u'

 

Madhu

Answer 9

it's ok, you only clean up data so far.

 

how you haven't posted the result header, only the result detail.

I imagine this scenario:

 

database_name = msdb
database_size =  14339.63 MB

unallocated_space = 13660.91 MB

 

if it's right and now you need to release space that was allocated, you'll use the DBCC SHRINKFILE command.

 

Example:

This command shrink MSDBData file to 1Gb.

 

DBCC SHRINKFILE (MSDBData, 1000)

Answer 10

Madhu,

 

Here are the tables with atleast one record-

 

database_name	rows	reserved	data	index_size	unused
sysmaintplan_logdetail	2097	228000 KB	227936 KB	16 KB	48 KB
DTA_tuninglog	181658	165336 KB	163248 KB	56 KB	2032 KB
sysmail_mailitems	14311	128000 KB	126984 KB	216 KB	800 KB
backupfile	216767	90592 KB	89752 KB	608 KB	232 KB
backupset	107372	96768 KB	61696 KB	34768 KB	304 KB
backupmediafamily	107372	62848 KB	23808 KB	38744 KB	296 KB
backupfilegroup	107372	13280 KB	12848 KB	120 KB	312 KB
backupmediaset	107372	48960 KB	11232 KB	37376 KB	352 KB
sysjobactivity	47254	9232 KB	8536 KB	88 KB	608 KB
sysdtspackages90	23	8144 KB	8072 KB	24 KB	48 KB
sysmail_log	7066	2712 KB	1968 KB	32 KB	712 KB
sysjobs	1771	3176 KB	1416 KB	536 KB	1224 KB
sysjobsteps	1771	3168 KB	1240 KB	496 KB	1432 KB
sysjobhistory	999	1776 KB	832 KB	184 KB	760 KB
sysjobservers	1771	1744 KB	816 KB	128 KB	800 KB
DTA_reports_query	797	920 KB	800 KB	16 KB	104 KB
DTA_input	2	616 KB	544 KB	8 KB	64 KB
DTA_reports_index	5527	864 KB	528 KB	208 KB	128 KB
DTA_reports_column	10158	728 KB	464 KB	160 KB	104 KB
sysschedules	1784	968 KB	256 KB	16 KB	696 KB
DTA_reports_table	3176	328 KB	216 KB	72 KB	40 KB
sysjobschedules	1784	392 KB	128 KB	16 KB	248 KB
DTA_reports_querycolumn	2937	272 KB	88 KB	104 KB	80 KB
sysmail_send_retries	793	200 KB	88 KB	16 KB	96 KB
DTA_output	2	88 KB	80 KB	8 KB	0 KB
DTA_reports_queryindex	1251	104 KB	40 KB	64 KB	0 KB
DTA_reports_querytable	867	72 KB	24 KB	48 KB	0 KB
DTA_reports_querydatabase	833	72 KB	24 KB	48 KB	0 KB
restorefilegroup	9	32 KB	24 KB	8 KB	0 KB
MSdbms_datatype_mapping	325	40 KB	24 KB	16 KB	0 KB
MSdbms_map	248	40 KB	24 KB	16 KB	0 KB
restorehistory	9	48 KB	16 KB	32 KB	0 KB
DTA_reports_indexcolumn	212	32 KB	8 KB	24 KB	0 KB
MSdbms_datatype	141	16 KB	8 KB	8 KB	0 KB
DTA_reports_database	134	32 KB	8 KB	24 KB	0 KB
syssessions	44	32 KB	8 KB	24 KB	0 KB
syscategories	22	16 KB	8 KB	8 KB	0 KB
sysmaintplan_log	18	16 KB	8 KB	8 KB	0 KB
syssubsystems	11	32 KB	8 KB	24 KB	0 KB
sysmaintplan_subplans	8	16 KB	8 KB	8 KB	0 KB
MSdbms	7	16 KB	8 KB	8 KB	0 KB
sysmail_configuration	7	16 KB	8 KB	8 KB	0 KB
DTA_progress	6	16 KB	8 KB	8 KB	0 KB
sysdtscategories	3	32 KB	8 KB	24 KB	0 KB
sysmail_profile	2	32 KB	8 KB	24 KB	0 KB
syscachedcredentials	2	16 KB	8 KB	8 KB	0 KB
sysmail_profileaccount	2	16 KB	8 KB	8 KB	0 KB
sysdtspackagefolders90	2	48 KB	8 KB	40 KB	0 KB
sysoperators	1	32 KB	8 KB	24 KB	0 KB
sysmail_principalprofile	1	16 KB	8 KB	8 KB	0 KB
sysmail_account	1	32 KB	8 KB	24 KB	0 KB
sysdbmaintplans	1	16 KB	8 KB	8 KB	0 KB
sysmail_servertype	1	16 KB	8 KB	8 KB	0 KB
sysmail_server	1	16 KB	8 KB	8 KB	0 KB

 

Please let me know if you need any more info.

 

Thanks

-Binoy

Answer 11

Alex,

 

I believe I tried that last night, but will try it again tonight and will post the results tomorrow.

 

Sorry for not posting headers.

 

database name    database size   unallocated space   reserved           data             index size     unused
msdb                   14353.19 MB    13364.29 MB         995160 KB       866936 KB   115888 KB    12336 KB

 

Thanks again for helping me out

-Binoy

Answer 12

Alex,

 

This time it worked. msdb is now down to 1.3GB.

 

How can I keep msdb down to somewhere around 2GB? Is it good to select maximum database size as 2GB?

 

Right now recovery mode is 'simple' and database is growing in MB rather than %.

 

We have around 60 databases that are getting backed up every day and their transaction logs every hour.

 

Any ideas to keep msdb to it's best minimum will be appreciated.

 

Deepak, Madhu - thanks for sharing your valuable time to help me out.

 

Thanks

-Binoy

Answer 13

 

How can I keep msdb down to somewhere around 2GB? Is it good to select maximum database size as 2GB?

[Answer] it's possible, but first you need to analyze the average size and set the maximum database size. If you set 2Gb should be not enough.

 

Right now recovery mode is 'simple' and database is growing in MB rather than %.

[Answer] it's better growing up in MB rather than %. an example could be 200Mb.

 

We have around 60 databases that are getting backed up every day and their transaction logs every hour.

Any ideas to keep msdb to it's best minimum will be appreciated.

[Answer] Review MSDB backup plan and monitor the size of the tables mentioned before, you could schedule the clean up and shrink task per week, for example.

Answer 14

For cleaning up your MSDB tables prefixed with DTA_ simply opening up the Database Engine Tuning Advisor and delete the old sessions.

Answer 15

Thanks WebWillam ,

That helped .

Answer 16

Thanks  Alex

Your script worked for  me SQL Server 2008 r2

Ron