Reply
Frequent Visitor
Posts: 5
Registered: ‎12-28-2016
Accepted Solution

SCCM Template timeout

Hi,

 

My template seems to get timeout during scheduled task operation.

This is what i found in log:

""
azurebcp.exe Information: 0 : Source query processed in 12,717 (s)
    DateTime=2016-12-28T12:36:44.2789998Z
azurebcp.exe Error: 0 : System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)
   at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
   at System.Data.SqlClient.TdsParserStateObject.WriteByte(Byte b)
   at System.Data.SqlClient.TdsParser.WriteBulkCopyValue(Object value, SqlMetaDataPriv metadata, TdsParserStateObject stateObj, Boolean isSqlType, Boolean isDataFeed, Boolean isNull)
   at System.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
   at System.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at azurebcp.AzureQueryBcp.<>c__DisplayClass5_0.<SaveToServer>b__1()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.<ExecuteAction>b__0()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at azurebcp.Program.ProcessOptions(Options o)
   at azurebcp.Program.Main(String[] args)
ClientConnectionId:e6834baa-10af-4288-8461-866edc7268d0
Error Number:-2,State:0,Class:11
""

 

And powershell script output:
"
Starting processing for pbist_sccm.site_staging
        StartDateTime: 12/28/2016 11:54:37
        EndDateTime: 12/28/2016 11:54:38

Starting processing for pbist_sccm.update_staging
        StartDateTime: 12/28/2016 11:54:38
        EndDateTime: 12/28/2016 11:54:39

Starting processing for pbist_sccm.user_staging
        StartDateTime: 12/28/2016 11:54:39
        EndDateTime: 12/28/2016 11:54:39

Starting processing for pbist_sccm.usercomputer_staging
        StartDateTime: 12/28/2016 11:54:39
        EndDateTime: 12/28/2016 11:54:40

Starting processing for pbist_sccm.computermalware_staging
        StartDateTime: 12/28/2016 11:54:40
        EndDateTime: 12/28/2016 11:54:40

Starting processing for pbist_sccm.computer_staging
        StartDateTime: 12/28/2016 11:54:40
        EndDateTime: 12/28/2016 11:54:40

Starting processing for pbist_sccm.malware_staging
        StartDateTime: 12/28/2016 11:54:40
        EndDateTime: 12/28/2016 11:54:40

Starting processing for pbist_sccm.scanhistory_staging
        StartDateTime: 12/28/2016 11:54:40
Error copying data!!!

"

 

Any ideas what can i do ?

 

Best Regards,

Karol


Accepted Solutions
Frequent Visitor
Posts: 14
Registered: ‎12-20-2016

Re: SCCM Template timeout

Can you please check if the target database is in simple recovery mode instead of full?

If that doesn't help, please email pbisolntemplates@microsoft.com and we'll continue to investigate.

View solution in original post


All Replies
Frequent Visitor
Posts: 14
Registered: ‎12-20-2016

Re: SCCM Template timeout

Hi Karol,

 

Looks like the task failed to copy the data generated by the query in ScanHistory.sql (and it took 12717 seconds to prepare the result). That is strange considering that previous queries run a second or so.

 

Is it possible that the machine where the scheduled task run went to sleep? Or that the destination server was under a high load (or is it a low throughput Azure database)? We set an internal timeout to 60 seconds, which should be more than enough to push a batch size of 20000 records to the destination (via bulk copy interface).

 

Did you try to manually re-run the scheduled task? If it fails again, the next steps would be:

  • check how long does ScanHistory.sql take to execute and how many records it returns
  • decreasing the batch size (search for " .\azurebcp.exe -b 20000" in dataload.ps1 and try a lower value, let's say 5000)?

 

Frequent Visitor
Posts: 5
Registered: ‎12-28-2016

Re: SCCM Template timeout

Hi,

 

When i manually run ScanHistory.sql query it takes 20min to process and gives me 4800 rows of data. I notoiced that results showed up in sql studio in batches of around ~800 results during execution. On-premise SQL 2016 is not particulary under load, so that's very strange. All of the rest queries takes max 3min to run, execept this particular one.

 

I will decrease batch size to 3000 and return with results.

 

Thanks, you helped me a lot! ;-)

Frequent Visitor
Posts: 5
Registered: ‎12-28-2016

Re: SCCM Template timeout

[ Edited ]

Hi,

 

i did not have success in importing data to PowerBI. My azurebcp still timeout eventually:

 

azurebcp.exe Error: 0 : System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

 

I've tried running azurebcp with -b switch set to range 100-5000 but it's not heliping ;-( Problem i have is only with ScanHistory.sql query, the rest of quieries go througt just fine.

 

What i did is comment out hashtable entry with scanhistory.sql for script to skip it, and the rest of data loads just fine.

 

Best Regards,

Karol

Frequent Visitor
Posts: 14
Registered: ‎12-20-2016

Re: SCCM Template timeout

Can you please check if the target database is in simple recovery mode instead of full?

If that doesn't help, please email pbisolntemplates@microsoft.com and we'll continue to investigate.

Frequent Visitor
Posts: 5
Registered: ‎12-28-2016

Re: SCCM Template timeout

That was the case. I will change that settings and give feedback.

 

Thanks,

Karol

Frequent Visitor
Posts: 5
Registered: ‎12-28-2016

Re: SCCM Template timeout

Hi,

 

just to give you feedback. After setting this and database restart. Everything seems to work just fine.

 

Best Regards,

Karol