March 31, 2017 at 3:01 am
11.0.3381.0
SET SINGLE_USER WITH ROLLBACK IMMEDIATE hangs indefinitely
According to sp_who2's LastBatch column it hasn't done anything since 6th February (dm_exec_requests says it was a select)
While all other databases have the sid of 0x01 in sys.sysdatabases, this database has 0xAD512E3928C3CA418EB9FE22BA3E99DB
Session not showing in sp_whoisactive
dm_tran_locks.request_session_id: 141
dm_tran_locks.request_owner_type: SHARED_TRANSACTION_WORKSPACE
dm_tran_locks.request_mode: S
dm_tran_locks.request_status: GRANT
dm_tran_locks.lock_owner_address: 0x000000046A4BC7C0 -- but it doesn't exist in dm_os_waiting_tasks
dm_exec_requests.command: null
dm_exec_requests.status: null
dm_exec_requests.blocking_session_id: null
dm_exec_requests.wait_type: null
kill 141 does nothing
sysprocesses.kpid is 0
sp_lock shows ObjId=0, IndId=0, Type=DB, Resource=null, Mode=S, Status=GRANT
dm_os_waiting_tasks has nothing for the session
As kpid is 0, any ideas other than instance restart/failover?
March 31, 2017 at 2:26 pm
Sue
March 31, 2017 at 3:23 pm
Sue_H - Friday, March 31, 2017 2:26 PMDoes kill 141 with statusonly give you any information?
Anything in sys.dm_tran_session_transactions or dbcc opentran() ?Sue
SPID 141: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
But I don't think it is true as the last activity involving that session (besides the kill command) was almost 2 months ago and a normal select statement.
opentran = No active open transactions.
sys.dm_tran_session_transactions = 6 read-only worktables from December 2015 😀 transaction_state's all 0 (uninitialised), uow's null
March 31, 2017 at 4:18 pm
Dird - Friday, March 31, 2017 3:23 PMSue_H - Friday, March 31, 2017 2:26 PMDoes kill 141 with statusonly give you any information?
Anything in sys.dm_tran_session_transactions or dbcc opentran() ?Sue
SPID 141: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
But I don't think it is true as the last activity involving that session (besides the kill command) was almost 2 months ago and a normal select statement.opentran = No active open transactions.
sys.dm_tran_session_transactions = 6 read-only worktables from December 2015 😀 transaction_state's all 0 (uninitialised), uow's null
But if the process is seen as having a rollback in process, you can't kill it. I think that's why kill did nothing.
Bummer that uow is null - so I can tell you know what I was thinking with that one.
With the transaction state being 0, it looks like something may have caused it to die before starting the whole process to change to single_user.
Only other thing I can think of to try would be to check sys.dm_exec_sessions, sys.dm_exec_connections to see if you can get information on who and where it was initiated from.Maybe you can shut off that end of things.
Sue
April 1, 2017 at 5:32 pm
Those session transactions from December 2015 make me go :pinch:
Where was the single_user initiated from (ssms or some other app)? This wreaks of an orphand transaction from a connection pooling type of connection.
With the session still trying to rollback, are any other transactions getting blocked by it by chance?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2017 at 6:15 pm
SQLRNNR - Saturday, April 1, 2017 5:32 PMWhere was the single_user initiated from (ssms or some other app)? This wreaks of an orphand transaction from a connection pooling type of connection.
With the session still trying to rollback, are any other transactions getting blocked by it by chance?
Nothing is being blocked, that's the only user using that db (and preventing its dropping). Initially single_user would have been attempted through the app (sql server auth), I then tried via SSMS. I guess I'll just leave it there until patching as it's not really causing a problem as the application is now using a slightly different db name while the old remains there. Just find it a bit strange
April 1, 2017 at 7:38 pm
Dird - Saturday, April 1, 2017 6:15 PMSQLRNNR - Saturday, April 1, 2017 5:32 PMWhere was the single_user initiated from (ssms or some other app)? This wreaks of an orphand transaction from a connection pooling type of connection.
With the session still trying to rollback, are any other transactions getting blocked by it by chance?Nothing is being blocked, that's the only user using that db (and preventing its dropping). Initially single_user would have been attempted through the app (sql server auth), I then tried via SSMS. I guess I'll just leave it there until patching as it's not really causing a problem as the application is now using a slightly different db name while the old remains there. Just find it a bit strange
I think that is a good route to take.
The app performing the single_user action is a key piece of information. I bet the pool with the connection for that spid went wonky and you ended up with an orphaned transaction.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2017 at 1:51 am
SQLRNNR - Saturday, April 1, 2017 7:38 PMI think that is a good route to take.The app performing the single_user action is a key piece of information. I bet the pool with the connection for that spid went wonky and you ended up with an orphaned transaction.
Oh it's not working with single user normally, it was trying to drop/recreate the application databases. For some reason it does that as part of the application upgrade -_-
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply