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

In the project that you are building and deploying do you mean to make the database Partially Contained?

You have to have exclusive access to the database to set the PARTIAL containment on.

What about using

ALTER DATABASE [databasename] SET CONTAINMENT = PARTIAL WITH ROLLBACK IMMEDIATE