MORE INFORMATION
Overview of the troubleshooting process
If the SMS synchronization process is unsuccessful and if you cannot determine the location where the processing fails, perform troubleshooting steps in a linear manner. To do this, verify the data flow in the following order:
- The scheduling process
Verify whether SMS has collected the schedule and whether SMS has entered
the schedule into the SMS database. - The data extraction process
Verify whether data has been successfully extracted
from the SMS database. - The file copy process
Verify whether the extracted data (.dat files) have been
copied to the SCRM server. - The ETL process
Verify whether the ETL process has successfully completed.
How to troubleshoot the scheduling process
When you schedule an SMS DTS job in the SCRM Administrator Console, the following actions occur:
- A schedule file is written to the outgoing folder for the particular SMS data source on the SCRMSMSShare share. The SCRMSMSShare share is known as the staging share. By default, the schedule file is written to the following folder:
C:\SCRM_Staging\SMS Data SourceName\Outbound
- The SCRM SMS Site Service on the computer that is running SMS 2003 collects the schedule file.
- The SCRM SMS Site Service creates a Microsoft SQL Server
Agent job for the schedule file.
- The schedule is entered into the SMS database according to the schedule that you specified when you configured the SMS DTS job in the SCRM Administrator Console.
To verify whether the schedule file has been picked up by the SMS server, examine the
%ProgramFiles%\Microsoft System Center Reporting
Manager\Log\SCRMLog.log file on the computer that is running SMS 2003. The following entries appear in this file during a typical run of the
Change Management Component thread of the SCRM SMS Site Service.
Note A typical run occurs when no schedule
file or watermark file is present in the staging share.
CChangeManagement::FindAndCopyWatermarkFile : Watermark File not found at
\\<SCRMServer>\SCRMSMSShare\<SMSDataSourceName>\Outbound\watermark.txt
Watermark file not found in outbound folder
cmd.exe /c "dir /B /OD "\\<SCRMServer>\SCRMSMSShare\<SMSDataSourceName>\Outbound
schedule*.txt" >> "C:\Program Files\Microsoft System Center Reporting
Manager\Log\schtmp.txt"" successfully executed
Opening temp file: C:\Program Files\Microsoft System Center Reporting
Manager\Log\schtmp.txt
Successfully opened temp file: C:\Program Files\Microsoft System Center Reporting
Manager\Log\schtmp.txt
Reading file name from temp file
Failed to read string
GetFileName Failed
The system cannot find the file specified.
The Change Management Component thread runs every five minutes. However, SCRM only puts watermark files and schedule files on the staging share when it is required. Therefore, these entries appear in the SCRMLog.log file many times. When a schedule file is put on the staging share, the Change Management Component
thread collects the file. In this situation, the following entries appear in the SCRMLog.log file:
Watermark file not found in outbound folder
Message File Source is
C:\Program Files\Microsoft System Center Reporting
Manager\Extraction\Status\SCRM_Message.log
Message File Dest is
\\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Inbound\SCRM_Message.log
CMessageFileTransfer::OnPoll : Copy Message file is succeed
cmd.exe /c "dir /B /OD
"\\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\schedule*.txt" >>
"C:\Program Files\Microsoft System Center Reporting Manager\Log\schtmp.txt""
successfully executed
Opening temp file: C:\Program Files\Microsoft System Center Reporting
Manager\Log\schtmp.txt
Successfully opened temp file: C:\Program Files\Microsoft System Center Reporting
Manager\Log\schtmp.txt
Reading file name from temp file
Preparing file name from read string
Read from temp file
Opening schedule file:
\\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt
Successfully opened schedule file:
\\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt
Reading schedule file:
\\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt
Successfully Read schedule file:
\\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt
Executing command: EXEC SCRM_SP_CreateJob 'sms_cen',@JobName =
'SC_SMS_Delta-Dts1_from <SMSDataSourceName>' ,@enabled =1, @SyncName ='Delta',
@Schedule ='@freq_type = 1,@freq_interval = 0,@freq_subday_type =
0,@freq_subday_interval = 0,@freq_relative_interval = 0,@freq_recurrence_factor =
0,@active_start_date = 20060420,@active_end_date = 0,@active_start_time =
141500,@active_end_time = 3200'
Successfully Executed command: EXEC SCRM_SP_CreateJob 'sms_cen',@JobName =
'SC_SMS_Delta-Dts1_from <SMSDataSourceName>' ,@enabled =1, @SyncName ='Delta',
@Schedule ='@freq_type = 1,@freq_interval = 0,@freq_subday_type =
0,@freq_subday_interval = 0,@freq_relative_interval = 0,@freq_recurrence_factor =
0,@active_start_date = 20060420,@active_end_date = 0,@active_start_time =
141500,@active_end_time = 3200'
CChangeManagement::FindAndCopyScheduleFile: Deleting schedule file
\\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt
CChangeManagement::FindAndCopyScheduleFile: Successfully deleted schedule file
\\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt
Reading file name from temp file
Failed to read string
GetFileName Failed
All the commands that appear in this log file run by using the credentials of the account that is used
to start the SCRM SMS Site Service on the computer that is running SMS 2003. If error messages are logged when this account tries to connect to the SCRM server, follow these steps:
- Verify whether the path that is specified in the SCRMLog.log file is available from the computer that is running SMS 2003.
- Verify whether the account under which the SCRM SMS Site Service runs has been granted permissions to the path that is specified in the
SCRMLog.log file.
- If no entries appear in the SCRMLog.log file, verify whether the SCRM SMS Site Service is running.
The following entry in the SCRMLog.log file shows that the
SCRM_SP_CreateJob stored procedure has run on the computer that is running SMS 2003:
Executing command: EXEC SCRM_SP_CreateJob 'sms_cen',@JobName =
'SC_SMS_Delta-Dts1_from <SMSDataSourceName>' ,@enabled =1, @SyncName ='Delta',
@Schedule ='@freq_type = 1,@freq_interval = 0,@freq_subday_type =
0,@freq_subday_interval = 0,@freq_relative_interval = 0,@freq_recurrence_factor =
0,@active_start_date = 20060420,@active_end_date = 0,@active_start_time =
141500,@active_end_time = 3200'
When this stored procedure runs, a SQL Server Agent job is created for a full synchronization or for a delta synchronization, depending on the kind of synchronization that is specified in the schedule file. The SQL Server Agent job is configured to run on the same
schedule as the schedule that is specified in the SCRM Administrator Console. When the SQL Server Agent job runs, a record
that resembles the following is entered into the
SCRM_MTD_SyncSchedule table of
the SMS database.
| SyncScheduleID | ScheduleDateTime | Status | SyncTypeID | Job Name |
| 1 | date and time | NULL | 1 | SC_SMS_Full-Dts1_from SMS DataSourceName |
If this record does not appear in the
SCRM_MTD_SyncSchedule table of
the SMS database when the SMS DTS job is scheduled, follow these steps:
- Verify whether the SQL Server Agent job is enabled, and then make sure that the SQL Server Agent job is not failing. To do this, follow these steps:
- Start the SQL Server Management Studio program, and then connect to the computer that hosts the SMS database.
- Expand SQL Server Agent, expand Jobs, right-click the SQL Server Agent job, and then click View History. For example, right-click SC_SMS_Full-Dts1_from SMS DataSourceName, and then click View History.
- Examine the information that appears in the Log File Viewer - ServerName dialog box to determine whether the SQL Server Agent job successfully finished.
- If the SQL Server Agent job failed, make sure that the account under which the SQL Server Agent job runs is a
member of the SC DW SMS DTS group on the SMS database server.
How to troubleshoot the data extraction process
After the record for the SMS synchronization job has been added to the SCRM_MTD_SyncSchedule
table in the SMS database, it is detected by the
SyncProcessAgentJob SQL Server Agent job. This SQL Server Agent job runs every five minutes.
When the
SyncProcessAgentJob runs, the data extraction process starts. When this process starts, the entry that appears in the SCRM_MTD_SyncSchedule table of the SMS database resembles the following.
| SyncScheduleID | ScheduleDateTime | Status | SyncTypeID | JobName |
| 1 | date and time | InProgress | 1 | SC_SMS_Full-Dts1_from SMS DataSourceName |
If the value that appears in the
Status column of this table is not set to
InProgress within five minutes of the scheduled task being added to the SMS database, follow these steps:
- Verify that the SyncProcessAgentJob SQL Server Agent job is enabled, and then make sure that this job is scheduled to run
every five minutes.
To do this, follow these steps:
- Start the SQL Server Management Studio program, and then connect to the computer that hosts the SMS database.
- Expand SQL Server Agent, expand Jobs, right-click SyncProcessAgentJob, and then click Properties.
- In the Select a page pane, click General, and then make sure that the Enabled check box is selected.
- In the Select a page pane, click Schedules, click the job that is displayed in the Schedule list box, and then click Edit.
- In the Job Schedule Properties - Daily Schedule Every 5 Mins dialog box, examine the values that are displayed under Daily frequency.
- Click Cancel, and then exit the Job Properties - SyncProcessAgentJob dialog box.
- Make sure that the account under which the SyncProcessAgentJob SQL Server Agent job runs is a member of the SC DW SMS DTS group on the SMS database server.
To view the accounts under which the SyncProcessAgentJob SQL Server Agent job processes run, follow these steps:
- Start the SQL Server Management Studio program, and then connect to the computer that hosts the SMS database.
- Expand SQL Server Agent, expand Jobs, right-click SyncProcessAgentJob, and then click View History.
- In the right pane of the Log File Viewer - SMS ServerName dialog box, expand the SyncProcessAgentJob job.
- Examine the information that appears in the Step Name column and in the corresponding Message column. Information that resembles the following is displayed.
| Date | Step ID | Server | Job Name | Step Name | Notifications | Message |
| date | 1 | SMS ServerName | SyncProcessAgentJob | Manage SyncProcess | blank | Executed as user: NT AUTHORITY\SYSTEM. schedule information |
- Examine the job history of the SyncProcessAgentJob SQL Server Agent job to determine whether the job is failing when it runs. To do this, repeat steps 2a through 2d.
If the SyncProcessAgentJob SQL Server Agent job fails on the Manage SyncProcess step, examine the ManageSynchProcess_Log.log file to help determine the reason for this failure. This log file is located in the following folder on the computer that is running SMS 2003: %ProgramFiles%\Microsoft System Center
Reporting Manager\Log
- Verify whether an earlier job that has a status of InProgress is displayed in the SCRM_MTD_SyncSchedule table of the SMS database. An earlier job must finish before a new job can start.
After the data extraction process starts, the progress is tracked in the following tables in the SMS database:
- SCRM_MTD_ExtractQueue
- SCRM_MTD_ExtractTargetStatus
The SCRM_MTD_ExtractQueue table displays the data extraction status for each SMS database table. To view the table names together with the data extraction status, run the following Transact-SQL query.
SELECT SCRM_MTD_ExtractTargetList.TableName,SCRM_MTD_ExtractQueue.Status
FROM SCRM_MTD_ExtractTargetList
JOIN SCRM_MTD_ExtractQueue
ON SCRM_MTD_ExtractTargetList.ExtractTargetListID =
SCRM_MTD_ExtractQueue.ExtractTargetListID
The SCRM_MTD_ExtractTargetStatus table displays the start time and the end time for each
data extraction operation. Additionally, this table displays the output file name and the retry count for each data extraction operation. To view information about the synchronization job that is currently in progress, run the following Transact-SQL query.
SELECT * FROM SCRM_MTD_ExtractTargetStatus
WHERE SyncScheduleID IN
(SELECT SyncScheduleID FROM SCRM_MTD_SyncSchedule WHERE status = 'InProgress')
If the SCRM_MTD_ExtractQueue table displays an error for any extraction operation or if the
SCRM_MTD_ExtractTargetStatus table displays a
RetryAttempt value that is greater than
0 (zero) for any table, follow these steps:
- Examine the DTS_MDS_SYNCH.log file to help determine the reason for the data extraction failure. This is the log file for the DTS job that performs the data extraction operation. This file is located in the following folder on the computer that is running SMS 2003:
%ProgramFiles%\Microsoft System Center
Reporting Manager\Log
- Examine the DTSExtractPackage_Log.log file and the SCRMLog.log file to help determine the reason for the data extraction failure. These log files are located in the following folder on the computer that is running SMS 2003:
%ProgramFiles%\Microsoft System Center Reporting Manager\Log
To verify whether all the required data has been successfully extracted from the SMS database, examine the SCRM_Message.log file. This file is located in the following folder on the computer that is running SMS 2003:
%ProgramFiles%\Microsoft System Center
Reporting Manager\Extraction\Status
If all the required data has been successfully extracted from the SMS database, this file contains an "Extraction Finished" entry. Also, if the file copy operation to the SCRM server has not yet
started, 91 .dat files should be present in the following folder on the computer that is running SMS 2003:
%ProgramFiles%\Microsoft System
Center Reporting Manager\Extraction\DATA
If these 91 .dat files are not present in this
folder, verify the value that appears in the
ParamValue column of the
ExtractionDataFilePath parameter entry of the
SCRM_MTD_Configuration table in the SMS database.
How to troubleshoot the file copy process
After all the required data has been extracted from the SMS database and after the "Extraction Finished" entry has been written to the SCRM_Message.log file, the SCRM
SMS Site Service copies the data files to the staging share on the SCRM server.
The progress of this file copy operation is logged in the SCRMLog.log file. This file is located in the following folder on the computer that is running SMS 2003:
%ProgramFiles%\Microsoft System Center Reporting Manager\Log
If no entries appear in this file, follow these steps:
- Verify whether the SCRM SMS Site Service is started on the computer that is running SMS 2003.
- Verify whether the 91 .dat files are present in the following folder on the computer that is running SMS 2003:
%ProgramFiles%\Microsoft System Center
Reporting Manager\Extraction\DATA
If the SCRMLog.log file displays an entry such as "Access Denied" or "Server not Found," follow these steps:
- In the SMS database, examine the value that appears in the ParamValue column of the ShareDirectory parameter entry of the SCRM_MTD_Configuration
table. This value displays the path of the particular SMS data source share on the SCRM server.
This value must display the correct path of this SMS data source.
- Make sure that the account under which the SCRM SMS Site Service on the SMS server runs has Full
Control permissions to the Inbound folder of the SCRM server's share that is specified in the ParamValue column of the ShareDirectory path. Also,
make sure that this account has both of the following permissions assigned for the SCRM server's SCRMSMSShare share:
- On the computer that is running SMS 2003, try to manually connect to the Inbound folder of the share that is specified in the ParamValue column of the ShareDirectory path. When you do this, first log on to the computer that is running SMS 2003 by using the credentials of the account under which the SCRM SMS Site Service runs.
Note By default, the SCRM SMS Site Service runs under the LOCAL SYSTEM account. In this situation, start a command prompt by using the LOCAL SYSTEM account, and then try to connect to the Inbound folder of the staging share on the SCRM server.
To verify whether the file transfer operation successfully finished, follow these steps:
- Verify that the "File Transfer Finished" entry appears in the SCRMLog.log file. This file is located in the following folder on the computer that is running SMS 2003:
%ProgramFiles%\Microsoft System Center Reporting Manager\Log
- Verify that the "File Transfer Finished" entry appears in the SCRM_Message.log file. This file is located in the following folder on the computer that is running SMS 2003:
%ProgramFiles%\Microsoft System Center Reporting Manager\Extraction\Status
How to troubleshoot the ETL process
After all the SMS data files have been successfully copied to the SCRM server, the
ProcessDWJob SQL Server Agent job on the SCRM server starts the ETL process. This SQL Server Agent job starts the various DTS jobs that execute
the ETL stored procedures and operations. You can troubleshoot the ETL process by using the following tables and views in the
SystemCenterStaging database on the SCRM server:
- STG_MTD_Status_Extract
This table logs the progress and the details of the data extraction operation from the .dat files to the SystemCenterStaging database.
- STG_MTD_AgentStatus
This table logs the start times and the end times of each stage of the ETL process. - STG_ETL_STATUS_VIEW
This view displays the start time, the end time, the status, and the row count for each task in the ETL process.
- STG_ETL_HISTORY_VIEW
This view displays the start time, the end time, the status, and the row count for each task in the ETL process for SMS Synchronization jobs that have finished.
To determine the status of the ETL process, examine the latest entries in the
STG_MTD_AgentStatus table. The following four high-level steps are logged in this table:
- Extract
This step covers the extraction of data files to the Inbound tables. - Transform
This step covers the transformation of Inbound data to Outbound tables.
- Load
This step covers the loading of data in the data warehouse. - Watermark
This step covers watermark generation and the removal of data files.
When each of these steps starts, the current date and time is recorded in the
ExecutionStart column of the
STG_MTD_AgentStatus table. When the particular step ends, the current date and time is recorded in the
ExecutionEnd column of this table. Therefore, all the following actions occur:
- A step that has completed has both an ExecutionStart value and an ExecutionEnd value.
- A step that has not yet started does not have an ExecutionStart value or an ExecutionEnd value.
- A step that has started but that has not yet completed has an ExecutionStart value but does not have an ExecutionEnd value.
You can use this information to help determine where the ETL process fails.
The Extract step
If the ETL
Extract step does not finish, follow these steps:
- Examine the STG_MTD_Status_Extract table in the SystemCenterStaging database. The following columns in this table indicate the .dat files that have been successfully extracted to the SystemCenterStaging database and the files that are currently being extracted:
- DataFileName
- Status
- TaskStartDateTime
- TaskEndDateTime
This table also contains historical data for earlier SMS synchronization jobs. You can use this historical data to help determine how long it may take to extract each file. - If the extraction process failed for one or more .dat files, examine the LOG_DTS_EXTRACT.Log file to determine whether one or more failure messages are logged. This file is located in the following folder on the SCRM server:
%ProgramFiles%\Microsoft System Center Reporting
Manager\DTS\SMS
The Transform and Load steps
During the
Transform step and the
Load step of the ETL process, SMS data is moved from
the Inbound staging tables in the
SystemCenterStaging database to the Outbound staging tables in the
SystemCenterStaging database. This data is then inserted into the SystemCenterPresentation
database. You can use the STG_ETL_STATUS_VIEW view to examine the status and the details of these steps. This view lists each task that is performed by these steps, together
with the task's start time, end time, and status.
The
ETLTaskStatusDescription column of the STG_ETL_STATUS_VIEW view displays one of the following values to indicate the
status of each task:
If a task fails, the
ProcessDWJob SQL Server Agent job fails. The
ProcessDWJob SQL Server Agent job runs again within five minutes. The
ProcessDWJob SQL Server Agent job then runs the failed task.
Note The
ETLTaskStatusDescription column does not display a failure status for the failed task. In this situation, the task status remains with a value of
RUNNING. A new row is created for the task when the ETL process next starts.
Therefore, if the STG_ETL_STATUS_VIEW view displays multiple entries for the same task and if each of these entries has a
ETLTaskStatusDescription value of
RUNNING, the task is failing.
If you determine that a task is failing, examine the
ETLTaskPackageName column for the failing task. This column displays the DTS
package that starts the task. From this information, you can determine which DTS log file to examine to help determine the reason that the task is failing. The
following table lists the DTS packages together with their corresponding log files. All these log files are located in the %ProgramFiles%\Microsoft System Center Reporting
Manager\DTS\SMS folder on the SCRM server.
| DTS task | Log file name |
| DTS_ETL | LOG_DTS_ETL.Log |
| DTS_LOAD | LOG_DTS_LOAD.Log |
| DTS_TRANSFORM | LOG_DTS_TRANSFORM.Log |
| DTS_WATERMARK | LOG_DTS_WATERMARK.LOG |
| DTS_WRAPPER | LOG_DTS_WRAPPER.Log |
After you determine the DTS task that is failing, examine the corresponding log file for error messages that may indicate the reason for the DTS task failure.
Data collection scripts
In addition to the log file information, you can run data collection scripts to obtain troubleshooting information from the SMS database and from the SCRM database. You can use the information that you gather to help troubleshoot the reason for the failing SMS synchronization job.
On the SMS database server, run the following query against the SMS database to collect troubleshooting data.
/*
||This script has to be run on the SMS Database
Purpose: purpose of this script is to collect data to help troubleshoot SMS data
source issue.
*/
print '** START: SMS Data Collection for Troubleshooting SCRM Data Source **'
print '-- Following is output from Staging Meta data tables --'
print '---------------------------------------------------------------------'
print '** Output for the SCRM SMS Data Source Configuration Parameters **'
print '------------------------------------------------------------------'
SELECT *
FROM SCRM_MTD_Configuration
print '** Output for the Table List to be BCP OUT in the current running synch
**'
print
'--------------------------------------------------------------------------'
SELECT *
FROM SCRM_MTD_ExtractQueue
print '** Output the List of Tables and their Synch Types (Full, Delta)
Applicability **'
print
'---------------------------------------------------------------------------------'
SELECT *
FROM SCRM_MTD_ExtractTargetListSyncType
print '** Outputs information on Table/DAT File specific BCP OUT details **'
print '--------------------------------------------------------------------'
SELECT *
FROM SCRM_MTD_ExtractTargetStatus
print '** Outputs current state of WaterMarks on the SMS Database **'
print '-------------------------------------------------------------'
SELECT *
FROM SCRM_MTD_LowHighTideWaterMark
print '** Outputs current state of Schedule Queue for the Data Source**'
print '----------------------------------------------------------------'
SELECT *FROM SCRM_MTD_SyncSchedule
On the SCRM database server, run the following query against the SCRM database to collect troubleshooting data.
/*
||This script has to be run on the SCRM SQL Server
Purpose: purpose of this script is to collect data to help troubleshoot SCRM
issue.
*/
USE SystemCenterStaging
GO
print '** START: SCRM Data Collection for Troubleshooting **'
print '-- Following is output from Staging Meta data tables --'
print '-------------------------------------------------------'
print '** Output for the High Level Job Steps (E,T,L,Watermark) **'
print '-----------------------------------------------------------'
SELECT * FROM STG_MTD_AgentStatus
print '** Output for Staging Configuration Parameters **'
print '-------------------------------------------------'
SELECT * FROM STG_MTD_Configuration
print '** Output for the Status of the Bulk Insert of Files into Inbound **'
print '--------------------------------------------------------------------'
SELECT * FROM STG_MTD_Status_Extract
print '** Outputs the Current Staging Lineage Number **'
print '------------------------------------------------'
SELECT * FROM STG_LineageStaging_DW
print '-----------------------------------------'
SELECT * FROM STG_ETL_ErrorStringLog_VIEW
print '-----------------------------------------'
SELECT * FROM STG_ETL_ErrorStringState_VIEW
print '** Outputs the DTS Run History for Data Sources **'
print '--------------------------------------------------'
SELECT * FROM STG_ETL_HISTORY_VIEW
print '** Outputs the Current DTS Running Status for Data Sources **'
print '-------------------------------------------------------------'
SELECT * FROM STG_ETL_STATUS_VIEW
GO
print '** Outputs the Job History for SCRM DWProcessJob **'
print '---------------------------------------------------'
SELECT j.name,h.* FROM msdb..sysjobs j JOIN msdb..sysjobhistory h
ON j.job_id = h.job_id WHERE j.name = 'ProcessDWJob'
print '** Outputs the list of SMS Data Sources added **'
print '------------------------------------------------'
SELECT DataSourceName FROM SystemCenterConfiguration.dbo.SC_MMC_DataSource
WHERE DataSourceTypeID = 1
GO
print '** Outputs the records in the Lineage Dimension from Presentation DB **'
print '-----------------------------------------------------------------------'
SELECT * FROM SystemCenterPresentation.dbo.SC_Lineage_Dimension
print '--------------- END --------------------------'
print '-----------------------------------------'