Microsoft KB Archive/899158

= How to troubleshoot DTS and database sizing issues in MOM 2005 Reporting =

Article ID: 899158

Article Last Modified on 1/9/2007

-

APPLIES TO


 * Microsoft Operations Manager (MOM) 2005

-



SYMPTOMS
You experience one or more of the following symptoms after you install Microsoft Operations Manager (MOM) Reporting Services in MOM 2005:  MOM reports are empty or incomplete. When you try to view some reports by using the MOM Reporting Console, you may receive the following error message:

There is no data available for this report.

 The size of the OnePoint database suddenly grows. The database grows even though you have not changed one or more of the managed servers or rule groups in the MOM configuration group. The OnePoint database may grow beyond the recommended 30-gigabyte (GB) limit.  The following events appear in the Application log on the MOM Reporting Server:

Event ID 1001

Source: MOM.Datawarehousing.DTSPackageGenerator.exe

Description: MOM Reporting DTS Job failed to complete successfully Event ID: 81

Event Source: DataTransformationServices

Description: The execution of the following DTS Package failed: 

Event ID 1001 provides a brief description of why the Data Transformation Services (DTS) package failed. Event ID 81 provides more details about the cause of the error. The description of both events may indicate that the database has insufficient free space. Examples of some typical error descriptions are as follows:
 * Could not allocate space for object 'SC_EventFact_Table' in database 'SystemCenterReporting' because the 'PRIMARY' filegroup is full.
 * The log file for database 'SystemCenterReporting' is full. Back up the transaction log for the database to free up some log space.
 * Could not allocate space for object '(SYSTEM table id: -548802855)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

The “MOM Reporting DTS Job failed to complete successfully” event rule may also generate an alert. This alert indicates that the task that copies the reporting data from the OnePoint database to the reporting database has failed.



CAUSE
These symptoms occur if the DTS package cannot copy the reporting data from the OnePoint database to the MOM Reporting Server database. This causes the OnePoint database to stop grooming, and it will continue to grow until the DTS job runs successfully.

The DTS package may fail when the following conditions are true:
 * The DTS package tries to copy lots of data to the MOM Reporting Server database.
 * The MOM Reporting Server database has insufficient free space to complete the copy task.
 * The SystemCenterDTSPackageTask scheduled task in Scheduled Tasks has been modified to not run automatically.

The MOM Reporting Server database or log may run out of free space when the DTS package tries to copy data from the OnePoint database. This behavior may occur if the MOM server has already collected lots of data. For example, this behavior occurs if one of the following conditions is true:
 * The MOM server has been running for a long time before you install MOM Reporting Services.
 * You install many agents to the MOM server group at the same time.
 * You add a new rule that generates a large number of events or a large amount of performance data.
 * You have installed management packs that have added lots of new rules.
 * Your version of MOM 2005 Reorting has not been upgraded to Service Pack 1 (SP1).



RESOLUTION
To resolve this issue, increase the size of the SystemCenterReporting database and the SystemCenterReporting log. To do this, follow these steps on the MOM Reporting Server:
 * 1) Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
 * 2) Expand the Microsoft SQL Servers\SQL Server Group\ \Databases container.

Note is the named instance that contains the MOM Reporting Server database.
 * 1) Right-click SystemCenterReporting, and then click properties.
 * 2) On the Data Files tab, click the Space allocated (MB) value, and then type a larger file size.
 * 3) On the Transaction Log tab, click the Space allocated (MB) value, and then type a larger file size.
 * 4) Click OK to accept the changes.
 * 5) Make sure that Tempdb is not running out of disk space. Tempdb holds all temporary tables and temporary stored procedures. Tempdb will automatically increase and decrease as the data engine performs actions. If Tempdb runs out of space, an error appears in the SQLERROR log. You can either set the Data Files and Transaction Log to Automatically grow file, or you can expand the files by manually using Enterprise Manager or Transact-SQL.

Verify that there is enough physical disk space on the hard disk drive where Tempdb is stored. Move Tempdb to a larger hard disk drive if necessary.

After you increase the size of the SystemCenterReporting database and the SystemCenterReporting log, or Tempdb, manually run the DTS job again. This step copies the data from the OnePoint database to the SystemCenterReporting database. To manually run the DTS job, follow these steps.

Verify that the SystemCenterDTSPackageTask scheduled task in Scheduled Tasks is scheduled to run automatically. If so, go to the next procedure.
 * 1) Use Windows Explorer to find the  :\Program Files\Microsoft System Center Reporting\Reporting folder.
 * 2) Double-click the MOM.Datawarehouse.DTSPackageGenerator.exe file.

Note You can also manually run the SystemCenterDTSPackageTask scheduled task in Scheduled Tasks, which is in Control Panel.



Moving smaller amounts of data by using the /latency switch or the /chunksize switch
We recommend that you move data in smaller packets if you have a large amount of data to move at the same time. This can help avoid time outs in SQL Server. This can also avoid putting too much of a load on the computer that is running SQL Server.

The /latency switch
The MOM.Datawarehouse.DTSPackageGenerator.exe file supports a latency parameter that lets you specify the age of the data to transfer to the SystemCenterReporting database. You can use the latency parameter to determine how much data is copied to the SystemCenterReporting database. The latency parameter uses the following syntax:

MOM.Datawarehousing.DTSPackageGenerator.exe /latency:

We recommend that you use the latency parameter if the DTS job has failed for more than three or four days.

To see when the last time the DTS job successfully ran, examine the ReportingSettings table in the OnePoint database for the TimeDTSLastRan value. This is displayed in Coordinated Universal Time (Greenwich Mean Time).

