SQL database in suspect or emergency mode

If you have a database that is in suspect or emergency mode do this to get it online:

ALTER DATABASE XXX SET ONLINE

If this work is’s all fine, else you have to kill all active connections with this:

select
t1.session_id,
t1.start_time as request_start_time,
db_name(t1.database_id) as DatabaseName,
t1.reads,
t1.writes,
t1.logical_reads,
t1.logical_reads*8/1024/1024 as logical_reads_GB,
t1.cpu_time as cpu,
t1.total_elapsed_time,
t1.wait_time,
t1.wait_type,
t1.wait_resource,
t1.open_transaction_count,
t1.blocking_session_id,
SUBSTRING(text, (statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END – statement_start_offset)/2) + 1) AS statement_text,
text as full_statement_text,
login_time,
program_name,
login_name,
client_net_address,
query_plan
from
sys.dm_exec_requests t1
outer apply sys.dm_exec_sql_text(sql_handle) t2
outer apply sys.dm_exec_query_plan(t1.plan_handle)
inner join sys.dm_exec_sessions t3 on t1.session_id = t3.session_id
inner join sys.dm_exec_connections t4 on t1.session_id = t4.session_id
where t1.session_id > 50 and t1.session_id <> @@spid

When all connections is terminatet do this:

ALTER DATABASE XXX SET EMERGENCY
ALTER DATABASE XXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (XXX, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE XXX SET ONLINE
ALTER DATABASE XXX SET MULTI_USER

To see the status on this you this command:

SELECT
session_id ,
request_id ,
percent_complete ,
estimated_completion_time ,
DATEADD(ms,estimated_completion_time,GETDATE()) AS EstimatedEndTime,
start_time ,
status ,
command
FROM sys.dm_exec_requests

 

Leave a Reply