32 votes
Accepted

Is this an error in SQL Server's documentation for ROLLBACK?

The ROLLBACK TRANSACTION documentation is correct, albeit not clear without additional context. ROLLBACK is shorthand for the similar ROLLBACK WORK statement rather than ROLLBACK TRANSACTION. Below is ...
Dan Guzman's user avatar
  • 28.1k
17 votes
Accepted

Disk space full during insert, what happens?

The actual query wasn't executed in an explicit transaction though, can that explain the existence of the target table? Yes, exactly so. If you do a simple select into outside of an explicit ...
sepupic's user avatar
  • 10.8k
16 votes
Accepted

mssql revert changes made today without backup

My Recovery model is set to simple In short, there is nothing you can do but learn from this lesson that important databases should use the full recovery model (along with proper restore plans). ...
Sean Gallardy's user avatar
16 votes

Disk space full during insert, what happens?

You're correct, the SELECT...INTO command is not atomic. This wasn't documented at the time of the original post, but is now called out specifically on the SELECT - INTO Clause (Transact-SQL) page on ...
Josh Darnell's user avatar
  • 29.2k
13 votes
Accepted

ROLLBACK after COMMIT

Generally, a transaction is exactly one of committed rolled back Committed transactions are never rolled back. It's how all RDBMS operate, on ACID principles Now, there are some different cases ...
gbn's user avatar
  • 69.8k
11 votes
Accepted

During rollback, is lock escalation reversed

SQL Server does not de-escalate the lock. I investigated using a "Numbers" table with 100,000 rows. Empirically, updating 5,000 rows produced a corresponding number of RID locks in sys.dm_tran_locks. ...
Michael Green's user avatar
11 votes

Can I change table structure in a transaction and then roll it back if there is an error?

Yes, it is possible to use many ALTER TABLE statements in one transaction with ROLLBACK and COMMIT. Here is a scaffold for your script (following MS guidelines with improvements): BEGIN TRANSACTION ...
maxkoryukov's user avatar
9 votes
Accepted

Why did a SQL Server Restart roll-back seemingly committed transactions?

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 ...
George.Palacios's user avatar
9 votes
Accepted

Delete statement not responding to Kill session command

I am at a loss as to what to do about this query to get it to rollback and also just understanding what is going on, can anyone suggest what I can look at? DELETE FROM [Some400MRowTable] is ...
David Browne - Microsoft's user avatar
7 votes

Resume a suspended task

If you kill it, you'll have to roll your progress so far back, which could take a very long time. Rollbacks are single threaded. You could be waiting much more than 14 hours for that to happen. You ...
Erik Darling's user avatar
  • 40.6k
7 votes
Accepted

SQL Server Rollback being performed - how to stop it?

@MaxVernon already gave all the info you need. Just some addition from my side. The bigger the transaction the harder the (pre)calculation for the SQL Server. That's why your remaining time in ...
RayofCommand's user avatar
  • 1,353
7 votes
Accepted

If I kill a looping INSERT query, will it rollback all inserts since the loop started or only the last iteration?

Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping? If the session has started a transaction before the loop, then all statements will be ...
David Browne - Microsoft's user avatar
7 votes

Delete statement not responding to Kill session command

Rollbacks are single threaded, so it will take much longer, although 4 days seems long, we have no idea how long the original delete would have taken. From Jes Schultz Borland (link): If the ...
Amar L's user avatar
  • 111
6 votes

Can independent applications cause roll back to each other's transactions?

I want to understand if there is a possibility that transaction being run via one application can affect the ones from an independent other application. Directly, no. Each transaction is by ...
LowlyDBA - John M's user avatar
5 votes

SQL Server - What access do I have during a transaction rollback?

The answer really depends largely on your isolation level, what type of locks are held by the transaction being rolled back, and what your non-rollback session is trying to do. Step 1) What is ...
AMtwo's user avatar
  • 16.1k
5 votes

