Questions tagged [rollback]

Ending a transaction by reverting to the changes made by the transaction.

Filter by
Sorted by
Tagged with
2 votes
1 answer
153 views

Query stuck on 0% ROLLBACK for 2 days

kill with statusonly returns: transaction rollback in progress. Estimated rollback completion: 0%. The query creates a temp table and runs on local server without using xp_cmdshell. CPU & reads ...
  • 23
0 votes
3 answers
92 views

Rollback a transaction using single thread/multi thread

Would it be possible to rollback a transaction using a single thread? I tried to find an answer to this question, but I couldn't find one that provided references. I read about rollback mechanisms. ...
0 votes
2 answers
212 views

How to find which transaction is being rolled back

My MariaDB server crashed earlier today and is still recovering. The error log shows InnoDB: To roll back: 1 transactions, 519345 rows From the log entries it appears that the rollback is going to ...
-1 votes
1 answer
728 views

Rollback of ALTER TABLE ALTER COLUMN command taking forever

I have a very large table that needed a schema change to one column - I needed to expand a datatype from varchar(50) to varchar(100). I expected this to happen quickly since the column datatype is ...
1 vote
1 answer
77 views

What could be the reason for running rollback after a commit?

I am trying to understand why there are so many rollback queries being issued by Jira and is equal to the number of commits. MySQL log reveals the following: 2021-04-29T14:55:58.364468Z 42059 ...
  • 135
13 votes
1 answer
2k views

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

This is the SQL Server's document talking about the ROLLBACK statement. On that page it states that the syntax of it is as follows: ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @...
0 votes
0 answers
132 views

How to find reason for rollback during rollback works in Oracle?

Working on Oracle 12.c, I have a situation, that a big transaction performs a rollback, which takes several hours. I see the status of the session e.g. in V$SESSION.MESSAGE "Transaction Rollback: ...
1 vote
1 answer
367 views

MariaDB InnoDB does not rollback on timeout

I have the ff. sample queries in this sequence: START TRANSACTION UPDATE INSERT COMMIT Query #2 is timing out but I noticed that query #3 is not rolled back. I am also trying to manually rollback by ...
  • 159
0 votes
1 answer
479 views

Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628)

I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say ...
0 votes
1 answer
302 views

Killed SPID on xp_LogInfo not rolling back and growing tempdb [duplicate]

I received a notification that the space on the drive my tempdb sits on was running low, when checking the running process it pointed to a server agent task. I checked the scheduled tasks and we had ...
0 votes
1 answer
72 views

Best practices for allowing rollback of individual customers on a shared database

Imagine having multiple customers (as an example) on a shared database, and needing a backup system so that a single customer can be rolled back without affecting the other customers. - It's not ...
  • 3
0 votes
1 answer
252 views

Com_commit and Com_rollback count are almost same. What doest it mean?

SHOW GLOBAL STATUS has Com_* variables: The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. When I compare Com_commit to Com_rollback, both are ...
  • 135
0 votes
1 answer
239 views

Is it possible to make a trigger that does not allow INSERT?

