[SQL Server] Database is in transition. Try the statement later
/*
Suite à un arrêt brutal de la restauration de la base MaBAse, la base est devienue inaccessible
et impossible de la supprimer.
Ci-dessous les messages d'erreurs affichés :
Message d'erreur N° 1 :
-----------------------
Database is in transition. Try the statement later
Message d'erreur N° 2 :
-----------------------
ALTER DATABASE failed because a lock could not be placed on database Try again later
Ci-dessous la solution à suivre :
*/
select percent_complete,T3.text AS Requete,'DBCC INPUTBUFFER (' + cast(spid as varchar(25)) + ')',hostname,nt_username,db_name(T1.dbid) as nombase,'kill ' + CAST(spid AS VARCHAR(25)) AS 'killprocess'
,* from sysprocesses T1
LEFT JOIN sys.dm_exec_requests T2 on T2.session_id=T1.spid
CROSS APPLY sys.dm_exec_sql_text(T1.sql_handle) T3
where
T1.dbid in(
DB_ID('MaBase')
)
-- L'exécution de la requête affiche le message d'erreur ci-dessous
-- Database is in transition. Try the statement later
SELECT
GETDATE() as now,
DATEDIFF(SECOND, transaction_begin_time, GETDATE()) as tran_elapsed_time_seconds,
T2.session_id,
T5.text,
*
FROM
sys.dm_tran_active_transactions T1
INNER JOIN sys.dm_tran_session_transactions T2 ON T2.transaction_id = T1.transaction_id
LEFT OUTER JOIN sys.dm_exec_sessions T3 ON T2.session_id = T3.session_id
LEFT OUTER JOIN sys.dm_exec_connections T4 ON T4.session_id = T3.session_id
OUTER APPLY sys.dm_exec_sql_text(T4.most_recent_sql_handle) AS T5
WHERE
--nt_user_name like '%zouhaier%'
DB_NAME(database_id)='MaBase'
ORDER BY tran_elapsed_time_seconds DESC;
SELECT DISTINCT DB_NAME(resource_database_id),'kill ' + CAST(request_session_id AS NVARCHAR(25))
FROM sys.dm_tran_locks where resource_database_id=DB_ID('MaBAse')
--Lancer tous les kill qui ont été générés par la requête précédente
USE master;
GO
ALTER DATABASE MaBAse
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
/*
ALTER DATABASE MaBAse
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO
*/
DROP DATABASE MaBAse
GO