Kevin Holman

25 Jun 2010 3:49 PM

Dont pay attention to how much it has deleted - the bulk of the job is creating a temp table in tempDB which will contain all the events we need to delete.

If you are over 1 hour per million rows as you say - I'd argue that it is likely your database is vastly undersized from an I/O perspective....

There are only two options to speed this process up other phan just plowing through it...

1. Stop the management server and RMS health, config, and SDK services, essentially shutting down the management group. This speeds up the cleanup a lot in larger management groups allowing all DB activity to be reserved for the cleanup job.

2. Stop the MS and RMS servers, and then detach the database - and attach it elsewhere on a much faster and more powerfull SQL server. You need a SQL server with REALLY good disk I/O, lots of CPU, enough memory to do the job (8GB should be fine but more is better), FAST disks where TempDB resides, and distinct disks for DB and tlog.

Holland G Humphrey

25 Jun 2010 8:54 PM

The script finally completed...it took 28 hours and after I ran the 2nd script (we are still SP1) it now has 365K rows (it was over 20 million). The reindex, which was not completing due to deadlock issues, completed in 16 minutes for the entire database (it was running for multiple hours before getting killed due to the deadlock).

The server our operationsDB resides on is a big quad-core server with 64 GB of RAM. Since we separate both the tempdb log and data files on their own 33 GB volume (attached to the SAN by dual fiber cards), the allocation of the 18 GB tempdb log file completed fairly quickly. Had it not been for the locking/blocking I was seeing yesterday, it would have completed much faster.

Now, Kevin, my next question...even though the LocalizedText table now has 365K rows, it still has data with a TimeAdded timestamp of 2008-08...why would it still have data for every month since August 2008 and not just for the last few months? Is this a setting that needs to be set? Can I run a DELETE query to delete all data in LocalizedText with a TimeAdded < '2010-01-01'?

NOTE: I am the DBA for SCOM in our shop...not the SCOM admin, so I don't have any control over the console and frankly, the Admin, who is somebody offshore, doesn't even know I am helping him/her by handling this problem. I'm sure they have seen poor performance, and in the past we have opened tickets with MS about the reason the data file kept growing, yet nobody from MS told us a bug existed that could cause this problem. In fact, they told our SCOM admin at the time, that the fact our database was 60 GB was normal for a company like ours. I knew at the time that was crap, because with MOM 2005, the operations database never got larger than 20 GB and if it did it meant we had a problem somewhere probably with the nightly load to the warehouse database.

PS -- Thanks so much for posting this blog article...it saved us.

Kevin Holman

26 Jun 2010 12:53 AM

Congrats!!! 365k rows is nothing! Sounds like a much more performant database!

Re: still seeing old stuff in LT table - IGNORE it. You have "as good as you can get and be supported" the way it is now. You CANNOT delete anything manually - as you can corrupt the database and any manual deletes are completely unsupported. Only those specific scripts, run exactly as they are - are supported. There are some old items that should be there by design, and we have also found some LT stuff that neither of our script catch... so it may be that this is "as good as it gets". Believe me - it wont have a huge console performance impact unless it gets really, really big again.

See my recent post on the OpsDB from a database perspective - I call out several other SQL related items that you can look at to see if something is awry in your environment. Then be a hero and send the data on to your SCOM admins. :-)

HGHumphrey71

28 Jun 2010 7:45 PM

Thanks for the tip and I'd love to read the article but can't find it. Would you mind replying with a link to that article?

Thanks.

Kevin Holman

28 Jun 2010 8:37 PM

blogs.technet.com/.../management-group-checkup-a-database-perspective.aspx

Lisa

29 Jul 2010 8:44 PM

Hello,

I have executed the cleanup script on our R2 Operations Manager database and it only executes for approximiately 5 minutes with no errors. Below is the output of the execution:

(5326165 row(s) affected)

Duplicate key was ignored.

(72939 row(s) affected)

(5326165 row(s) affected)

(5326151 row(s) affected)

(0 row(s) affected)

I execute a reindex and the rows remain. Any ideas of what I might have amiss is appreciated.

Kevin Holman

29 Jul 2010 9:00 PM

Hi Lisa -

Are you on SP1 or R2?

How many rows remain in the LT table?

Did you run both scripts or just one?

Lisa

29 Jul 2010 9:58 PM

Hi Kevin,

I am on R2, and 5,374,237 rows still exist.

I only executed the LocalizedTextCleanupforSP1.txt script. I did not attempt the other, but can certainly do so.

Lisa

2 Aug 2010 9:09 PM

Hi Kevin,

Wanted to let you know I executed the second script and it performed a cleanup of the localizedtext table. Thanks for the help.

Lisa

snakeaj

6 Oct 2010 4:18 PM

did this on Management Server of Essentials Business Server becuase of problems with SCE 2007

no Problems! took me 76 minutes to clean up 3.5 million lines on a Hyper-V VM.

thx!!

Ibrahim

12 Nov 2010 1:21 PM

if the first script fails, do I have to start from the begining or I can rerun the script and it will pickup from where it left?

Sander

2 Dec 2010 10:48 PM

When attempting to run the sp1 script on a database upgraded to R2/CU3, I get the following error:

Error 8152: "String or binary data would be truncated"

I believe that the contents of the LTValue table exceed the parameters of LTValueHash (VARBINARY(32)). I tried setting the value to VARBINARY(max), then I receive a different error:

1919 "Column 'LTValueHash' in table '#LTHashStrings' is of a type that is invalid for use as a key column in an index."

Any suggestions? Thanks.

Kevin Holman

2 Dec 2010 11:08 PM

@Sander -

Ugh. We did change some things around alerts on CU3. I sure hope we didnt break this cleanup script. I will look into this as soon as I can and get back to you.

Sander

3 Dec 2010 3:37 PM

Thanks Kevin. My bad for running the update prior to running the script.

FYI, I tried removing 'LTValueHash' from the indexes, and changing value to VARCHAR(max), but got the same 8152 error. 26.4 MM rows in Localzedtext. Looking forward to your response!

Kevin Holman

3 Dec 2010 4:14 PM

@Sander -

I finished testing. There is no issue and no repro. I can run the scripts to cleanup LT just fine on my R2 CU3 database. If yours are not working - I would have to assume you have corrupted something in your database outside the set DB schema. Modifying ANY index on ANY table in OpsMgr is unsupported - and makes your database suspect. Did you do any edits to the database ever, prioer to running the cleanup script?

It is possible you are facing something previously unknown, but I can say with confidence it isnt CU3 at the root of the issue. I would recommend a call to PSS support if you have a large LocalizedText table, need to clean it up, and the published scripts on my blog in the zip file are failing.