Sample Header Ad - 728x90

Stop rolling back transaction by committing them

0 votes
1 answer
100 views
I have a SQL Server process that runs annually, creating around 400 million records in several tables. The process operates in a loop, committing every 20,000 records for each of the tables. I recently discovered that someone changed the script and put a begin tran in the initial lines, which was only committed in the last lines. And there was an error in the process. Now the database is rolling back each of the 20 thousand lines that were committed in the nested transactions. Is there any way to prevent this rollback? How to identify the SPID of the transaction and commit it before the roll back reaches it? There were only about 50 million lines left when the error occurred due to lack of space in *tempdb*. We estimate that it will take around 20 hours to roll back everything in four different tables.
Asked by Jair Pedro Assis (1 rep)
Jan 29, 2025, 11:55 PM
Last activity: Jan 30, 2025, 10:54 PM