asked
change recovery mode for sql server
Hi, i have trasnactionlog shipping running onmy Db. We have to do some big maintenance from time to time like big rebuilds etc. When we do this i like to cancel tsl and after we finished to start again. Lately i have a bi issue to put teh db back to simple recovery or to full recovery. It takes a long time to change the recovery mode, what i found out is that i have active transaction running and while i have an active transaction i can't change recovery mode. How can i find out what are the active transaction that block me to do change the recovery mode, never happened before, i have to stay up for hours in the middle of night to find the spot where there is no active Transaction when i can change it and it drives me nuts. I used sp_who2 but didn't help. This nonsense only started two weeks ago before that i had no issues with it. any ideas? thanks
Here's a script that I have used in the past. It was originally done by Paul Randall: SELECT [s_tst].[session_id], [s_es].[login_name] AS [Login Name], DB_NAME (s_tdt.database_id) AS [Database], [s_tdt].[database_transaction_begin_time] AS [Begin Time], [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes], [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd], [s_est].text AS [Last T-SQL Text], [s_eqp].[query_plan] AS [Last Plan] FROM sys.dm_tran_database_transactions [s_tdt] JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id] JOIN sys.[dm_exec_sessions] [s_es] ON [s_es].[session_id] = [s_tst].[session_id] JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id] LEFT OUTER JOIN sys.dm_exec_requests [s_er] ON [s_er].[session_id] = [s_tst].[session_id] CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est] OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp] ORDER BY [Begin Time] ASC; GO
19 People are following this question.
