SQL Server Rollback – Why Did a SQL Server Restart Roll-Back Seemingly Committed Transactions?

rollbacksql server

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?

Best Answer

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.

Related Question