Timeout expired while publishing to SQL Database via SSDT

  • Article

We recently saw some cases where customer migrated their SQL Database to Basic edition. After the migration they get the following error while attempting to publish their database via SQL Server Data Tools (SSDT) in Visual Studio 2013

Failed to import target model ...Unable to reconnect to database

Timeout Expired. The timeout period expired prior to completion of the operation or the server is not responding.

The reason why this happens is because the Publish (or Generate Script) operation reaches the CPU limit of Basic edition and ultimately times out. See below snapshot of sys.dm_db_resource_stats showing 100% CPU usage during the operation

end_time avg_cpu_percent avg_data_io_percent avg_log_write_percent

----------------------- ---------------- --------------------- ----------------------

2014-11-13 21:13:17.570 2.40 0.83 0.05

2014-11-13 21:13:02.517 100.00
0.00 0.00

2014-11-13 21:12:47.477 100.00
0.00 0.00

2014-11-13 21:12:32.430 100.00
0.00 0.00

2014-11-13 21:12:17.383 100.00
0.00 0.00

There are a couple of ways to overcome this

  1. Change to Standard edition and retry the operation
  2. Increase the query timeout for SSDT so that we complete the operation without timing out

 

In the current version of SSDT there is no user interface that allows modifying the query timeout. Instead the query timeout for SSDT is controlled by the following registry keys:

  • QueryTimeoutSeconds – Default value is 60 seconds. This controls the amount of time that SSDT will wait for a response to a standard (non-long running) query. Setting this value to 0 means to wait indefinitely for a response from the server. Please try increasing this value to 600 and retrying the operation.
  • LockTimeoutSeconds – Default value is 5 seconds. This controls the amount of time that SSDT will wait for a locked resource before timeout. Unlike the other keys, setting this key to 0 means to give up immediately when any locked resource is encountered. Please do not set this value to 0. If you suspect that the timeout issue may be caused by lock contention, increase this timeout and retry the operation.
  • LongRunningQueryTimeoutSeconds – Default value is 0. This controls the amount of time that SSDT will wait for a response to a long running query. A value of 0 means to wait indefinitely for a response from the server. Please leave this timeout at the default value of 0.

 

The registry key path depends on the version of Visual Studio used by the customer:

  • Visual Studio 2013 - HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\SQLDB\Database
  • Visual Studio 2012 - HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0\SQLDB\Database
  • Visual Studio 2010 - HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\SQLDB\Database

 

For this particular issue it is advisable to check for the QueryTimeoutSeconds setting in all three locations and increase it to 600 if it is present.

Author: - Rohit Nayak (@sqlrohit)

Reviewers: - Keith Elmore, SSDT Product Group

Escalation Services, Microsoft