Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 926731 - Last Review: January 15, 2008 - Revision: 2.0
The SMS database may unexpectedly increase in size after you install SMS 2003 Service Pack 2
After you install Microsoft Systems Management Server (SMS) 2003 Service Pack 2 (SP2), you may experience the following symptoms:
- The SMS database may unexpectedly and quickly increase in size.
- If you enable the Delete Aged System Center Reporting Data task, the SMS database transaction log files may unexpectedly increase in size.
This problem occurs because of new triggers and tables that are included with SMS 2003 SP2. These new triggers and tables are included to provide support for Microsoft System Center Reporting Manager 2006. These tables are used to store information about items that are deleted from the SMS database. The associated grooming task,
Delete Aged System Center Reporting Data, has a default data retention threshold of 90 days. By default, this task is disabled. Also, this task is not enabled when you install System Center Reporting Manager 2006.
Therefore, if you enable the
Delete Aged System Center Reporting Data task after the data of lots of deleted items has been stored in the SMS database, the SMS transaction log files may quickly increase in size.
To resolve this problem, follow these steps:
- Configure the SMS database to use the simple recovery model. To do this, follow these steps.
For Microsoft SQL Server 2000- Start the SQL Server Enterprise Manager tool, and then locate the SMS database.
- Right-click the SMS database, and then click Properties.
-
In the DatabaseName Properties dialog box, click the Options tab.
- In the Model list, click Simple, and then click OK.
For Microsoft SQL Server 2005- Start the SQL Server Management Studio tool, and then locate the SMS database.
- Right-click the SMS database, and then click Properties.
-
In the Database Properties - DatabaseName dialog box, click Options.
- In the Recovery model list, click Simple, and then click OK.
- Enable the Truncate Log on Checkpoint feature in SQL Server if this feature is not already enabled. To do this, run the following SQL statement in SQL Query Analyzer or in SQL Server Management Studio, as appropriate for the version of SQL Server that you are running:
exec sp_dboption 'dbname', 'trunc. log on chkpt.', 'True'
- Determine the approximate size of the following tables to help you estimate how this problem affects the SMS database:
- DWTbl_System_Disc_Del
- DWTbl_User_Disc_Del
- DWTbl_User_Group_DISC_Del
- DWTbl_SoftwareInventory_Del
- DWTbl_CollectionMembers_Del
- DWTbl_PatchStatusSummary_Del
For more information about how to determine the size of a table, see SQL Server Books Online. - If the tables are so large that the removal of the data by using a single transaction could use all the available hard disk space, you may want to truncate the tables. To truncate a table, use the Truncate Table tablename statement. For example, to truncate the DWTbl_System_Disc_Del table, run the following SQL statement:
Truncate Table DWTbl_System_Disc_Del
Note If the data can be removed by using a single transaction, you can skip this step. - Enable the Delete Aged System Center Reporting Data task in the SMS Administrator Console Microsoft Management Console (MMC) snap-in. To do this, follow these steps:
- Start the SMS Administrator Console MMC snap-in.
- Expand the site, expand Site Hierarchy, expand SiteName, expand Site Settings, expand Site Maintenance, and then click Tasks.
- In the right pane, double-click Delete Aged System Center Reporting Data.
- In the Delete Aged System Center Reporting Data Task Properties dialog box, configure the data retention options as appropriate for your environment. We recommend that you base these settings on the amount of data that was previously retained and on the time that was required to accumulate this data. Generally, a data retention period of 20 days is sufficient for most environments. Configure the task to run approximately two or three times each week, and then configure a time that does not conflict with other tasks or with backup operations.
- Click to select the Enable this task check box, and then click OK.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
APPLIES TO
- Microsoft Systems Management Server 2003 Service Pack 2
| kbpending kbbug kbtshoot kbprb KB926731 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please