.

renaming database, kill database connections etc

 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 

 

 

 

 

3 Responses to this post.

  1. Posted by stephen on 21.12.10 at 9:06 am

    Worked like a bomb! thanks!!

  2. Posted by stephen on 21.12.10 at 9:06 am

    by the way – you can also play around with:

    ALTER DATABASE mydb SET OFFLINE WITH ROLLBACK IMMEDIATE

    and then run this to run database again.

    ALTER DATABASE mydb SET ONLINE WITH ROLLBACK IMMEDIATE

  3. Posted by pete on 21.12.10 at 9:06 am

    Thanks Stephen

What's your thoughts on this?

*

Protected by WP Anti Spam