Skip to main content

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
2k 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 ...
Ulukai's user avatar
  • 123
1 vote
0 answers
271 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 ...
Thomas Clowes's user avatar
0 votes
3 answers
358 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 ...
Harris Hilton's user avatar
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/...
gordon613's user avatar
  • 205
1 vote
1 answer
157 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)&...
Santhoshkumar KB's user avatar
4 votes
2 answers
1k 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 ...
Richard Herling's user avatar
1 vote
2 answers
10k 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 ...
user120047's user avatar
4 votes
2 answers
25k 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 ...
sparkymark75's user avatar
8 votes
3 answers
10k 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) ...
Manngo's user avatar
  • 2,849
1 vote
1 answer
4k views

Find history of rollbacks on SQL Server 2012 [duplicate]

Is there a way to find the history of any and all rollbacks that happened on a SQL Server database? Specifically, I would like to know the time and the user. Thanks.
NathanAck's user avatar
  • 111
0 votes
1 answer
224 views

Exclude procedure call from transaction rollback

How can I exclude a statement in a transaction rollback? Begin Insert into table a ... ... call proc_1() ... ... Rollback How can I rollback all the commands except/prevent proc_1() from rolling ...
zhenguang's user avatar
6 votes
2 answers
4k views

Tracking down a transaction rollback

I have a very busy database and I've been asked to look for transactions that rollback. I used SQL Server Profiler to monitor the TM: Rollback Tran completed event, which shows me that dozens of ...
paulH's user avatar
  • 1,444
0 votes
2 answers
899 views

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

Is this statement true: If a DB engine disallows rollback and always uses a redo log to enforce commits, then dirty-reads never happen (because all changes will be commited finally). If so, are ...
Cyker's user avatar
  • 125
1 vote
2 answers
2k views

Transaction Error in TRY CATCH block

The following code returns this message in case something goes wrong: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current ...
Magier's user avatar
  • 4,797
1 vote
1 answer
6k views

Why does a rollback of a CREATE INDEX statement take longer than expected?

Recently, I needed to apply a clustered index to a table. I first performed the operation in a Staging environment. The operation completed in about 20 minutes, so we planned for PROD to take ...
Clint's user avatar
  • 71
3 votes
1 answer
2k views

Can I avoid Rollback with drop table?

I had a very long query running that was causing everything to get stuck. (was running for around 24 hours before being killed) INSERT INTO tbl_new SELECT * FROM tbl_old So I killed it. Now I don't ...
Noam's user avatar
  • 979
3 votes
3 answers
5k views

Truncate temporary table commits transaction?

Is this correct that a "CREATE TEMPORARY TABLE IF NOT EXISTS.." does not commit current transaction (I like that), but a TRUNCATE TABLE, which is temporary, does implicitly commit any transaction? ...
Slawomir's user avatar
  • 453
0 votes
1 answer
2k views

Dependent Transaction in separate Database-Connections

I am quite new to database and I am in the following situation. I have two tables defining a 1:n relation, let's say Company and Employee which I access in two separated database connections in ...
user695652's user avatar
1 vote
1 answer
773 views

SQL Server Rollback nested Stored procedures and linked servers

I have read THIS StackOverflow article, and found it enlightening. However, I am having trouble assimilating that knowledge with my limited knowledge of transactions and linked servers. I am trying to ...
Jeff.Clark's user avatar
2 votes
1 answer
2k views

How costly are InnoDB rollbacks?

I recently heard a fellow co-worker mention that InnoDB rollbacks are costly and negatively impact performance. How true is this statement? To give a context, we use spring+hibernate and set @...
Amit Sharma's user avatar
5 votes
1 answer
1k views

During rollback, is lock escalation reversed

Generally, SQL Server will take locks during updates. It also supports Lock Escalation: Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing ...
Michael Green's user avatar
0 votes
1 answer
1k views

How to recover auto committed transaction in Oracle with normal privilage

