SQL Server Hangs when creating view

Question

In SQL Server 2014 when creating a new view SQL Server management studio hangs for about 10 minutes then comes up with the error...

failed to retrieve data for this request. An exception occurred while executing a transact-SQL statement or batch. Lock request timeout exceeded

We can then create and save a new view as normal. Any ideas why this is happening and how to solve it ?

Answer 1

Try unlocking the database before creating your view:

First see if there are any exiting connections:

SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID('[dbname]') 

If there are any open sessions, kill them using:

kill 52

Then use this to unlock the database:

USE [dbname]; 
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 

Now you can create the view.

After that, don't forget to change the database to multi user mode:

ALTER DATABASE [dbname]
SET MULTI_USER