Jaa

Timeout expired while publishing to SQL Database via SSDT

  • Artikkeli

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

Comments

  • Anonymous
    May 09, 2015
    Nice information

  • Anonymous
    March 14, 2017
    In Visual Studio 2015 / SSDT, this method worked to allow us to do a schema compare on our production SQL Server (Stand-alone, not Azure) by increasing the QueryTimeoutSeconds. In Visual Studio 2017 / SSDT, this registry path no longer exists and we are again having the timeout issue. How can this be resolved in VS 2017?

  • Anonymous
    April 17, 2017
    Like @Mikal, we're seeing the same error after installing VS 2017 and targeting standalone installs of SQL 2014. Only happens when targeting one very large database. Interesting thing is though is that the initial publish and subsequent publish of a derived project both work. Its only after our ETL process runs, that we can no longer publish or run schema compare against it. VS 2015 and VS 2013 both worked fine. It was only after installing VS 2017 that things broke.