I have updated the table & need to rollback that changes. I have tried Rollback work command but data still not rollback. I have also tried Flashback command but it is not working because "row ...
user avatar
3 votes
2 answers
458 views

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

As the title says, do I have read/write access to my entire database while it is rolling back a transaction? Or read/write access to only the tables that the transaction did not write to? What is the ...
Ngovkevin's user avatar
4 votes
0 answers
2k views

InnoDB crashes on trx rollback

I have InnoDB assertion failure during the trx rollback 160316 10:47:31 InnoDB: Rolling back trx with id 5ADBE4173, 1 rows to undo 160316 10:47:31 InnoDB: Assertion failure in thread ...
gevial's user avatar
  • 141
0 votes
1 answer
973 views

How to quantify rollback events in SQL Server between 2 dates

I need to quantify rollback events and find theirs reasons, without change the application code. There is any way to collect this data? The reason to check the rollback occurrences have need to ...
Edgard Leite's user avatar
1 vote
1 answer
249 views

Prepare MySQL for big UPDATE query on production server

Surprisingly I have not found anything relevant on this subject. What are best practices to prepare production MySQL server for a big update query? The question is broad but I'd like to know details ...
WooDzu's user avatar
  • 145
7 votes
1 answer
8k views

SQL Server Rollback being performed - how to stop it?

I have a query that takes 60 million rows, copies them in a new table, does some updates on them, then inserts them into another new table. It's all wrapped in a transaction. I thought this would be ...
Andy DesRosiers's user avatar
2 votes
1 answer
388 views

Report rollbacks to log file

There are several reasons for SQL transactions to rollback. I want to log all of them to the SQL Server Log File so that I can find information about them at a later time in the SQL Server Log File ...
Martin's user avatar
  • 187
6 votes
2 answers
3k views

What are all the ways an open transaction will rollback?

When a transaction is opened in SQL Server, what are all of the ways that it can be rolled back? We are currently working with a 3rd party application that is frequently showing open transactions ...
SomeGuy's user avatar
  • 2,013
4 votes
0 answers
257 views

Identifying table causing runaway rollback in MySQL 5.5 (InnoDB / Windows)

I have a large database (180 GB), running under Windows, with a runaway rollback that starts running as soon as the database is started up. I have started the database with innodb_force_recovery=3 ...
Joe Paul's user avatar
13 votes
2 answers
567 views

How are these two SQL Server rollbacks different?

In SQL Server 2008 R2, how are these two rollbacks different: Run an ALTER statement, for a few minutes, and then hit 'Cancel Executing'. It takes a few minutes to rollback completely. Run the same ...
ToC's user avatar
  • 565
-1 votes
1 answer
686 views

Mysql: How does a rollback operation be performed inside InnoDB

I would like to hear in simple words Please answer me in simple words to the question on the below scenario. ============== Engine: Innodb Trx1 : started and a DML is executed at 10:00 AM and rolled ...
Uday's user avatar
  • 814
1 vote
0 answers
54 views

Taking Backup of a database in mysql

I have database but somehow it has been deleted. Can i take the back-up of deleted database. Please Help. Thanks In Advance
visit2shobhit's user avatar
1 vote
1 answer
60 views

What transaction logs are for

I'm confused about tranasaction logs for SQL Server. I was under the impression they can be used to roll the database forward. For example say I made a software release on Sunday, I added three new ...
Ageis's user avatar
  • 199
6 votes
2 answers
24k views

Rollback Committed transaction in SQL Server

Is it possible to rollback a committed transaction? I have deleted all the data in a table. The delete statement is inside Begin Transaction and I have committed the transaction with a commit ...
user69701's user avatar
2 votes
2 answers
904 views

Keep local variable after rollback in mysql

I wish to keep local variable from stored procedure even if rollback occurres. In following procedure, i "register" numbers to users. The procedure takes user_id, value_from and value_to as arguments....
Zbynek's user avatar
  • 324
