Timeout Expired Error When Publishing to SQL Database From SSDT
You may received below error when trying to publish the database with SQL Server Data Tools (SSDT) in Visual Studio after you migrating the SQL Server Database to Basic edition.
Failed to import target model DBName, Unable to reconnect to database
Timeout Expired. The timeout period expired prior to completion of the operation or the server is not responding.
This error occurs because the Publish or Generate Script operation high the CPU limit of Basic edition and finally times out. If you check the resource stats you may notice 100% CPU usage during this operation.
You can try couple of methods to get out of this issue, one of them is to upgrade to Standard edition and then retry the operation or try to increase the query timeout value for SSDT, so that the operation completes without timing out. In the present adaptation of SSDT there is no client interface that permits altering the question timeout. Rather the inquiry timeout for SSDT is controlled by the accompanying registry keys
QueryTimeoutSeconds – Default worth is 60 seconds. This controls the measure of time that SSDT will sit tight for a reaction to a standard (non-long running) inquiry. Setting this worth to 0 intends to sit tight inconclusively for a reaction from the server. If you don’t mind take a stab at expanding this quality to 600 and retrying the operation.
LockTimeoutSeconds – Default quality is 5 seconds. This controls the measure of time that SSDT will sit tight for a bolted asset before timeout. Dissimilar to alternate keys, setting this key to 0 intends to surrender quickly when any bolted asset is experienced. Kindly don’t set this quality to 0. In the event that you think that the timeout issue may be brought on by lock discord, expand this timeout and retry the operation.
LongRunningQueryTimeoutSeconds – Default worth is 0. This controls the measure of time that SSDT will sit tight for a reaction to a long running inquiry. An estimation of 0 intends to sit tight uncertainly for a reaction from the server. If its not too much trouble leave this timeout at the default estimation
You can check the “HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\SQLDB\Database” registry key and change the timeout value accordingly.
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings