Database growing rapidly due to size of TS_TaskSequence table.
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.
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
— DB size.
— Table row counts and sizes.
CREATE TABLE #t
INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’
SELECT *, CONVERT (Int, REPLACE (reserved, ‘Kb’, ”)) AS Total
— # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
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:
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.
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.