1 vote
0 answers
2k views

MySQL Transaction with rollback upon @@error

I have done several transactions with MySQL but mainly using php's libraries as the handle. Using php you would have something as: $pdo->beginTransaction(); $pdo->execute(' query1; ...
Mcloide's user avatar
  • 121
0 votes
1 answer
731 views

Copy a Large INNODB table in MySQL

I have an INNODB table that's > 93 million rows. A lot of the data is considered "temp" data and is governed by an "is_active" flag of 1/0. When a user updates a form, new data ...
Don's user avatar
  • 103
1 vote
2 answers
153 views

Maintain Up-To-Date Copy of Very Large Table if Clustering/Partitioning/Moving Operation Needs to be Killed/Rollback

I've appended the following information in a previous post but I asked the 'why' instead of the 'how'. So here's the question... SQL Server 2014, CU6. 4.5 TB database, Simple recovery. The most ...
GregSD's user avatar
  • 21
0 votes
2 answers
501 views

is it possible to rollback only a partition/subpartition?

Is it possible to create a savepoint and to rollback only a partition of a table? Lets assume I created a monthly partition and subpartitions with a group_id for table x. Now i run some stored ...
user avatar
0 votes
1 answer
2k views

MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

I have this stored procedure: START TRANSACTION; code_gen_loop: WHILE @Codes > 0 DO IF MOD( @Codes, 10000 ) = 0 THEN EXECUTE qry_status_stmt2; EXECUTE qry_status_stmt3; ...
Scott's user avatar
  • 169
27 votes
2 answers
4k views

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
sennett's user avatar
  • 370
13 votes
2 answers
18k views

ROLLBACK doesn't work after INSERT INTO newly created destination table

I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers). Before inserting contents of CSV-file into the mysql table I am first backing up the original customers ...
Dimitry K's user avatar
  • 235
7 votes
2 answers
10k views

Truncating and inserting in a safe transaction on MySQL

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table. I wanted to make sure that if any statement in the transaction failed, the data will be untouched. I can't have an empty or ...
Clarkey's user avatar
  • 425
2 votes
2 answers
5k views

Oracle OLEDB Connections hang in SQL Server: Can I restart Instance?

There is a killed/rolled back query on this instance that was originally attempting to execute a SELECT on a remote Oracle Server via OLEDB. This query was running for over two days before it finally ...
Craig Efrein's user avatar
  • 9,640
4 votes
1 answer
19k views

Shut Down MySQL during InnoDB rollback after crash

I am puzzled once again. We had a server crash last night during some huge queries. Upon restart in the morning MySQL went into recovery mode. I needed to shut down the MySQL service and it is ...
Dmitri Zyuzin's user avatar
2 votes
1 answer
3k views

Identifying if spid on SQL Server is in rollback after low disk space

On a test box I wanted to get an idea of how long adding an identity field would take to a table with roughly a half billion rows. The process ran for about 10 hours and we noticed it was getting ...
RThomas's user avatar
  • 3,396
0 votes
0 answers
3k views

Log full and an Active_transaction on database [duplicate]

I have limited access to this server eg I cannot SELECT * FROM sys.dm_tran_session_transactions for example I, perhaps foolishly, ran this query to insert some test data... begin transaction update ...
El Ronnoco's user avatar
1 vote
2 answers
6k views

How to stop ROLLBACK in progress?

How do I stop a rollback in progress. I killed a very long running INSERT INTO table1 SELECT * FROM table2 statement. I want to commit what's already there but I can't since it's rolling back. This ...
N..'s user avatar
  • 13
11 votes
1 answer
6k views

Investigating rollbacks in Postgres

A Postgres monitoring script, check_postgres.pl, is warning me that a large number of rollbacks are occurring on a database server. How can I investigate what queries are being rolled back? I have ...
aco's user avatar
  • 211