Opsmgr (SCOM) 2007 R2 logs are eating up all free space


We have a setup of OpsMgr 2007 R2 (CU6) (ver 6.1.7221.99). We got one RMS and one Management Server. We are hosting our database on external DB cluster.  Here is the version detail of DB server:

SQL 2008 R2 Enterprise Edition ver 10.0.5500.0

We are monitoring only 100 agents but our log size is around 800 GB and keep increasing. I don't know where to start in order to troubleshoot this issue.

I have run few commands on our datawarehouse DB and took the screenshots that are attached to this question.

I know, we need to groom our database, please help me in this regard.
Who is Participating?
Dan McFaddenSystems EngineerCommented:
Here is an MSDN article about tempdb, what it is, what is does and how to monitor it:

link:  http://msdn.microsoft.com/en-us/library/ms345368(v=sql.105).aspx

Also, a little about trying to ID what is using the tempdb.

link:  http://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-tempdb-transaction-log

Dan McFaddenSystems EngineerCommented:
It would appear that you are keeping a ton (400 days worth) of performance and state data.  I would reduce the retention of this data down to a manageable level.

You can change the global grooming setting by doing the following:

1. open the SCOM console
2. click on "Administration"
3. go to "Settings"
4. right-click "Database Grooming"
5. edit and update the setting for "Perfomance Data"
6. edit and update the setting for "State Change Events Data"

Here are 2 articles that explain grooming and how to manage it:

- http://searchwindowsserver.techtarget.com/feature/Database-grooming-for-System-Center-Operations-Manager-2007
- http://blogs.technet.com/b/kevinholman/archive/2010/01/05/understanding-and-modifying-data-warehouse-retention-and-grooming.aspx

TAMUQITSAuthor Commented:
Hi Dan,

Thanks for your reply. It's strange, my settings reflect only 2 days :|

I have attached the screenshot.
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Dan McFaddenSystems EngineerCommented:
I would go thru the 2nd link and try the actions detailed there.

TAMUQITSAuthor Commented:
Hi Dan,

I have changed the grooming settings. Now when or how the DB size will be decreased?
Dan McFaddenSystems EngineerCommented:
You can force grooming according to this article.  Basically you will manually run the stored procedures directly on SQL Server.  You will need access to the SQL Server Management Studio (SSMS) GUI.

Link:  http://blogs.technet.com/b/kevinholman/archive/2008/02/13/grooming-process-in-the-operations-database.aspx

Also, after running the grooming process, you can then look at the quantity of data in the DB by using SSMS to do a database report.  You will see how big the actual data and log files are and how much on the allocated space IN the database/log files are being utilized.  Based on the amount of space free, you can do a shrink on the data file.

report link:  http://www.databasejournal.com/features/mssql/article.php/3743596/SQL-Server-Management-Studio-Reports-and-Dashboard.htm

shrink link:  http://msdn.microsoft.com/en-us/library/ms189035(v=sql.105).aspx

After another day or two, you may be able to reclaim a little more disk space after the grooming runs automatically.

TAMUQITSAuthor Commented:
Great... I will follow tomorrow. I am more worried about log files because their recovery model is full but they are not being truncated by TSM backup. They say it's not possible. Do u know anything about it.
TAMUQITSAuthor Commented:
Well, I have followed all the documents and did shrinking on log files too but in vain, still my free space has gone. I guess, I may have to install OpsMgr 2012 R2 from scratch and delete the old log and DB file :(
Dan McFaddenSystems EngineerCommented:
Have you either rebooted the server or run a full backup thru SSMS?  Can you look into the events logs for errors related to SQL or SCOM/

Also, can you give a little more detail about all the sql server file sizes (both MDF and LDF files)?

1. how big are all the system db files (tempdb especially)?
2. how big are the SCOM db files?
3. are there any other databases hosted on the db server?

Dropping SCOM 2007 in the hope that the issues will disappear is not always a solution.  The issue may lay in another software component.  Trying to determine the source is always the best solution.

TAMUQITSAuthor Commented:
Hi Dan,

Thanks again for the reply.

We backup through Tivoli Storage Manager that do not truncate the logs. I heard, it's a OpsMgr 2007 R2 bug. DBs' recovery models are full.

Here are your requested details:
1. System DBs

Master: Size: 80mb, available: 37.20MB
Model:  Size: 7.31MB, available: 1.06mb
MSDB: Size: 792 mb, available: 15.81 mb
Temdb: Size: 97 GB

Initially I couldn't see the properties of TempDB, then I had to run sp_helpdb 'tempdb'

2. SCOM DB files:

OperationsManager: Size: 61GB, Available: 27.71GB

OperationsManagerAC: Size 37GB, available: 51GB

OperationsManagerDW: Size:  590GB, available: 61GB

3. Yes, there are other DBs hosted on this server but they have their own file locations.
Dan McFaddenSystems EngineerCommented:
OK, so here is my thinking... a tempdb that is 97GB is a bit of an issue.

This may or may not be a SCOM issue.  You will need to investigate why tempdb is 97GB.  You will need the assistance of a DBA, if one is available since troubleshooting tempdb space consumption require some knowledge of SQL Server.

I have, in Production, SCOM 2007 and SCOM 2012R2, the tempdb's are less than 1GB on each server.

Since you reconfigured the grooming settings for the main database, I would try to reconfig the SCOM DW settings:

link:  https://social.technet.microsoft.com/forums/systemcenter/en-US/b661e6a1-35e3-4917-ad07-9bd6dac0c514/how-to-reduce-the-amount-of-data-stores-in-scom-datawarehouse-for-reports

But more importantly is the tempdb size issue.  The other databases on the SQL server can affect the tempdb size, not just SCOM.  Are the other databases hosted on this server custom or 3rd party vendor apps?

If they are custom databases, I would talk with the developers & dbas that are maintaining the db.  

Here is a Microsoft article about shrinking the tempdb:

link: http://support.microsoft.com/kb/307487/en-us

After you reduce the tempdb, I would monitor the activity on the tempdb to ID what is hitting it so much.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.