?
Solved

SCCM 2012 R2 Database growing

Posted on 2014-10-31
6
Medium Priority
?
1,243 Views
Last Modified: 2014-11-03
i have migrated my sccm server 2012 R2 from one to another with different site name of course however i noticed that my new SCCM database now after 3 months of the migration become 84 GB and i didn't find any error may cause that and i used an script to define the biggest table sizes in my SCCM and found a table called process_hist consuming about 60GB and i selected top 10000 rows trying to find out its content and it seems to be operating system task sequence
any advice here my SQL server is 2012 SP1 CU4
2014-10-30-13-00-16.png
SCCM.png
SCCM2.png
0
Comment
Question by:Centamin-SGM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 28

Expert Comment

by:Dan McFadden
ID: 40415666
There are plenty of articles talking about runaway table growth in SCCM.  Though the table names are different, the process to clean up the tables are the same.  Here are few links to solutions that others have used:

- http://blogs.technet.com/b/configurationmgr/archive/2011/08/30/eliminating-slack-space-in-the-configmgr-2007-database.aspx
- http://myitforum.com/myitforumwp/2012/03/20/configmgr-2007-database-growing-rapidly-due-to-size-of-ts_tasksequence-table/
- http://blogs.technet.com/b/configurationmgr/archive/2009/01/27/troubleshooting-database-growth-issues-in-configuration-manager-2007.aspx

You may want to read thru the SysAdmin Operations & Maintenance section of the product documentation:

http://technet.microsoft.com/en-us/library/gg682155.aspx

Hope this helps.

Dan
0
 

Author Comment

by:Centamin-SGM
ID: 40416881
hi dan
thanks for your help
i already tried to do dbcc cleantable on my very large table process_hist but that query didn't delete one single row so i had to use the below query to delete all rows in that big massive table
DELETE TOP (99) PERCENT
FROM PROCESS_HIST;
but that table grows every day with approximate amount 2GB do you have any idea how to solve that
0
 
LVL 28

Expert Comment

by:Dan McFadden
ID: 40419314
Can you post the content of the following file:

<ConfigMgr installation directory> \ Inboxes \ clifiles.src \ hinv \ SMS_def.mof

It may be that you have asset intelligence turned on and it can hit the process_hist table pretty hard.

Dan
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
LVL 28

Expert Comment

by:Dan McFadden
ID: 40419338
Forget the .mof file.

Open the SCCM Console and navigate to the following location:

1. Administration > Overview > Client Settings
2. Right click on the "Default Client Settings" and select Properties
3. Select "Hardware Inventory"
4. on the right, click the "Set Classes" button
5. Slide down to an item called "Process (Win32_Process)

Is it checked?  If so, this is what is hitting the process_hist table.  You can see that the objects under this item match the table structure in table in SQL.

If you are sure you do not need this info, uncheck the box and click OK out.

Dan
0
 

Author Comment

by:Centamin-SGM
ID: 40419371
thanks dan
yes Process (Win32_Process) was responsible of growing that table many thanks
i also have another big table in size called HinvChangeLog any idea what this is because it's about 6 GB
0
 
LVL 28

Accepted Solution

by:
Dan McFadden earned 1500 total points
ID: 40419422
I believe hinv = Hardware Inventory.

Have you checked that your SCCM Site Maintenance is configured to be on?  It sounds like your tables are not being cleaned up.

In SCCM Console:
1. Adminstration > Site Configuration > Click on "Sites"
2. Right-Click your site name and select "Site Maintenance"
3. The "Site Maintenance" box opens.

Our SCCM maintenance has all tasks enabled... except for the "Clear Install Flag" task which is disabled.

You can the various "Delete Aged ..." objects to see how long they stay around.  I believe the default is 90 days.  I would try reducing the time on the object "Delete Aged Inventory History."

Dan
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question