Questions tagged [rollback]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
25 views

Killed SPID on xp_LogInfo not rolling back and growing tempdb

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
1answer
30 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 ...
0
votes
1answer
19 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 ...
0
votes
1answer
36 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
1answer
39 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 ...
0
votes
2answers
38 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 ...
0
votes
1answer
96 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
2answers
30 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
1answer
70 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
1answer
65 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]...
7
votes
4answers
1k 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
1answer
26 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 ...
1
vote
1answer
69 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 ...
0
votes
1answer
28 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 ...
2
votes
1answer
38 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) ...
0
votes
1answer
83 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
2answers
401 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 * ...
0
votes
2answers
766 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
0answers
20 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
1answer
125 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
1answer
217 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 ...
18
votes
2answers
2k 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 ...
1
vote
2answers
783 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
4answers
4k 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 ...
0
votes
2answers
67 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 ...
0
votes
1answer
705 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 ...
3
votes
2answers
94 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, ...
1
vote
1answer
655 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
1answer
686 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
2answers
297 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? ...
0
votes
2answers
5k 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 ...
4
votes
2answers
14k 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 ...
2
votes
1answer
867 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 ...
1
vote
0answers
101 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
3answers
110 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
2answers
923 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/...
1
vote
1answer
92 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
2answers
652 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
2answers
6k 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 ...
3
votes
2answers
14k 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
3answers
7k 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) ...
1
vote
1answer
2k 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.
0
votes
1answer
113 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 ...
6
votes
2answers
2k 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 ...
0
votes
2answers
709 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 ...
1
vote
2answers
968 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 ...
1
vote
1answer
3k 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 ...
2
votes
1answer
833 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 ...
3
votes
3answers
3k 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? ...
0
votes
1answer
328 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 ...