October 13, 2009 at 1:06 am
Hi
Can somebody tell me how big can msdb grow? I mean, is that normal if size is more then 1GB?
On all my other servers size is much smaller (max 400MB). Only on one server size of msdb is more then 1GB, I wonder if everything is fine with this server...
October 13, 2009 at 1:12 am
k.przewozniak (10/13/2009)
HiCan somebody tell me how big can msdb grow? I mean, is that normal if size is more then 1GB?
On all my other servers size is much smaller (max 400MB). Only on one server size of msdb is more then 1GB, I wonder if everything is fine with this server...
MSDB is just a database, system database but still a database. it does sound large but msdb can grow.
The things I would check are
Are there any user owned tables in the database
How big is the mdf and ldf files
find out the biggest tables in the database
Once you have that information, post it and we can help you further
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 13, 2009 at 1:21 am
To make sure every thing is fine run
dbcc checkdb (msdb)
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
October 13, 2009 at 1:24 am
free_mascot (10/13/2009)
To make sure every thing is fine rundbcc checkdb (msdb)
How does that explain the size of it?
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 13, 2009 at 1:55 am
looks like both topics that I posted today are connected...
the biggest table in msdb database is sysmaintplan_logdetail and it has 1023312 KB
mdf is 1117568 KB
ldf is 136064 KB
October 13, 2009 at 1:59 am
Silverfox
This is with reference to doubt of k.przewozniak that...
"I wonder if everything is fine with this server... "
Can you please tell me how you can find out if everything is fine on your database????
Here You have to show your intelligence and run DBCC command to find out any inconsistancy.
Hope it's clear now???
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
October 13, 2009 at 2:05 am
This is from BOL (Books Online)
You can either modify the maintenance plan, as your biggest table is the maintenance plan history
or you can try running teh stored procedures manually.
Maintenance Plan Wizard (Define History Cleanup Task Page)
Updated: 15 September 2007
Use the Define History Cleanup Task page to discard old task history. This task deletes the records of when certain types of jobs occurred.
This statement uses the sp_purge_jobhistory, sp_maintplan_delete_log, and sp_delete_backuphistory statements to remove history information from the msdb tables.
Important:
The addition of the Hour interval in cleanup tasks has implications when you are running multiple version of SQL Server 2005. If you create a task that has an interval of hours, and then try to run that task on either the release version of SQL Server 2005 or SQL Server 2005 Service Pack 1, you will get a different interval. If you edit the task using the earlier versions of the tools, an error message will appear.
Options
Backup and restore history
Retaining records of when recent backups were created can help SQL Server create a recovery plan when you want to restore a database. The retention period should be at least the frequency of full database backups.
SQL Server Agent Job history
This history can help you troubleshoot failed jobs, or determine why database actions occurred.
Maintenance Plan history
This history can help you troubleshoot failed maintenance plan jobs, or determine why database actions occurred.
Remove historical data older than
Specify age of items that you want to delete.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 13, 2009 at 2:07 am
Ok, I accept that, thanks for the clarification
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 13, 2009 at 2:16 am
Silverfox
Thank you for understanding.
----------------------------
Give Respect & Earn Respect
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
October 14, 2009 at 7:53 am
Job histories and backup/restore histories are the things that take the most room over time in msdb. They can both be pruned using system stored procedures or maintenance plans.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply