Wednesday 26 November 2014

Taking a SQL server database offline fails or killing a database connection

In most cases, when the operation for taking a database offline; this is attributed to connections established to this database. The apparent solution is to close the locking connections, the below is how to force them closed.


Use [master]
GO

sp_WHO 
GO

spid | ecid | status   | loginame | hostname | blk      | dbname | cmd 
58    | 0     | sleeping| user1        | user1PC   | NULL| MyDB   |LOG WRITER      


KILL 58
GO

Command(s) completed successfully.





P.S. 
- sp_who2 provides even more details about the current connections.
- No need to mention that you must have the appropriate rights on the master database to perform this commands.

No comments:

Post a Comment