Publishing SQL Data Tools 2012 project: Forces into Single User Mode

Question

I have a CLR Project that I'm trying to publish using Visual Studio. I had to change the project to a SQL Data Tools project, and now it's not publishing. Each time I try, I get a timeout error. When I take it step-by-step, I find this line of code hangs on my server.

IF EXISTS (
  SELECT 1
  FROM [master].[dbo].[sysdatabases]
  WHERE  [name] = N'fwDrawings')
BEGIN
  ALTER DATABASE [fwDrawings]
  SET READ_COMMITTED_SNAPSHOT OFF;
END

Basically, I know it's trying to force the server into single user mode when I try to publish this up. It's just to my staging server and not to a production server, but this is still a problem. I can't keep kicking everyone off the server and try and switch it to single user mode every time I want to update the CLR while I'm testing it's functionality. And I don't want to wait for a maintenance cycle or down-time to promote it up to production. Is there a way around this?

Answer 1

Presumably you have READ_COMMITTED_SNAPSHOT turned on for your database.

If this is the case, you need to change your Database project settings to match. Check "Read committed snapshot" transaction isolation, within the Operational tab in Database Settings for the project.

For me, this prevented the publish timing out, i.e. I can now publish successfully.

Answer 2

For a safer way to deploy to a server that's in use, try using a schema comparison instead.