unable to restore db
-
Hi All,
Unable to restore/refresh a database. I was able to kill the sessions but still I unable to restore.
When I try to use below query, I get below output. A negative spid on that database.
select
db_name(l.resource_database_id) as dbname,
l.resource_type,
l.request_mode,
l.request_status,
l.request_session_id,
r.command,
r.status,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
request_sql_text = st.text,
s.program_name,
s.host_name,
s.login_name,
s.status,
s.nt_domain,
most_recent_sql_text = stc.text
from sys.dm_tran_locks l
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
left join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
left join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
where l.resource_database_id = db_id('mydbname')
order by request_session_id;
--https://dba.stackexchange.com/questions/57432/database-is-in-transition-error
What can be done ? Please help.
Cant take the database to single user mode as well. It is keep on running and eventually had to cancel single_user statement as well
USE [master]
GO
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
…. And I am seeing below output.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Query was cancelled by user.
Checked for negative spids and wasnt able to KILL it.
uSE Master;
GO
SELECT
DISTINCT(request_owner_guid) as UoW_Guid
FROM sys.dm_tran_locks
WHERE request_session_id =-2
GO
KILL '00000000-0000-0000-0000-000000000000'
Msg 6110, Level 16, State 1, Line 5
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.
Can restarting MSDTC service in services.msc will fix the issue? This is a dev server by the way.
Thanks,
Sam
- This topic was modified 4 years, 6 months ago by vsamantha35.
- This topic was modified 4 years, 6 months ago by vsamantha35.
-
Restarted the MSDTC service as it is a Development server. It fixed the orphan spid. Now the restore is in progress.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply