Lock could not be placed when publish from sstd sql project issue

Question

Issue appears when I try to publish from ssdt sql project to my local database.

(47,1): SQL72014: .Net SqlClient Data Provider: Msg 5061, Level 16, State 1, Line 5 ALTER DATABASE failed because a lock could not be placed on database '*'. Try again later.
(43,0): SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N'$(DatabaseName)')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET CONTAINMENT = PARTIAL
WITH ROLLBACK IMMEDIATE;
END

I tried many things:

• setup db in single user mode
• execute the same from sqlpackage

I have workaround - I generate script then remove lock and execute.

Anyone has the same issue and resolved it?

Answer 1

@seba stian the issue I expect is that the database was created outside of ssdt, and the database settings in the ssdt project don't match that of the deployed database.

Under the project properties on the "Project Settings" tab there is a "Database settings" section. On the Miscellaneous tab is the Containment option. Check that matches what is set for the deployed database.

Answer 2

I don't work with sqlpackage, so I don't know what it's up to, or why it generates that code. Or is it your intention to make the database partially contained?

In any case, I found that when testing that the statement will block if there is another process in the database. You could modify the above to read:

   IF EXISTS (SELECT 1  
   FROM [master].[dbo].[sysdatabases]  
   WHERE [name] = N'$(DatabaseName)')  
   BEGIN  
       ALTER DATABASE  [$(DatabaseName)] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE  
     
      ALTER DATABASE [$(DatabaseName)]  
      SET CONTAINMENT = PARTIAL  
      WITH ROLLBACK IMMEDIATE;  
   END  

Answer 3

Hi @seba stian ,
How about trying restarting your database may solve this error:

Firstly you can use the following command to find your sql SPID:
EXEC sp_who2

Then you can use the command to shut it down
KILL (SPID)
At last make the database online again
ALTER DATABASE [test] SET ONLINE
GO

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 4

Hi @seba stian ,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Answer 5

@Erland Sommarskog , ssdt project generates script (dacpac) and this should just work by publish of it. I can edit script, by just removing locking part and it works. It should just work from "Publish".
@PandaPan-MSFT , I killed all what I thought it is connected to db. As I also mentioned I also moved db into single user. It didn't help.