In this example, the DTS job has not run in 30 days. Therefore, you can use the latency switch with a value of 20 and the job will migrate all data that is older than 20 days into the MOM Reporting database. The next time that you run the job, specify a value of10 to migrate anything that is older than 10 days. The last time that you run the job, remove the /latency switch from the MOM.Datawarehousing.DTSPackageGenerator.exe to migrate the remaining data. By doing this, the data can be moved over without overwhelming the system.

Note This example assumes that you have 30 days of data in the OnePoint database.
 * 1) Log on to the MOM Reporting server by using an account that has administrative rights.
 * 2) Click Start, click Run, type cmd, and then click OK.
 * 3) At the command prompt, copy the command line parameters of SystemCenterDTSPackageTask, type /latency:20, and then press ENTER.

The “20” represents 20 days in this example.
 * 1) Repeat step 3 by using smaller latency timeframes.
 * 2) Remove the /latency switch the last time the job is run. The default latency setting is 5 minutes. At this point, all data that is older than 5 minutes will have been moved.

Note After you have moved all of your data to the Reporting Server, you must remove the latency parameter on the scheduled job.

The /chunksize switch
A new size parameter has been added in MOM 2005 SP1 for use when you run the DTS job from the command line:
 * /chunksize: Chunk size in days. High volume data will be transferred in increments of the chunk size. This enables the transfer of high-volume data with limited log space. If you specify a value of 0 (zero), this will not chunk the data. (Default = 1 day).
 * /maxchunks: Maximum number of chunks to create when chunking is enabled. (Default = 10 chunks).

DTS job still fails and you receive a time-out error
The DTS job may still fail and generate a time-out error if the amount of data that is to be copied is very large.

The description text of events 1001, event 81, or the output of a DTS command that has the /silent switch removed may include an error message that is similar to the following:

Timeout expired.

The timeout period elapsed prior to completion of the operation or the server is not responding.

Execution terminated by the provider because a resource limit was reached

If the details in the corresponding event 81 do not indicate an error about the database or transaction log being full, this is most likely due to connectivity issues or network problems. Therefore, use one of the following methods:
 * Run the DTS job again.
 * If the DTS job is not successful, change the query time out value.

The remote query time out can be increased from the default of 600 to 1200 seconds or more, or it is possible to set the remote query time out to 0 (unlimited) to see if the job will complete. To change the remote query time out, follow these steps:

Note The remote query time out value should be changed on both the SQL Server that hosts the Reporting database and the SQL Server that hosts the OnePoint database.
 * 1) On the Open Enterprise Manager, right-click the Reporting database SQL Server, and then click Properties, Connections tab.
 * 2) In the SQL Server Properties page, click the Connections tab.
 * 3) In the remote server connections section, type 1200 in the Query time-out value box.
 * 4) Repeat steps 1 through 3 on the OnePoint database SQL Server.
 * 5) Start the DTS job.

Notes
 * 1) * If the DTS job still times out, repeat steps 1 through 6. However, increase the entry in step 3 to 2400, 3600, or 0.
 * 2) * After the issue is resolved, reset the query time out value to 600 or whatever value is needed for subsequent jobs to run successfully.

Event ID 1001 may contain information that resembles the following: Step StepInvokeInnerPackage failed. Step Error Source: Microsoft OLE DB Provider for SQL Server Step Error Description: (1:SC_Inner_DTS_Package) SubStep 'DTSStep_ExecuteSQLTask_SC_SampledNumericDataFact_View_1_Insert' failed with the following error: OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached. (Microsoft OLE DB Provider for SQL Server (80040e14): OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].) (Microsoft OLE DB Provider for SQL Server (80040e14): [OLE/DB provider returned message: Timeout expired]) Execution was canceled by user. Step Error Code: -2147220441 Step Error Help File: Step Error Help Context ID:0

Additional troubleshooting information
 Review the application and system logs on the SQL Server where the DTS package is being executed.</li> Review the SQL Server logs on the SQL Server where the DTS package is being executed.</li> Make sure that the OnePoint and SystemCenterReporting databases are set to Simple recovery mode. This is configured in the database properties Options tab.</li>  To obtain the error information output directly from the program, copy the DTS command line from the scheduled task to a command prompt.

For example, C:\Program Files\Microsoft System Center Reporting\Reporting>MOM.Datawarehousing.DTSPackageGenerator.exe /silent /srcserver:servername /srcdb:OnePoint /dwserver:servername /dwdb:SystemCenterReporting /product:&quot;Microsoft Operations Manager&quot;

Remove the /silent switch. Run the command from the command prompt. If the job fails, the error information will be output on the screen. This can be copied to a text file for future reference.

It the DTS job succeeds no output is displayed and a command prompt will be displayed.

If no more disk space is available
If the SystemCenterReporting database has reached the limit of the physical hard drive capacity, either more disk space will need to be added or the database grooming interval will need to be reconfigured to be more aggressive. By default, the period to retain data in the SystemCenterReporting database is 395 days. When you install the MOM 2005 Reporting component, a stored procedure p_updategroomdays is created in the SystemCenterReporting database. You can use this stored procedure to configure the number of days that data is retained in the SystemCenterReporting database. For more information about how to change the grooming interval, click the following article number to view the article in the Microsoft Knowledge Base:

887016 How to modify the number of days to retain data in the SystemCenterReporting database in Microsoft Operations Manager 2005

</li></ul>

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

873235 How to stop the transaction log of a SQL Server database from growing unexpectedly

317375 A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server

Additional query words: completely empty grows failure

Keywords: kbopmanreporting kbopmaneventmgmt kbopmanalerts kbtshoot kbdatabase kberrmsg kbevent KB899158

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.