Microsoft KB Archive/926731

= The SMS database may unexpectedly increase in size after you install SMS 2003 Service Pack 2 =

Article ID: 926731

Article Last Modified on 11/3/2006

-

APPLIES TO


 * Microsoft Systems Management Server 2003 Service Pack 2

-



SYMPTOMS
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.



CAUSE
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.



RESOLUTION
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  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.</li> Right-click the SMS database, and then click Properties.</li> In the Database Properties -  dialog box, click Options.</li> In the Recovery model list, click Simple, and then click OK.</li></ol> </li> 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

</li> Determine the approximate file size of the following tables to help you estimate how this problem affects the SMS database: <ul> DWTbl_System_Disc_Del</li> DWTbl_User_Disc_Del</li> DWTbl_User_Group_DISC_Del</li> DWTbl_SoftwareInventory_Del</li> DWTbl_CollectionMembers_Del</li> DWTbl_PatchStatusSummary_Del</li></ul>

For more information about how to determine the file size of a table, see SQL Server Books Online.</li> 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  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.</li> 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.</li> <li>Expand the site, expand Site Hierarchy, expand  , expand Site Settings, expand Site Maintenance, and then click Tasks.</li> <li>In the right pane, double-click Delete Aged System Center Reporting Data.</li> <li>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.</li> <li>Click to select the Enable this task check box, and then click OK.</li></ol> </li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Additional query words: SMS2003, SCRM

Keywords: kbpending kbbug kbtshoot kbprb KB926731

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.