question

bernard avatar image
bernard 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
transaction-logrecovery-mode
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image Shawn_Melton commented ·
Is there something wrong with just using `DBCC OPENTRAN` ?
0 Likes 0 ·
sjimmo avatar image sjimmo commented ·
The amount of information being retrieved, with more detail in the script. I can see the command being executed, and better make a decision as to whether to kill the process or let it continue.
0 Likes 0 ·

1 Answer

·
sjimmo avatar image
sjimmo answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.