How to troubleshoot DTS and database sizing issues in MOM 2005 Reporting
- 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
Description: MOM Reporting DTS Job failed to complete successfully
Event ID: 81
Event Source: DataTransformationServices
Description: The execution of the following DTS Package failed:
- 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 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 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).
- Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
- Expand the Microsoft SQL Servers\SQL Server Group\SQL Instance\Databases container.
Note SQL Instance is the named instance that contains the MOM Reporting Server database.
- Right-click SystemCenterReporting, and then click properties.
- On the Data Files tab, click the Space allocated (MB) value, and then type a larger file size.
- On the Transaction Log tab, click the Space allocated (MB) value, and then type a larger file size.
- Click OK to accept the changes.
- 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.
Verify that the SystemCenterDTSPackageTask scheduled task in Scheduled Tasks is scheduled to run automatically. If so, go to the next procedure.
- Use Windows Explorer to find the MOM reporting drive:\Program Files\Microsoft System Center Reporting\Reporting folder.
- Double-click the MOM.Datawarehouse.DTSPackageGenerator.exe file.
Moving smaller amounts of data by using the /latency switch or the /chunksize switchWe 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 switchThe 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:
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.
- Log on to the MOM Reporting server by using an account that has administrative rights.
- Click Start, click Run, type cmd, and then click OK.
- 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.
- Repeat step 3 by using smaller latency timeframes.
- 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.
The /chunksize switchA 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 errorThe 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:
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
- 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.
- On the Open Enterprise Manager, right-click the Reporting database SQL Server, and then click Properties, Connections tab.
- In the SQL Server Properties page, click the Connections tab.
- In the remote server connections section, type 1200 in the Query time-out value box.
- Repeat steps 1 through 3 on the OnePoint database SQL Server.
- Start the DTS job.
- If the DTS job still times out, repeat steps 1 through 6. However, increase the entry in step 3 to 2400, 3600, or 0.
- After the issue is resolved, reset the query time out value to 600 or whatever value is needed for subsequent jobs to run successfully.
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.
- Review the SQL Server logs on the SQL Server where the DTS package is being executed.
- Make sure that the OnePoint and SystemCenterReporting databases are set to Simple recovery mode. This is configured in the database properties Options tab.
- 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:"Microsoft Operations Manager"
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 availableIf 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
Id. de artículo: 899158 - Última revisión: 01/09/2007 00:35:16 - Revisión: 9.0
- kbopmanreporting kbopmaneventmgmt kbopmanalerts kbtshoot kbdatabase kberrmsg kbevent KB899158