[ConfigMgr 2007] Fix: The ConfigMgr 2007 database grows excessively large and DPs do not honor the "Number of retries" and "Delay before retrying (minutes)" retry settings

This is one KB you SERIOUSLY need to consider as noted on the Configuration Manager Support Team Blog:

"You may notice that an unusually large number of System Center Configuration Manager 2007 status messages are generated causing significant growth in database size.  Further investigation also shows a large number of these entries repeating over and over again in the distmgr.log:

Compressed files for package xxx hasn't arrived from site xxx

Note: These messages can occur on a normally functioning site.  It is only a cause for concern when an excessively large number are repeatedly seen.

This can be caused by the issue documented in the following Knowledge Base articles:

SP1 version:  KB978875 - The Distribution Manager does not honor the "Number of retries" and "Delay before retrying (minutes)" retry settings on SCCM 2007 SP1 site servers

SP2 and later version:  KB978021 - The Distribution Manager that is in System Center Configuration Manager 2007 SP2 does not honor the "Number of retries" and "Delay before retrying (minutes)" retry settings

To resolve this issue apply the appropriate hotfix referenced above.

Troubleshooting and verification:

You can use this script to determine that the status specific tables are largest in the ConfigMgr database:

http://www.sqlteam.com/article/finding-the-biggest-tables-in-a-database

Then you can use these queries to determine the components generating the most status messages.  In this specific scenario you'll find that it is Distribution Manager.

select Component, count(*) from vStatusMessages group by Component order by count(*) desc 
select MessageID, count(*) from vStatusMessages group by MessageID order by count(*) desc

ref: http://blogs.technet.com/b/configurationmgr/archive/2009/01/27/troubleshooting-database-growth-issues-in-configuration-manager-2007.aspx

You should find that the SMS_DISTRIBUTION_MANAGER thread has been generating status message ID's 2342, 2300 and 2301 or similar every 5 seconds, repeatedly.

So what's the takeaway from all this? 

If you have System Center Configuration Manager 2007 SP1 or later and are doing software distribution then you need this hotfix!

Clint Koenig | Senior Support Escalation Engineer

The App-V Team blog: http://blogs.technet.com/appv/ 
The WSUS Support Team blog: 
http://blogs.technet.com/sus/ 
The SCMDM Support Team blog: 
http://blogs.technet.com/mdm/ 
The ConfigMgr Support Team blog: 
http://blogs.technet.com/configurationmgr/ 
The OpsMgr Support Team blog: 
http://blogs.technet.com/operationsmgr/ 
The SCVMM Team blog: 
http://blogs.technet.com/scvmm/ 
The MED-V Team blog: 
http://blogs.technet.com/medv/ 
The DPM Team blog: 
http://blogs.technet.com/dpm/ 
The OOB Support Team blog: 
http://blogs.technet.com/oob/ 

The Opalis Team blog: http://blogs.technet.com/opalis"

http://blogs.technet.com/b/configurationmgr/archive/2010/09/22/fix-the-configmgr-2007-database-grows-excessively-large-and-dps-do-not-honor-the-quot-number-of-retries-quot-and-quot-delay-before-retrying-minutes-quot-retry-settings.aspx

 

 

Read the complete post at http://wmug.co.uk/blogs/cliffs_blog/archive/2010/09/22/configmgr-2007-fix-the-configmgr-2007-database-grows-excessively-large-and-dps-do-not-honor-the-quot-number-of-retries-quot-and-quot-delay-before-retrying-minutes-quot-retry-settings.aspx

Published Wednesday, September 22, 2010 3:03 PM by Cliff Hobbs - FAQShop.com and Microsoft MVP ConfigMgr/ SMS