?
Solved

SCCM Database

Posted on 2016-08-06
6
Medium Priority
?
125 Views
Last Modified: 2016-08-12
My SCCM database is around 190 GB and still growing? Can some one suggest best way maintain SCCM DB? I am using SQL 2014 SP2 with SCCM 1602 now
0
Comment
Question by:asnagesh
[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
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:Mike T
ID: 41746413
Hi,

That sounds massive. Have you got software inventory turned on and are you collecting files or something? I've worked on several 5000 seat sites and the DB is less than 10GB.

As for maintaining the DB, you need to look at several SQL scripts by Ola Hallengren. His scripts are now the de facto way to look after SQL:

https://ola.hallengren.com/

The other thing to look at is whether your DB is set to Full or SImple mode. You don't want full. It is only meant for databases where you would want/need to replay actions, like finance. CM does not need that and yes, it bloats the logs a lot. I had to cleanup the mess from a predecessor for SCOM and the logs went from 20GB to 2GB.

Mike
0
 

Author Comment

by:asnagesh
ID: 41746694
only default settings are enabled and software inventory will capture the exe file information from computers but it does not capture the entire files.  When I click on shrink I am getting 43% free space. can I shrink sccm database?
0
 
LVL 18

Accepted Solution

by:
Mike T earned 2000 total points
ID: 41747113
OK, I forgot to ask - how many computers does CM manage? Also are you just talking about the Transaction Log? If so, as I said CM doesn't want it, doesn't need it. At all.

Many DBA's mistakingly think that an SMS/SCCM database needs to be handled like a transactional database, which it is not. Database recovery mode should be set to simple... in other words, we do not want the transaction log file for anything!

Ref: from MS tech answers

So the answer is yes you can BUT do a full SQL backup first.
then do a full CM backup.

Then check/validate both backups are GOOD backups by restoring to another box.

Note: I've used shrink personally on the SCOM transaction log and it worked perfectly but never on SCCM. I believe neither make full use of it, so it's just there to satisfy SQL.

I would get a second opinion if I were you, rather relying on my word alone :).

Mike
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:asnagesh
ID: 41747120
1. I have 1000 clients
2. I am talking about CM database not the transaction log
3. Recovery Model is simple
0
 

Author Comment

by:asnagesh
ID: 41747121
sorry it is not 1000, it is 10000 clients
0
 
LVL 18

Expert Comment

by:Mike T
ID: 41747384
OK - 190GB still sounds big to me for 10K machines.

If you're talking about the CM I would definitely get a second opinion.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
Binary Differential Replication, What it is, how it works and how it differs from standard delta file replication
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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