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