Solved

SCCM 2012 R2 Database growing

Posted on 2014-10-31
6
998 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
  • 4
  • 2
6 Comments
 
LVL 26

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 26

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 26

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 26

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article will review the basic installation and configuration for Windows Software Update Services (WSUS) in a Windows 2012 R2 environment.  WSUS is a Microsoft tool that allows administrators to manage and control updates to be approved and ins…
The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now