I'm currently in the process of detaching a development database on the production server. Since this is a production server I don't want to restart the sql service. That is the worst case scenario.
Obviously I tried detaching it through SSMS. Told me there was an active connection and I disconnected it. When detaching the second time it told me that was impossible since it was in use.
I tried EXEC sp_detach_db 'DB' with no luck.
I tried getting the database offline. That ran for about 15 minutes when I got bored and turned it off.
Anyway, I tried everything ... I made sure all connections were killed using the connections indicator in detach database using SSMS.
The following returned 0 results:
USE master SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('DB')
And the following is running for 18 minutes now:
ALTER DATABASE DB SET OFFLINE WITH ROLLBACK IMMEDIATE
I did restart SMSS regularly during all this to make sure SSMS wasn't the culprit by locking something invisibly.
Isn't there a way to brute force it? The database schema is something I'm pretty fond of but the data is expendable.
Hopefully there is some sort of a quick fix? :)
The DBA will try to reset the process tonight but I'd like to know the fix for this just in case.
Thx!
ps: I'm using DTC ... so perhaps this might explain why my database got locked up all of a sudden?
edit:
I'm now doing the following which results in an infinite execution of the final part. The first query even returns 0, so I suppose the killing of the users won't even matter.
USE [master] GO
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('Database')
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_KillUsers] @p_DBName = 'Database'
SELECT 'Return Value' = @return_value
GO
ALTER DATABASE Database SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
SELECT DISTINCT req_transactionUOW FROM syslockinfo
KILL 'number_returned' (the one(s) with process_id -2)
The cause was DTC being a little bit annoying and locking up the database completely with a failed transaction. Now I would like to know the reason why this happened. But at least it gives me the ability to reset the broken transactions when the problem re-occurs.
I'm posting it here since I'm sure it'll help some people who are experiencing the same issues.