Trying to rename a database or taking it offline?
Getting the following 2 errors:
Msg 5061, Level 16, State 1, Line xxx
ALTER DATABASE failed because a lock could not be placed on database 'dbname'. Try again later.
Msg 952, ...
Database is in transition ...
Here is the solution:
The following will find the processes that prevents you from modifying the database.
--run the next line only once
--sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = 'colhpasql002'
SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC sp_who') WHERE dbname = 'HIV_STI_DataWarehouse'
The Following will find and kill the locking processes on a database ... change 'HIV_STI_DataWarehouse' to your database name
USE master
GO
SET NOCOUNT ON
DECLARE @DatabaseName varchar(50)
DECLARE @DynamicSQL varchar(8000)
DECLARE @KillCount smallint
SET @KillCount=0
SET @DynamicSQL = ''
Set @DatabaseName = 'HIV_STI_DataWarehouse'
IF db_id(@DatabaseName) < 4
BEGIN
PRINT 'Target cannot be a system database'
RETURN
END
SELECT @DynamicSQL=coalesce(@DynamicSQL,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses
WHERE dbid=db_id(@DatabaseName)
PRINT(@DynamicSQL)
IF LEN(@DynamicSQL) > 0
BEGIN
EXEC(@DynamicSQL)
SELECT @KillCount = COUNT(1) FROM master..sysprocesses
WHERE dbid=db_id(@DatabaseName)
END
Subscribe