Sql-server – “Lock request time out period exceeded” when publishing SqlServer databases in parallel

lockingparallelismsql serversql server 2014ssdt

I am working on a continuous integration environment using SqlServer2014. There I need to publish many SqlServer databases simultaneously on the same instance. Our publish process uses a .sqlproj file executed by MSBuild, which generates a publishing script.

Sometimes, during this publishing process we got an error: "Lock request time out period exceeded". It happens when two or more publishing scripts are being executed in parallel, more specifically on the procedure sp_fulltext_database.

Searching for further explanations, I found out that the default lock timeout limit is -1, which is unlimited. Even so, the error keep showing. Trying to change the lock timeout limit to 20 seconds through the query:

SET LOCK_TIMEOUT 20000;

The error did not happen again.

My question is: Is this the only way to solve the problem? Changing the LOCK_TIMEOUT? Since the publishing process is done by a auto-generated script, I do not think it is a good idea to change the script manually.

Edit:
Using the SqlServer profiler during a deploy I found out that something is changing the lock timeout to 5 seconds. This command does not exists in my publishing script, so I assume that an internal command or configuration is doing it. Going to Sql Server Management Studio and right-clicking on the server > properties > advanced > parallelism I found some configurations, but they do not seem to affect this automatic definition of 5 seconds.

SqlServer Trace

Best Answer

Looking at the MSDN documentation for sp_fulltext_database we see the following note:

Has no effect on full-text catalogs in SQL Server 2008 and later versions and is supported for backward compatibility only. sp_fulltext_database does not disable the Full-Text Engine for a given database. All user-created databases in SQL Server 20xx are always enabled for full-text indexing.

The "xx" in the "20xx" above changes based on what version of the documentation you are looking at, but starting with SQL Server 2008, that xx will be: "08", "12", or "16".

You are on SQL Server 2014, so I am questioning why EXECUTE sp_fulltext_database 'disable'; is even showing up in your SSDT-generated deploy scripts. I just did some testing and it seems that no matter what the project's "Target platform" is set to, the deploy script always generates lines for:

IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';

The "enable" or "disable" is controlled by the "Database Setting..." on the "Project Settings" tab of Project Properties, in the "Miscellaneous" tab.

The only way to get rid of these 2 lines is to uncheck the "Deploy database properties" option, under "Deployment Options" on the "Debug" tab of Project Properties. But if you uncheck that option, then it won't enforce any of the database properties. So:

  • If you are not using the SSDT deployment to enforce the database properties then go ahead and uncheck this option. If you haven't made any other changes since the last build, this change alone will not update the deploy script, in which case you need to do a "rebuild".
  • If you are using the SSDT deployment to enforce the database properties, then either remove those two lines manually or find a way to do it programmatically.

I don't know why there is a call to an unused system stored procedure outside of it probably being a low priority to remove since it doesn't break anything and most people aren't doing parallel deployments ;-).

Related Question