Answered by:
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 KBI 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
Answers
-
try this code
before it's recommended to backup MSDB database.
Code SnippetALTER
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; -
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 MBunallocated_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)
All replies
-
You can use this procedure: sp_maintplan_delete_log
Check out: http://www.elsasoft.org/SUMMER.msdb/sp_dbospmaintplandeletelog.htm
-
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 -
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
-
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
-
try this code
before it's recommended to backup MSDB database.
Code SnippetALTER
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; -
-
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
-
-
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 MBunallocated_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) -
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
-
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 KBThanks again for helping me out
-Binoy
-
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
-
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.
-
-
-