Detach database / perform offline crash
I am currently in the process of detaching a development database on a production server. Since this is a production server, I don't want to restart the sql service. This is the worst case scenario.
Obviously I tried to disable it via SSMS. Told me there was an active connection and I turned it off. When he disconnected the second time, I was told that this is not possible since it is in use.
I tried EXEC sp_detach_db
'DB' with no luck.
I tried to get the database offline. This went on 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 connection indicator in disconnecting the database using SSMS.
The following results are returned:
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID ('DB') wizard
And now it takes 18 minutes:
ALTER DATABASE DB SET OFFLINE WITH ROLLBACK IMMEDIATE
I regularly restarted SMSS during all of this to make sure SSMS was not the culprit by blocking something invisible.
Isn't there a way to overdo it? The database schema is something I really like, but the data is wasted.
Hope there is some quick solution? :)
The DBA will try to reset the process today, but I would like to know how to fix this, just in case.
thank!
ps: I am using DTC ... maybe this can explain why my database was suddenly locked?
edit:
Now I do the following, which leads to endless execution of the final part. The first query even returns 0, so I suppose killing users doesn'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
source to share
SELECT DISTINCT req_transactionUOW FROM syslockinfo
KILL 'number_returned'
(one with process_id -2)
The cause was a DTC slightly annoying and completely blocking the database 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 occurs again.
I am posting it here as I am sure it will help some people who are experiencing the same problems.
source to share
Try to kill all connections before detaching the database, IE:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_KillUsers] Script Date: 08/18/2009 10:42:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_KillUsers]
@p_DBName SYSNAME = NULL
AS
/* Check Paramaters */
/* Check for a DB name */
IF (@p_DBName IS NULL)
BEGIN
PRINT 'You must supply a DB Name'
RETURN
END -- DB is NULL
IF (@p_DBName = 'master')
BEGIN
PRINT 'You cannot run this process against the master database!'
RETURN
END -- Master supplied
IF (@p_DBName = DB_NAME())
BEGIN
PRINT 'You cannot run this process against your connections database!'
RETURN
END -- your database supplied
SET NOCOUNT ON
/* Declare Variables */
DECLARE @v_spid INT,
@v_SQL NVARCHAR(255)
/* Declare the Table Cursor (Identity) */
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT spid
FROM master..sysprocesses (NOLOCK)
WHERE db_name(dbid) LIKE @p_DBName
OPEN c_Users
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @v_SQL = 'KILL ' + CONVERT(NVARCHAR, @v_spid)
-- PRINT @v_SQL
EXEC (@v_SQL)
END -- -2
FETCH NEXT FROM c_Users INTO @v_spid
END -- While
CLOSE c_Users
DEALLOCATE c_Users
It's a script to kill all user database connections, just pass in the database name and close them. Then you can try to detach the database. This script is the one I found a while ago and I cannot validate it as mine. I don't mean this as some kind of plug-in, I just don't have the source.
source to share