Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-12-30
11
Medium Priority
?
219 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 2000 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 2000 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 2000 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 2000 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
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 to another domain controller. Log onto the new domain controller with a user account t…

722 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