Your company's ad could live here and reach over 50,000 people a month!

Share This Post

ConfigMgr 2007: Database growing rapidly due to size of TS_TaskSequence table

Symptoms

Database growing rapidly due to size of TS_TaskSequence table.

Cause

We weren’t able to determine the cause of the initial growth. Some existing cases suggest task sequence corruption but there wasn’t really any proof of this.

Resolution

Perform the following query on the database to recover the lost space (replace SMS_CEN with the appropriate database name):

dbcc cleantable (SMS_CEN, TS_TaskSequence)

dbcc shrinkdatabase (SMS_CEN)

 

First off we used a modified SQL query that does a better job of showing the actual space used by each table. Run on site Database SMS_XXX.

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

— DB size.

EXEC sp_spaceused

— Table row counts and sizes.

CREATE TABLE #t

(

[name] NVARCHAR(128),

[rows] CHAR(11),

reserved VARCHAR(18),

data VARCHAR(18),

index_size VARCHAR(18),

unused VARCHAR(18)

)

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

SELECT *, CONVERT (Int, REPLACE (reserved, ‘Kb’, ”)) AS Total

FROM #t

order by

Total

DESC

— # of rows.

SELECT SUM(CAST([rows] AS int)) AS [rows]

FROM #t

DROP TABLE #t

 

We found on both servers, VERY Large amounts of data in the top four tables. For the most part these were all CI related tables.

The recommendation is to follow these steps below on each database and each of the top four tables. and then check the SMS Database sizes again. It appears we have a known issue with the specific tables referenced.

So from here on the top four tables run this on each DB where ABC will equal your site code:

dbcc cleantable(sms_ABC,ci_sdmpackages)

After all 4 tables have been cleaned do this once on each site where ABC will equal your site code:

Execute a dbcc shrinkdatabase(sms_ABC,10)

This will take some time to complete. After it is all done run the DBCC UPDATEUSAGE(0) query again to see what has changed.

More Information

This problem can be made worse by deleting CI Replication data and forcing replication from parent to child via SHA file creation on the parent for the child.

Share This Post

Leave a Reply