none
SCCM 2007 R2 - Policy table large and growing fast. RRS feed

  • Question

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

    Wednesday, December 22, 2010 8:35 PM

Answers

  • The best option would be to open a case with Microsoft.

    Regards, Madan
    • Proposed as answer by Anoop C NairMVP Friday, December 31, 2010 1:23 PM
    • Marked as answer by Yog Li Monday, January 3, 2011 10:11 AM
    Friday, December 31, 2010 9:07 AM
  • 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. 

     

    Monday, January 24, 2011 6:30 PM

All replies

  • Wednesday, December 22, 2010 9:01 PM
    Moderator
  • 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
    Thursday, December 23, 2010 12:50 AM
  • The best option would be to open a case with Microsoft.

    Regards, Madan
    • Proposed as answer by Anoop C NairMVP Friday, December 31, 2010 1:23 PM
    • Marked as answer by Yog Li Monday, January 3, 2011 10:11 AM
    Friday, December 31, 2010 9:07 AM
  • 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
    Friday, December 31, 2010 1:15 PM
  • 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. 

     

    Monday, January 24, 2011 6:30 PM
  • Could you upload please this script which deletes this rows?

    And what kind of rows should i delete?

     

    Thanks

    Tuesday, July 26, 2011 8:00 AM
  • 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/|
    Tuesday, July 26, 2011 11:02 PM
    Moderator
  • 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?
    Thursday, September 22, 2011 4:07 PM
  • 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)

    Monday, November 7, 2011 9:00 PM
  • @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.

    Monday, March 12, 2012 2:13 AM
  • @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.
    Tuesday, August 7, 2012 12:24 PM
  • We are getting the same issue in out SCCM 2007 R3 with FEP integration. 

    Our table has blown out to 17+GB so far and counting. 

    Monday, June 24, 2013 11:59 PM
  • to delete a more managable number at a time (and thus prevent the size increase in the SQL log files), try this variation:

    DELETE top (1000) 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_%' ;
    GO 1000

    change the last "GO 1000" to any number you like to manage the number of iterations performed, deleting 1000 rows at a time.

    Monday, November 7, 2016 4:24 AM
  • to delete a more managable number at a time (and thus prevent the size increase in the SQL log files), try this variation:

    DELETE top (1000) 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_%' ;
    GO 1000 completely 

    change the last "GO 1000" to any number you like to manage the number of iterations performed, deleting 1000 rows at a time.


    It is completely unsupported to delete anytime from the cm07 databsae.

    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased

    Monday, November 7, 2016 7:37 AM
    Moderator