No, this post is not about how to spay or neutor your mother. It's about Microsoft Operations Manager and one piece of troubleshooting that will save the day for you when the daily partitioning and grooming job fails... and it WILL fail.
Here's how this one goes. There's an error that popped up with a MOM 2005 database I manage where the groom job would continuously fail with something like 'drop failed FK_EventParam18 is not a constraint'... Awesome. At this point your OnePoint database is going to keep on growing forever and ever because it can't purge old data. Too bad that it's no longer supported by MS when it hits >30gb. The solution? Fix the problem yourself of course!
I hunted around the internet for help with this one but was left with nothing. Finally I started stepping through the code of each stored procedure as it would have been executed to find what was being called and when.
I got tired of that and threw up a trace.
I eventually narrowed it down to the fact that it really wanted to drop a constraint named in the error on a table of a similar name. Now of course I'm sure there's a table somewhere that holds what to drop and what not to drop but I really didn't feel like editing that so I took the easy way out: I created a constraint with the required name on the proper table and BAM! It worked.
What was the constraint you ask? heh... 1=1 ;)
Hopefully some other DBA out there (or someone else stuck looking after a bloated and mis-managed MOM 2k5 deployment) will see this and be saved a lot of investigative work. (Legal self-help: I am not making any claim that this will work for you and/or won't harm your database. Use this advice at your own risk!)
Oh- and the other thing I did to purge my db (which was 130gb!)? I modded the sp_purge (or whatever it's called) to be able to groom and purge starting at 125 days rather than the 60 days it maxes out at via the GUI. This allowed me to purge data and shrink the db in a controlled fashion that wouldn't blast the storage system with IOPS it couldn't hope to handle.
Here's how this one goes. There's an error that popped up with a MOM 2005 database I manage where the groom job would continuously fail with something like 'drop failed FK_EventParam18 is not a constraint'... Awesome. At this point your OnePoint database is going to keep on growing forever and ever because it can't purge old data. Too bad that it's no longer supported by MS when it hits >30gb. The solution? Fix the problem yourself of course!
I hunted around the internet for help with this one but was left with nothing. Finally I started stepping through the code of each stored procedure as it would have been executed to find what was being called and when.
I got tired of that and threw up a trace.
I eventually narrowed it down to the fact that it really wanted to drop a constraint named in the error on a table of a similar name. Now of course I'm sure there's a table somewhere that holds what to drop and what not to drop but I really didn't feel like editing that so I took the easy way out: I created a constraint with the required name on the proper table and BAM! It worked.
What was the constraint you ask? heh... 1=1 ;)
Hopefully some other DBA out there (or someone else stuck looking after a bloated and mis-managed MOM 2k5 deployment) will see this and be saved a lot of investigative work. (Legal self-help: I am not making any claim that this will work for you and/or won't harm your database. Use this advice at your own risk!)
Oh- and the other thing I did to purge my db (which was 130gb!)? I modded the sp_purge (or whatever it's called) to be able to groom and purge starting at 125 days rather than the 60 days it maxes out at via the GUI. This allowed me to purge data and shrink the db in a controlled fashion that wouldn't blast the storage system with IOPS it couldn't hope to handle.
No comments:
Post a Comment