Does your OpsDB keep growing? Is your localizedtext table using all the space?

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

Derek — Thu, 16 Oct 2008 18:33:48 GMT

One of our databases had almost 20 million rows in localized text. Thanks for the post

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

xacolabril — Fri, 31 Oct 2008 16:50:59 GMT

Oh! Our OperationsManager database hd 18 milions of rows in LocalizedText and our performance is very poor.

Thank you for the post too.

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

Zied — Tue, 28 Apr 2009 18:48:00 GMT

we have 6 millions of rows in LocalizedText, the performance, alerts and events are poor...

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

zied — Tue, 28 Apr 2009 18:48:51 GMT

Thank you very very much for the informations, and for this wonderful Blog..

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

MVerbaas — Fri, 08 May 2009 18:35:04 GMT

Kevin, great post. We really had a big table. Unfortunately the first time we ran into diskspace issues. That's how I figured out the errorprocessing part does not delete the first temporary table (#PublisherMessageReverseIndex).

My experience upgrading to OpsMgr R2 RTM

Kevin Holman's OpsMgr Blog — Sat, 23 May 2009 20:01:23 GMT

I upgraded my test lab from SP1 to R2-RTM this weekend.   My current test lab consists of the following

Optimizing the performance of your Opsmgr Console and reducing DB size

System Center Operations Manager — Thu, 28 May 2009 12:27:51 GMT

I had troubles with the Opmsgr Console performance at serveral clients. One of the most important things

Steve Rachui is an OpsMgr blogging machine

The Operations Manager Support Team Blog — Wed, 10 Jun 2009 16:24:53 GMT

If you’ve been reading this blog for any length of time then you’re already familiar with the postings

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

bradje — Tue, 16 Jun 2009 06:51:35 GMT

Lifesaver!

Thankyou,

John Bradshaw

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

Daniel Elliott — Mon, 22 Jun 2009 09:46:30 GMT

Thank you Kevin,

Our environment is large with 30 Exchange 2007 servers and 180,000 mailboxes.

Finding this post has save me many hours of frustration, our DB was 70gb with 50 million entries in the localizedtext table. After running your scripts our DB is now 4gb and operating with far less noise.

It would be good if this was resolved as I don’t really want to rebuild our SCOM environment with R2. Would also be nice if Microsoft release the native Exchange 2007 MP, two years after exchange 07 and we are still monitoring with a converted 03 exchange MP (poor form msoft).

Once again, thanks Kevin!!

re: anwers

kevinhol — Mon, 22 Jun 2009 18:02:38 GMT

1. This issue is resolved in R2, for the most part.

2. By moving to the native Exchange MP for R2, you also remove this issue. Microsoft HAS shipped the native Exchange MP. However - due to it needing some native powershell modules that were added in R2, it is R2-only MP.

3. R2 is not a "rebuild" it is an upgrade, and installs just as most hotfixes do.

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

Pascal Slijkerman — Tue, 23 Jun 2009 14:50:10 GMT

Thanks for the script!

Whe monitor about 130 agents with more then 25 MP's. Whe had over 20.000.000 rows of localizedtext.

The script took 27,5 hours!!! On a HP Proliant DL380 G5 with P500 raid controller and 512MB BBC.

Our database was 26GB and has dropped to a 2.4GB

Ít's now nice and fast :-)

re: 27.5 hours???

kevinhol — Tue, 23 Jun 2009 16:13:45 GMT

So, typically, I expect 20 minutes per million rows.

I would have estimated your cleanup to be around 7 hours, 14 MAX.

This makes we wonder if you really have good disk I/O for the SCOM subsystem? You are using a DL380 - with only 130 agents, how are the disks set up on this server? Are you using RAID5? Do you have the RMS and DB on the same server?

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

Gerardo — Wed, 24 Jun 2009 23:41:17 GMT

Hi, may be you can help me. The result of the script was that WorkflowTimestamps table has 9 million of rows so i dont know how to reduce the space.FYI the grooming process finish ok. so what can i do?

re: WorkflowTimestamps table???

kevinhol — Wed, 24 Jun 2009 23:49:51 GMT

I have no idea what "WorkFlowTimestamps" table is, or how it relates to this issue. Can you give more information on how you deduced that table name? Was that in an error output or something?

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

Gerardo — Thu, 25 Jun 2009 00:25:36 GMT

Sory, I'll try to explain better. I ran the "Simple query to display large tables"... and the result was (I'm showing only the top 10)

WorkflowTimestamps 29979232 451864 0

Event_13 95344 15344 0

ProcessRuleComment 44120 392 0

Event_15 40864 7048 0

Event_14 28048 3888 0

SampledNumericDataSource 20816 12856 0

SampledNumericData_13 18744 9464 0

Alert 10144 2432 0

SampledNumericData_15 8264 4400 0

Attribute 8184 1688 0

so my Onepoint database is full and can not accept more data from clients. I suspect that WorkflowTimestamps is the problem.

re: Workflowtimestaps

kevinhol — Thu, 25 Jun 2009 00:42:47 GMT

That is MOM2005 - not SCOM. The script above ONLY applies to SCOM and I hope you didnt run it against your MOM 2005 Onepoint DB?

I dont know what would cause that to be the top table consumer, or 29GB in size - but something is very wrong with that. I would open a case with PSS support immediately.

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

martit01 — Wed, 22 Jul 2009 02:39:43 GMT

Do I ever need to be concerned about localizedtext tables in the OpsMgr DW? Or is only for OpsMgr DB?

Thanks,

Tom

re: DW

kevinhol — Wed, 22 Jul 2009 02:42:17 GMT

There is no LocalizedText table in the DW.

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

Ravi Gupta — Thu, 30 Jul 2009 15:17:53 GMT

do we need to shutdown our SCOM application (by stopping all 3 services)to run the script (deletepublisher script) or can we run the script while SCOM is fully operational?

re: stop services?

kevinhol — Thu, 30 Jul 2009 20:21:23 GMT

There is no requirement to stop any services in order to run the cleanup script.

That said - if you have a large management group, with poor disk I/O on the backend, and your DB is always hammered.... the cleanup script will run faster if you disable the Health Service on the RMS and all management servers. However - that is not a requirement, and essentially stops all monitoring, so I dont recommend that unless the script will not complete due to I/O issues. But at that point - you have bigger issues with I/O than just localizedtext table size. :-)

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

raed fouad — Sun, 08 Nov 2009 11:28:22 GMT

Dear Kevin,

you will save my life since i have arround 40,000,000 row in that table, but i have windows 2008 and once i extracted the file its with .TXT extension. how i can run in on windows 2008 and wich extension i have to use, since i change the extension from .TXT to .VBS bnut it didnt work.

waiting you reply and thanks again.

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

kevinhol — Sun, 08 Nov 2009 17:47:01 GMT

You PASTE the contents of these TXT files into a new SQL query window - targeting the OpsDB SQL instance.

re: Does your OpsDB keep growing? Is your localizedtext table using all the space?

raed fouad — Mon, 09 Nov 2009 07:21:05 GMT

Dear Kevin;

thanks alot for your help, im running it now.