Solved

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

Posted on 2014-12-30
11
173 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
[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
  • 6
  • 5
11 Comments
 
LVL 28

Assisted Solution

by:Dan McFadden
Dan McFadden earned 500 total points
ID: 40525120
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
ID: 40525126
Hi Dan,

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

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

Expert Comment

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

Dan
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:TAMUQITS
ID: 40525274
Hi Dan,

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

Assisted Solution

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

Author Comment

by:TAMUQITS
ID: 40526025
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
ID: 40529944
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 28

Assisted Solution

by:Dan McFadden
Dan McFadden earned 500 total points
ID: 40529957
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
ID: 40530058
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 28

Assisted Solution

by:Dan McFadden
Dan McFadden earned 500 total points
ID: 40530276
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 28

Accepted Solution

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

710 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