DTS job can't run - Reporting server dead - Onepoint at 10GB - need urgent help please

 Hi All,

MOM 2005 on one server, Onepoint on another.  MOM 2005 Reporting and SCR DB on another server.  The reporting server died recently and can't be repaired (out of warranty).  We're very short on human resources at present so some jobs (like building another reporting server) are pushed back.  However, as a result of losing the reporting server the Onepoint DB has grown to 10GB.  I know the DTS job needs to run but it must have been on the reporting server that died.

What I need to know is, is there any way of configuring MOM so that reporting is no longer needed, without uninstalling reporting from the mom reporting server.  We don't have time or resuorces at the moment to build a new reporting server and restore the reporting DB.

Thanks in advance.

 If you do not mind losing historical data then you can spoof the time/date that the DTS transfer completed in the Onepoint database:

When the groom job runs on Onepoint, it gets the date from the timedtslastran from the Reportingsettings table and then works out the the date of alerts to resolved based on your retention period in MOM i.e. 4 days. So, the DTS job ran OK today, the groom job takes off 4 days, anything older gets groomed out of the database.

You can either change the date manually to 02/07/2009 00:05:00 (this is utc time btw) which would groom out from 28/6/2009 00:05:00 leaving 4 days worth of alert/perf data (again, based on your own retention in MOM - Global Settings Database Grooming)

The query to view the date in the table is as follows:

Use Onepoint
SELECT TimeDTSLastRan from ReportingSettings

To amend this date:

USE Onepoint
UPDATE ReportingSettings
SET TimeDTSLastRan='02/07/2009 00:05:00'

Then manually kick off the SQL Server Agent job "MOMX Partitioning And Grooming"

You could put something like GetDate() in there instead of the date and schedule the job until you get your Reporting server back up and running.

Obviously back up your db prior to messing around with the db directly and no responsibility taken etc etc

Hope that helps.

Dave

 This was also mentioned in a previoous post:

http://systemcentercommunity.com/forums/t/4250.aspx

Cheers

Dave, thanks for your help.  It'll get us through until we get the reports server rebuilt.  Much appreciated.

Glad to see the MOM 2005 stuff is still being used!