Resume a suspended task

kill 61 with statusonly. SQL tells me that,Status report cannot be obtained. Rollback operation for Process ID 61 is not in progress It seems Kill With statusonly reports ,only when the session is ...
TheGameiswar's user avatar
  • 2,989
4 votes
Accepted

How costly are InnoDB rollbacks?

InnoDB uses "optimistic" MVCC - when you do some changes, it applies them to the real tables and may even write them to disk. Commit only ensures that all the writes were finished and makes the ...
jkavalik's user avatar
  • 5,080
4 votes

Inner procedure is rolled back but outer procedure is still committed

The problem you're facing is down to how the TRY...CATCH construct handles errors - it consumes them, eats them, and spits nothing back out except for a 0 return code. This is evidenced by a very ...
George.Palacios's user avatar
4 votes

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Some errors automatically rollback the transaction. See generally Error and Transaction Handling in SQL Server And if your error already rolled back the transaction, the first line of your catch ...
David Browne - Microsoft's user avatar
4 votes
Accepted

Rolling back truncate table

The insertion of records from the RAW table into the dbo table got rolled back; BUT The truncation of the RAW table did not roll back. No, there was no rollback at all, and here is the repro. ...
sepupic's user avatar
  • 10.8k
4 votes

Implementation of Rollback in PostgreSQL

Apart from some side tasks like releasing locks, deleting files and similar, the main activity is to set the two bits that correspond to the transaction in the commit log (in pg_xact) to “...
Laurenz Albe's user avatar
  • 50.5k
4 votes

Delete statement not responding to Kill session command

So the delete ran for only "a few hours" before you killed it, and now the "rollback" has been running for 4 days? That's well past the time I would normally expect, so here's what I would recommend: ...
BradC's user avatar
  • 9,954
3 votes

Truncate temporary table commits transaction?

You can use DROP TEMPORARY TABLE instead of TRUNCATE, then CREATE TEMPORARY TABLE(...) again, it will not commit the transaction but you gain the speed of TRUNCATE statement. "CREATE TABLE and DROP ...
kroczu's user avatar
  • 31
3 votes

Dependent Transaction in separate Database-Connections

If you really need to split data in multiple databases, you will need a PREPARED TRANSACTION[1] (A.K.A Two fase commit). Your process will be much simpler if you put all tables in the same database. ...
Sebastian Webber's user avatar
3 votes

Is dirty-read a problem in an always-commit database?

If you still allow transactions that span multiple statements, you would still potentially have dirty reads. Imagine that I have a simple banking application where session 1 is trying to transfer $...
Justin Cave's user avatar
  • 20.3k
3 votes
Accepted

Cancelling the rebuilding of a partition with compression

Rollback could be 2x, 4x, etc., since rollback is single-threaded even for operations that were parallel. My advice would be to let it finish. What's your alternative? Not do it ever? If you can't ...
Aaron Bertrand's user avatar
3 votes

Can independent applications cause roll back to each other's transactions?

If your code does not affect Isolation Level of your queries, non of your apps can make queries to Rollback. They can only Suspend each other. Use a SQL Server Profiler to track your app and see ...
El.Hum's user avatar
  • 182
3 votes

How to immediately terminate a SPID with a very long rollback time estimated?

Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the ...
Aaron Bertrand's user avatar
3 votes

How a Transaction is atomic?

A transaction is atomic when it's encapsulated like this: BEGIN TRAN UPDATE dbo.Users SET Reputation = Reputation + 1 WHERE Id = 26837; UPDATE dbo.Posts SET Score = Score + 1 WHERE Id = 227563; ...
Brent Ozar's user avatar
  • 42.9k
3 votes
Accepted

Rollback a transaction using single thread/multi thread

Rollback are mostly single threaded (this is why rollback could take way longer then the transaction you are rollbacking). https://www.brentozar.com/archive/2014/03/happens-issue-kill/ You cannot &...
Dominique Boucher's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible