We see a fair number of issues relating to failure of the MOM DTS package to execute. There can be many reasons for this but when the DTS package fails the whole MOM system may have problems as a result.
If the MOM DTS package is not running correctly the MOM grooming jobs will fail. This can lead to a database that quickly grows toward the 30 GB maximum supported size. If autogrow is disabled (the supported setting) this will lead to the MOM system shutting down as there is no room available in the database for continued operations. This leads to cascading backlogs. First, the MOM server queues will full up with data that needs to go to the database. Once the MOM server queues fill we will signal agents to start storing data locally in their local queues. If the database is set to autogrow (not supported for MOM) we can quickly see the size expand. The server will remain operational but very likely will suffer from degraded performance.
Troubleshooting DTS failures is often easy but in some cases it’s helpful to understand what is actually happening behind the scenes. Before going further I should mention that the information provided below is to assist in troubleshooting DTS issues – it is not supported to modify the DTS job or to run the DTS job directly from SQL for typical production use.
The MOM 2005 DTS job is executed as a scheduled task. Have you ever wondered about this? A scheduled task that runs an EXE isn’t really a DTS job, right? So what really happens. Essentially, the EXE causes a DTS job to be extracted and that extracted DTS job is what runs in SQL. The DTS job consists of an inner job and an outer job. The inner job calls the outer job. The basic flow is as follows:
Extracts DTS package for execution – source and destination databases are specified on the command line.
Extracted when scheduled task runs. These packages are responsible for executing the actual DTS run. The outer package is started first which makes a few adjustments to the databae and then calls the inner package. The inner package does the actual work of moving data. When the inner package finishes control is handed back to the outer package which readjusts the database.
After each DTS run an event entry is added to the application event log giving status of the last run and, if a failure has occurred, the step at which the failure was noted. The individual steps of the DTS jobs can be reviewed if the respective DTS job is loaded into SQL Enterprise Manager.
Typically you won’t see the individual .DTS packages that are extracted during scheduled task execution. If you want to obtain these files for further troubleshooting you can run MOM.Datawarehousing.DTSPackageGenerator.exe with the /savetodisk option. This will save out the two DTS packages which can then be imported into SQL. From here it is possible to examine the individual steps in DTS design view. It is also possible to execute these jobs manually from SQL for troubleshooting purposes – if this manual execution isn’t done correctly, however, you may encounter failures. It’s probably a good idea to let us help you if you need to get this detailed.
There is much more detail that surrounds the execution of the DTS package. I’ll stop here for now. If comments warrant it I will consider adding more detail in a separate entry.