4

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

  • The publish process is setting the timeout to something other than 0 (wait forever). What are you using to do the publish, TFS? – Max Vernon Nov 18 '15 at 14:20
  • Yes... You are correct. Checking the publishing script I just saw that he can execute the command 'SET LOCK_TIMEOUT'. We are using MSBuild to do the publish. Building a sqlproj passing Publish as target, the MSBuild generates the script and execute it. – Phellipe Silva Nov 18 '15 at 14:53
  • Phellipe, are you actually using Full Text Search (FTS) in any of these databases? – Solomon Rutzky Nov 18 '15 at 15:29
  • Hi @srutzky, the error is raised when executing this command: EXECUTE sp_fulltext_database 'disable';. So, I suppose the script is trying to disable the FTS. – Phellipe Silva Nov 18 '15 at 17:47
  • Great, that is a 2nd reason why this should be easy to fix without needing to mess with LOCK_TIMEOUT :-) – Solomon Rutzky Nov 18 '15 at 17:48
3

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 ;-).

  • Hi @srutzky, by unchecking the "Deploy database properties" and generating the script, the command IF fulltextserviceproperty(N'IsFulltextInstalled') = 1 EXECUTE sp_fulltext_database 'enable'; still shows up. I also noticed that by doing it, my sqlproj does not change. I am using Visual Studio 2015. PS: Changing the enable/disable property in the "Miscellaneous" tab worked, but this case does not work for me since the command is still there =( – Phellipe Silva Nov 18 '15 at 21:16
  • @PhellipeSilva Correct, the "enabled" vs "disabled" is reflected properly, but the call to that system stored proc is always there. And I forgot to mention that I also ran into the same behavior of unchecking "deploy properties" and it having no noticeable effect. You need to do a "rebuild" of the project or possibly the solution. Or I suppose any code change might also trigger a refresh of those settings. I went with the "rebuild solution" and that always picked up the change immediately. – Solomon Rutzky Nov 18 '15 at 21:53
  • 1
    @PhellipeSilva Where is "there" in the "it is still there"? Does your build process use a Publish Profile that maybe it is getting the option for "deploy database properties from"? The "Script Database Options" option is saved (for Visual Studio) in the .sqlproj.user file, but that is probably not being referenced by the build process. However, you might be able to add the <ScriptDatabaseOptions>False</ScriptDatabaseOptions> to the main .sqlproj file. – Solomon Rutzky Nov 18 '15 at 22:50
  • 1
    @PhellipeSilva Well, I think we found the real culprit ;-). You are recreating the DB each time? That is not very efficient, and certainly would cause contention when many are run in parallel because the conflict is with the system meta-data for managing databases. You should probably also uncheck the "always recreate database" option (in the same section as "deploy database properties"). – Solomon Rutzky Nov 20 '15 at 20:31
  • 1
    All right! I will give it a try. Thank you very much again. – Phellipe Silva Nov 20 '15 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.