MDW - job collection_set_3_upload Step 2 raises error when a database is not online - by Kunk

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 778825 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/8/2013 4:58:54 AM
Access Restriction Public


On a SQL Server 2008 R2 system where we restore some databases during the night, step 2 of job collection_set_3_upload raises errors if it runs while a database is in the middle of a restore. 

Date		08.02.2013 02:30:00
Log		Job History (collection_set_3_upload)

Step ID		2
Job Name		collection_set_3_upload
Step Name		collection_set_3_upload_upload
Duration		00:00:53
Sql Severity		0
Sql Message ID		0
Operator Emailed		
Operator Net sent		
Operator Paged		
Retries Attempted		0

Executed as user: NETRTLSRV\SvcSQLServerAgent. SSIS error. Component name: DFT - Find and save sql text, Code: -1071636471, Subcomponent: LKUP - Look up query text on target server [475], Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.   An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Database 'smart4rtl' cannot be opened. It is in the middle of a restore.".   .  SSIS error. Component name: DFT - Find and save sql text, Code: -1071611309, Subcomponent: LKUP - Look up query text on target server [475], Description: OLE DB error occurred while fetching parameterized rowset. Check SQLCommand and SqlCommandParam properties.   .  SSIS error. Component name: DFT - Find and save sql text, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "LKUP - Look up query text on target server" (475) failed with error code 0xC0208253 while processing input "Lookup Input" (476). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.   .The master package exited with error, previous error messages should explain the cause.  Process Exit Code 5.  The step failed.
Sign in to post a comment.
Posted by Microsoft on 12/13/2013 at 9:33 AM
Kunk, we have investigated. This is by design: Data Collector will not continue if it fails at accessing a DB that is not online.
Posted by Microsoft on 3/8/2013 at 9:18 AM
Hello Kunk. Thank you for bringing this to our attention. We really do appreciate the feedback. We’ll investigate and get back to you. -Walter A Jokiel, Program Manager, SQL Server (
Posted by Kunk on 2/19/2013 at 6:54 AM
It is really easy to reproduce the issue. Set up standard mdw and configure the system data collections sets on a sql server instance. Then recover a db instance aon that server and start the job collection_set_3_upload.
It will fail on step 2.

The profile shows that the error occured during execution of the following statement.

exec sp_executesql N'exec [dbo].[sp_syscollector_sql_text_lookup] @P1',N'@P1 binary(64)',0x03001D001A2905206B5E100157A10000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Manually executing this statement shows following result :

Msg 927, Level 14, State 2, Procedure sp_syscollector_sql_text_lookup, Line 6
Database 'smart4rtl' cannot be opened. It is in the middle of a restore.

Examine the proc shows that the binary value represents a sql handle which is used to query the dm-object dm_exec_sql_text. This doesn't work while a db used in the statement represented by the handle is in recovery mode.

Normaly it isn't a big thing as the job runs every 15 min, but it always throws an alert in SCOM and I have to check if it is this "failure" or a real serious one.
And it happens every day as in this case it is a test environment where we restore databases every night.

But even if this normaly not happens on prodction systems the job should run without throwing errors if a database isn't online!

Wolfgang Kunk
Posted by Kunk on 2/18/2013 at 5:31 AM
1. I will try to repruduce the issue manually. Then I can capture a trace file
2. Of course. As far as I know collection_set_3_upload is the job for the system collection set "Query Statistics". which allways has number 3!
Posted by Sethu Srinivasan on 2/13/2013 at 11:27 AM
Hello Kunk,
Can you capture profiler traces when this error occurs?. Are you seeing this issue in "Query Statistics" upload job?

Sethu Srinivasan [MSFT]