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?

gordon613
  • 205
  • 1
  • 8

2 Answers2

9

As per docs online:

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

https://learn.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.

George.Palacios
  • 5,590
  • 23
  • 48
0

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

Jeff A
  • 468
  • 1
  • 4
  • 11