I have been fighting an issue the last couple days because my Operations Manager database has exceeded the SQL Express limit of 4096MB. I have done some research to determine whether I need to adjust the grooming process but it appears that my settings should be fine. I did just determine that for some reason the StateChangeEvent table has entries dating back to the day I implemented SCE 2007 in our environment even though it says that the groomingprocess is completing correctly. I need to clean that table? Can I manually clean it? It is currently using almost 3/4 of my allowed DB space. Any Ideas?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
This should help..
Check "To groom Event dimension tables, set up a SQL job to run the following script" in the link below
http://blogs.technet.com/o
And "To clean up old StateChangeEvent data for disabled monitors" in the link below
http://blogs.technet.com/k
Thank you for the input so far. Given the small # of servers we have I chose to use the default install using SQL Server Express which, if grooming was functioning properly, should be fine for us. I read over the links sent and unfortunately the first talks about Ops Manager and the OperationsManagerDW database while my issue exists in th OperationsManager Database whil the OperationsManagerDW grooming appears to be functioning properly. In fact, the grooming on the OperationsManager database seems to be functioning as advertise for all but the StageChangeEvent table. I found the SQL statement from the second link the other day and ran it with no success. I think I will just try to manually clean that table after shutting down the SCE services. I played a bit wth the a couple of select statements this morning and was able to get the results I was looking for with select * from dbo.StateChangeEvent where TimeGenerated < DATEADD (m,-1,GETDATE()) which listed all State Changes older than 1 month. I will backup the entire environment and attempt a delete with the above statement and post my results later
Backed up the environment and ran the following to test to ensure that its results were State Change Events older than 1 month.
select * from dbo.StateChangeEvent where TimeGenerated < DATEADD (m,-1,GETDATE())
Confirmed results and ran delete from dbo.StateChangeEvent where TimeGenerated < DATEADD (m,-1,GETDATE()) to delete said entries.
Reran select * from dbo.StateChangeEvent where TimeGenerated < DATEADD (m,-1,GETDATE()) and confirmed that it only showed entries that were exactly 1 year old as of the date run.
Was able to shrink the database. Created a scheduled script to clean with this statement once a week until I can determine what the grooming isn't functoning.
Business Accounts
Answer for Membership
by: BitsqueezerPosted on 2009-11-06 at 07:46:37ID: 25760074
Hi,
it is about a year ago that I last used SCOM and read something about SCE, but as far as I remember both of them wants to have a "real" SQL Server edition and no SQL Server Express which indeed expires after reaching the 4 GB limit.
The SCOM is intended to collect every data from the first day on in data warehouses to give you an overview of problems in the IT environment, how often they occur and so on. So it is "by design" that it must collect all data to make it possible to show statistics about all configured things, especially when using the reporting services.
I'm not sure if there is a possibility to clean up old entries in SCOM/SCE. If you plan to use this very powerful tool I would recommend to upgrade your SQL Server Express to the next possible version, then your grooming should work normally until it reaches the end of your harddisk...:-)
Cheers,
Christian