SCCM 2007 R2 - Policy table large and growing fast.
Hello, I have an SCCM 2007 R2 server and it serves roughly 500 clients machines. The policy table (dbo.policy) is 28 gigs and growing about roughly a gig a week. The next largest table size is 3.5 gigs, so in comparison, the policy table is very large. I have been trying to find out what is causing this table to grow so quickly and haven't been able to find the culprit. Does anyone have any ideas of what I should do to see what is causing that table to grow so rapidly and fix the problem? Thank You.
December 22nd, 2010 3:35pm

Does this help? http://blogs.technet.com/b/configurationmgr/archive/2009/01/27/troubleshooting-database-growth-issues-in-configuration-manager-2007.aspx John Marcum | http://myitforum.com/cs2/blogs/jmarcum/|
Free Windows Admin Tool Kit Click here and download it now
December 22nd, 2010 4:01pm

Hey John, I saw that page when I was searching for a potential solution. The DBA and I looked at the size of our tables to see where the problem child was (the Policy table). For the columns in the Policy table, we could see the PolicyID, Version, DeviceVersion, Body, DeviceBody, PolicyFlags and DeviceBodySignature columns. The 97504 PolicyIDs in the table are all unique. The Versions and the DeviceVersions are not. 97466/97504 of the DeviceVersions are NULL. For the Versions column, 50149/97504 are 2.0 and 43351/97504 are 1.0 (the rest are other versions). That is sort of suspicious, but I don't know what that means. Is that bad? I can't sort the Body or DeviceBody because they are an image type and cannot be sorted. I also cannot see what is in those columns. Those two columns are where most of the data in the table lives. I just don't know why there is so much of it there and how to stop it. That page you linked sort of helps, but I still am lost. It seems like the Policy table isn't as obvious to see why it is growing vs the StatusMessages table, since I can't see my data when viewing it in Management Studio. Is there anything else I should look into? SCCMNooby
December 22nd, 2010 7:50pm

The best option would be to open a case with Microsoft. Regards, Madan
Free Windows Admin Tool Kit Click here and download it now
December 31st, 2010 4:07am

Hello - I agree with Madan. How about the performance of SCCM? Is it normal? Did you verify the log files and Inboxes? And did you find any abnormality? Anoop C Nair
December 31st, 2010 8:15am

Sorry I didn't reply. I thought this thread was e-mailing me updates... Anyways, I called Microsoft and opened a case. An00p - Performance was extremely slow. It normally wasn't this slow and SQL would use its maximum amount of memory very quickly after a reboot. After opening a case with Microsoft, I spoke with an engineer that was awesome. After running several queries, he determined that 95,000 / 98,000 rows of the Policy Table were related to Software Updates. Out of those rows, a significant portion of the rows were 22 megabytes per row. The row information contains instructions to SCCM on when and how to apply the update. They should be way less than a megabyte typically. He says that the only updates that he could possibly think of that would be remotely that size are Forefront updates, but we don't use Forefront, and did not configure it to download Forefront updates. The engineer wrote a script to delete those rows (95,000 rows) and re-index the database. We re-synced with WSUS and our database size is now around 4 gigabytes. It ended up being a bug with SCCM and SQL, as he was able to duplicate the issue in his lab. He told us to apply Cumulative Update 12 to our SQL 2005 SP3 server. He wasn't too sure if that would fix it or not. SP4 is not supported by SCCM. Everything regarding the size of the database is now all good. None of the rows are near 22 megabytes in size.
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2011 1:30pm

Could you upload please this script which deletes this rows? And what kind of rows should i delete? Thanks
July 26th, 2011 4:00am

I wouldn't delete any rows directly from the database without calling CSS. Take that piece of advice from someone who got paid well to fix a mess someone made when they deleted a bunch of rows from the SCCM database after I told them not to. ;-) John Marcum | http://myitforum.com/cs2/blogs/jmarcum/|
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2011 7:02pm

We are having this exact same issue and so far PSS has not found a fix for it. Do you still have the case number so we can provide to PSS as a reference?
September 22nd, 2011 12:07pm

I was experiencing this issue with the same table and had to open a ticket with Microsoft due to the lack of information from forums. Basically, the engineer said it was a bug with FEP 2010. In our case, FEP was generating ALOT of notifications for "unknown machine" and/or "unkown SMS application" in the 'dbo.Policy' table creating orphaned CI's. The engineer I worked with ran a script to delete all the rows with the orphaned CI's which took my 27GB Policy table down to about 1.1GB. Below is the SQL query he ran against the Policy table. While this did fix my issue, I am not advising running this unless you are comfortable with deleting rows from the Policy table.... or have a good backup ; ) DELETE FROM Policy WHERE PolicyID NOT IN (SELECT PolicyID FROM PolicyAssignment WHERE PADBID IN (SELECT PADBID FROM ResPolicyMap)) AND PolicyID NOT IN (SELECT ModelName FROM CI_ConfigurationItems) AND PolicyID LIKE '%SUM_%' Hope this helps and/or sheds some light on this "bug"! (Btw, the engineer said this will be resolved in FEP 2012)
Free Windows Admin Tool Kit Click here and download it now
November 7th, 2011 4:00pm

@mike0492 - Thanks for posting the snippit. It's saving me the time and cost of Microsoft support. I'm experiencing the same issue in my production and test lab environments. It took me a while to notice it because the growth is slow, so I just assumed it was natural SCCM growth for a while. But when I realized that 26GB DB for probably a little large for a five computer test lab, I did some digging and googling. So far, so good in the test lab. If I don't have any issues in a week or two, this will become a daily scheduled task in production. A note I will point out to others who might try this: your SCCM DB transaction log and tempdb will grow EXTREMELY LARGE so be prepared. My test lab didn't have a big drive, so the first time I tried it, I ran out of disk space. My SCCM DB was 26GB in size and the transaction log grew to 50GB and the tempdb to 40GB before it finished. I imagine it will scale linearly with the DB size, so be careful with that! (Shrinking both of those afterwards is easy.) Considering my production SCCM DB is currently about 65GB, I'm going to guess I'll need a couple hundred GBs free for that.
March 11th, 2012 10:13pm

@mike0492 - Thanks for posting the snippit. It's saving me the time and cost of Microsoft support. I'm experiencing the same issue in my production and test lab environments. It took me a while to notice it because the growth is slow, so I just assumed it was natural SCCM growth for a while. But when I realized that 26GB DB for probably a little large for a five computer test lab, I did some digging and googling. So far, so good in the test lab. If I don't have any issues in a week or two, this will become a daily scheduled task in production. A note I will point out to others who might try this: your SCCM DB transaction log and tempdb will grow EXTREMELY LARGE so be prepared. My test lab didn't have a big drive, so the first time I tried it, I ran out of disk space. My SCCM DB was 26GB in size and the transaction log grew to 50GB and the tempdb to 40GB before it finished. I imagine it will scale linearly with the DB size, so be careful with that! (Shrinking both of those afterwards is easy.) Considering my production SCCM DB is currently about 65GB, I'm going to guess I'll need a couple hundred GBs free for that. How did it go in the production env. ? Im thinking about doing the same thing.
Free Windows Admin Tool Kit Click here and download it now
August 7th, 2012 8:37am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics