Solved

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

Posted on 2014-12-30
11
127 Views
Last Modified: 2015-02-22
Hi,

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.
Current-Grooming-Settings.JPG
Last-Grooming-Time.JPG
number-of-days-of-total-data-of-each-typ
oldest-and-newest-recorded-timestamps-of
Which-Tables-used-the-most-space.JPG
0
Comment
Question by:TAMUQITS
  • 6
  • 5
11 Comments
 
LVL 26

Assisted Solution

by:Dan McFadden
Dan McFadden earned 500 total points
Comment Utility
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

Dan
0
 

Author Comment

by:TAMUQITS
Comment Utility
Hi Dan,

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

I have attached the screenshot.
DWGroomSettings.JPG
0
 
LVL 26

Expert Comment

by:Dan McFadden
Comment Utility
I would go thru the 2nd link and try the actions detailed there.

Dan
0
 

Author Comment

by:TAMUQITS
Comment Utility
Hi Dan,

I have changed the grooming settings. Now when or how the DB size will be decreased?
0
 
LVL 26

Assisted Solution

by:Dan McFadden
Dan McFadden earned 500 total points
Comment Utility
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.

Dan
0
Why do Marketing keep bothering you?

Is your marketing department constantly asking for new email signature updates? Are they requesting a different design for every department? Do they need yet another banner added? Don’t let it get you down! There is an easy way to manage all of these requests...

 

Author Comment

by:TAMUQITS
Comment Utility
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.
0
 

Author Comment

by:TAMUQITS
Comment Utility
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 :(
0
 
LVL 26

Assisted Solution

by:Dan McFadden
Dan McFadden earned 500 total points
Comment Utility
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.

Dan
0
 

Author Comment

by:TAMUQITS
Comment Utility
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.
0
 
LVL 26

Assisted Solution

by:Dan McFadden
Dan McFadden earned 500 total points
Comment Utility
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.

Dan
0
 
LVL 26

Accepted Solution

by:
Dan McFadden earned 500 total points
Comment Utility
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
0

Featured Post

The curse of the end user strikes again      

You’ve updated all your end user’s email signatures. Hooray! But guess what? They’re playing around with the HTML, adding stupid taglines and ruining the imagery. Find out how you can save your signatures from end users today.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

744 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

15 Experts available now in Live!

Get 1:1 Help Now