5

I am using SQL Server 2014 Express

It seems that restarting SQL Server when an event such as a Data Lock happens is not (always) such a good idea. http://www.sqlpassion.at/archive/2016/08/08/restarting-sql-server-always-a-good-idea/

I want to ask specifically about the problem that a SQL Server Restart will roll-back uncommitted transactions.

My colleague ran SELECT @@TRANCOUNT and got zero. Therefore he assumed that restarting the SQL Server would not result in any kind of roll-backs. Nevertheless, a position which was moved from one table to another, got moved back after the restart.

Are there any situations where roll-backs would happen even if SELECT @@TRANCOUNT would return zero?

  • There is a specific T-SQL command: SHUTDOWN which will gracefully shutdown the server, waiting for pending transactions in flight to commit. – Remus Rusanu May 23 '17 at 18:54
9

As per docs online:

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql

This is a connection level variable. If there were transactions open for connections other than the one your colleague ran his query on, there could have been open transactions.

0

I believe that counter will show the number of explicit transactions, so you may not be getting the results you are expecting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.