Solved

SCCM 2012 R2 Database growing

Posted on 2014-10-31
6
1,140 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SBS 2008 to Server 2012 6 41
IP Address white listing in Windows Firewall 5 61
Help with IIS intermittent hangs on Windows 2012 5 60
Firewall support--by the month? 10 59
I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
In this Micro Tutorial viewers will learn how to use Boot Corrector from Paragon Rescue Kit Free to identify and fix the boot problems of Windows 7/8/2012R2 etc. As an example is used Windows 2012R2 which lost its active partition flag (often happen…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

739 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