So, if you saw my article on tracking table size growth/shrinkage (or if you've been doing that yourself) you may have noticed that one or more of the following SMS tables are getting quite big and you're not sure why or what they're used for:
DWTbl_System_Disc_Del
DWTbl_User_Disc_Del
DWTbl_User_Group_DISC_Del
DWTbl_SoftwareInventory_Del
DWTbl_CollectionMembers_Del
DWTbl_PatchStatusSummary_Del
I noticed the same thing and looked it up. Turns out that those tables were introduced back with SMS 2003 Service Pack 2 and they're included to support Microsoft Systems Center Reporting Manager 2006 and show what items are deleted from the SMS database. The Microsoft article explaining that is here.
In our case, the DWTbl_SoftwareInventory_Del table was over 36GB by itself...yeah, that's Gigabytes. And has gained about 1.5GB the last month alone. And that, despite us having the "Delete Aged System Center Reporting Data" maintenance task turned on weekly, and having all of the other recommendations in that MS article. Well, we don't use system center reporting manager on our SMS infrastructure so to me there's no good reason for ANY data to exist in those folders for us. So the easiest solution to that problem is to just truncate those tables. The TRUNCATE command, for those of you that don't know, is a nice, fast, unlogged way to empty a table. It's like saying DELETE FROM TABLE <blah>, except those normal row deletions are logged and thus incur a disk I/O and completion time hit. This can be important because if the normal deleting of those records by the maintenance tasks is performed on so many rows that the logging fills up the disk, the task will fail and the data won't delete. Nor will it delete the next time, or the next time. A TRUNCATE may be the only good way to remove the rows.
So, if you find yourself using Microsoft Systems Management Server 2003 (SMS 2003) with Service Pack 2 or higher, then you may just want to truncate the tables in listing #1 above.
You want a SQL script to do just that? Try this.
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