I'm currently learning how to implement sqlite commands through C code and I've been trying to figure out the best way to ensure that no data can be inserted to a table (which will only happen during ...
0 votes
1 answer
105 views

Install old Cumulative Update SQL Server 2016 Standard

We were running on SQL Server 2016 Standard Edition 2016 SP2-CU4 on our ALWAYS-ON HA cluster. Due to some recent issues one of our nodes got corrupted and we had to replace it with a new node. Since ...
  • 43
1 vote
2 answers
62 views

Fast Backup and Restore of Growing Databases

I work in Tech Support and the some of the products I support are heavily database dependent (PostgreSQL and MySQL, but most of the big ones are MySQL). Before we do an upgrade, we always take a full ...
  • 136
0 votes
1 answer
1k views

What happens if transaction rollback fails?

So failure while committing leads to rollback. But what if rollback also fails due to some reasons and the database has changed due to partial commitment. How is the database restored to its original ...
0 votes
2 answers
422 views

MySQL- Best practices for using rollback [closed]

What is the best practice / recommended way of using ROLLBACK operation, if any DML operation fails ? Any ROLLBACK, COMMIT mechanism ? FYI: I'm using InnoDB tables which complies with Atomicity.
0 votes
1 answer
785 views

syntax error at or near TO (ROLLBACK TO SAVEPOINT)

The following sql is a part from a FUNCTION ,i want to rollback to rbk SAVEPOINT : for ptrEnt in entite loop -- positionnement du point de retour pour une entité savepoint rbk; -...
2 votes
1 answer
2k views

ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER

I give an example to show my problem. I create a table as the following: CREATE TABLE a ( id INT ) I create a AFTER INSERT trigger to don't allow to insert id=1 into table "a" CREATE TRIGGER [dbo]...
8 votes
4 answers
2k views

Delete statement not responding to Kill session command

Four days ago a user ran the command below on a table with 400,000,000 rows. It's still running and the log file is increasing in size. delete from [table-name] This table does have a foreign key ...
1 vote
1 answer
250 views

Stop rollback of anonymous DO code block?

I'm executing some code through this anonymous DO code feature. I am observing that whenever it fails, all changes made are discarded and the database is rolled back to a state as it was before it ...
  • 437
2 votes
1 answer
637 views

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? DECLARE @LoopId INT = 1; WHILE (@LoopId < 100) BEGIN INSERT INTO Table2 ...
  • 26.5k
0 votes
1 answer
35 views

Cancel Rollback and accept data loss

I have a SQL instance (2012 Standard Edition) which consumes all CPU resources because of a rollback-process. The instance has been restarted, so the database is currently In Recovery. I cannot see ...
  • 342
2 votes
1 answer
321 views

Rollback is not working if the storedproce call another storedproc

Is it intended behavior that if I call a stored procedure inside a stored procedure will automatically commit? example: pseudo procedure proc1 create procedure proc1(in var1 int, out var2 int) ...
  • 453
0 votes
1 answer
341 views

Implementation of Rollback in PostgreSQL

How is the rollback command implemented within the PostgreSQL database? I wanted to know the implementation details and what exactly happens underneath when a rollback is executed, i.e. state of ...
3 votes
2 answers
995 views

Rolling back truncate table

I have a stored procedure that does the following: BEGIN TRANSACTION -- Code to delete updated records from production (dbo) table DELETE FROM [dbo].[factMyTable] WHERE exists (SELECT * ...
  • 681
0 votes
2 answers
3k views

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION [closed]

Below is an excerpt of an 800 line stored procedure that is failing with this error now. I say now because this is not new code. This script is scheduled to run every few minutes and has worked for a ...
0 votes
0 answers
27 views

Is it possible to create an SQL transaction that rollbacks on syntax errors?

In may day-to-day job, I often have to create SQL files containing minor changes (no DDL operation usually) in order to migrate those to production databases easily. The problem is, even with syntax ...
4 votes
1 answer
131 views

Inner procedure is rolled back but outer procedure is still committed

I have an outer procedure which calls 5 different inner procedures something like Parent procedure(Tran/try/catch) Inner sp1 (Tran/try/catch) ' ' Inner sp5 (Tran/try/catch) But in this case, if one ...
2 votes
1 answer
330 views

Transaction rollback hanging from past two months(MSSQL 2016)

I executed a query using SQLCMD on 2019-01-30, and the query caused ldf file to bloat exponentially and it became 2TB in size, then my MSSQL started rollback of transaction at 2019-02-01. I used KILL ...
  • 131
18 votes
2 answers
3k views

Disk space full during insert, what happens?

Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb ...
2 votes
2 answers
4k views

How a Transaction is atomic?

I am really confused as to how to digest the fact that a transaction is atomic. If a transaction is set of "queries" then how will it be atomic. I am relating the word "queries" to a general SQL query....
1 vote
4 answers
7k views

how to test query

I would like to run a query but just to test it and have the possibility to go back to previous state I saw rollback transaction. do I have just to run this command before ? delete * from myTables ...
  • 13
0 votes
2 answers
209 views

Keep a history of transactions for easy rollback

Is there a database (either SQL or NoSQL) which allows you to keep a history or log of all past transactions so that you can easily rollback to any given point in time (similar to how git manages a ...
  • 125
1 vote
1 answer
1k views

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

I have a script to restore a database from a backup. Prior to the restore command, it has: set single_user with rollback immediate The script seemed to be hanging here. I used sp_who2 to find out a ...
4 votes
2 answers
181 views

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

I have a database built on SQL Server Application-1 [Legacy App]: Written in C#, and runs transactions on the above database. Application-2 [New application being developed]: Written in Python, ...
2 votes
1 answer
1k views

What will happen with MySQL transaction if one of the queries fails?

I have 500 independent insert queries. I put them in a transaction to run faster. I am runing queries with PHP. What will happen if one of queries fails? Will the transaction be rolled back ...
1 vote
1 answer
751 views

mssql revert changes made today without backup

A bug in an app made some changes now i need to rollback without a backup Can i use the logfile for this ? Is 'ROLLBACK' another option ? INFO: -My Recovery model is set to simple -AutoCommit hasnt ...
2 votes
2 answers
653 views

Cancelling the rebuilding of a partition with compression

Trying to enable compression on a large table in production but we are now out of time (been running for 6.5 hours already). What is the impact of cancelling the operation and letting it roll back? ...
  • 532
0 votes
2 answers
10k views

Why am I getting a syntax error when trying to create a stored procedure in MySQL? [closed]

I would like to use a transaction to call upon a stored procedure on many rows. If it fails on a single row, I want to roll back everything. Otherwise I want to commit. I have the following so far ...
  • 1
4 votes
2 answers
20k views

ROLLBACK after COMMIT

I have multiple database sessions from which one has been blocking others because of a frozen application. There are transactions running on multiple sessions. I need to kill the head blocker because ...
  • 177
2 votes
1 answer
1k views

Set recovery mode in mysql database

I've been reading about different recovery modes you can set on sql databases, for example here: https://www.mssqltips.com/sqlservertutorial/2/sql-server-recovery-models/ So I've been trying to find ...
  • 123
1 vote
0 answers
198 views

mariadb won't restart after storage space induced crash

I have a large mariadb instance running (400 gb or so). Due to running out of storage space it crashed. I went ahead and resized my volume (I am using AWS), and went to restart the service service ...
0 votes
3 answers
210 views

after executing a back up cmd (via query) how to kill it

I have a big database (150 GB) that needs a back up and I don't have space in current machine, so I am pointing it to the different box which has space. After executing the cmd, it is executing the ...
5 votes
2 answers
1k views

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

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/...
  • 205
1 vote
1 answer
144 views

Full backup of Simple recovery model - how much log is backed up if there is an open transaction

I have a situation right now. There is a database in SIMPLE recovery model. Note: 1. Compression is not enabled which will be enabled pretty soon. 2. SQL Server 2012 SP3 BackupSize: 8GB (Usually)&...
4 votes
2 answers
883 views

ALTER DATABASE MODIFY FILEGROUP [filegroup] AUTOGROW_ALL_FILES

Does anyone know of a way to accomplish setting a filegroup to AUTOGROW_ALL_FILES without setting the database to single user mode? I frequently add new filegroups/files to a 24/7 production database ...
1 vote
2 answers
8k views

Mysql rollback and triggers

How does mysql triggers and rollback working together? I need to know how this is working together? I can rollback if i proceed with more than one sql statement because it is in same query but it ...
4 votes
2 answers
21k views

Resume a suspended task

I was applying an index to a database with archived data. I knew this would take quite a while. It was at 14 hours running when I left it last night. I came back to it this morning and enterprise ...
6 votes
3 answers
8k views

SQL Transactions: When do I Roll Back?

This is probably a simplistic question, but I would like to check on the mechanics of rolling back a transaction. As far as I understand, the steps are as follows: BEGIN TRANSACTION (or equivalent) ...
  • 2,377