• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1790
  • Last Modified:

SQL Server Transaction Rollback

i executed a delete query that ran for about 35 hours before the server rebooted itself.

now that it has restarted i imaging it is rolling back the delete transaction.

i need to know 2 things, can i stop the rollback ?
how do i know when the rollback is finished ?
0
mpdalton
Asked:
mpdalton
  • 5
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
mpdalton,
> now that it has restarted i imaging it is rolling back the delete transaction.

If your previous transaction (ur delete statement )  has already committed , then this rollback wont occur..
these two statements can be used to identify the opentransactions

select @@TRANCOUNT

DBCC OPENTRAN

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
mpdalton,
> how do i know when the rollback is finished ?

select @@TRANCOUNT    -- will give '0' if there are no open transactions, after finishing the rollback

same if you use the following too

DBCC OPENTRAN
0
 
mpdaltonAuthor Commented:
this was a rather large delete query that ran, deleting 40 million records from a table with about 150 million.

i can connect to the sql server using em or query analyser, but if i try to get a list of db's using the combobox it just locks up the user interface.

i can go into query analyser and run a command like
USE OKDB
select @@TRANCOUNT

and it works fine, but try
USE RollbackDB
select @@TRANCOUNT

and it just sits there.

the cpu is running at about 25%. could it be rolling back or is the db corrupted ?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Aneesh RetnakaranDatabase AdministratorCommented:
run
USE Master
GO
dbcc opentran('RollBackDB')

and let me know the results
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Also run 'sp_who2' to find which are the proceesses running, you need to watch for the columns
DBName ,Command ,ProgramName
0
 
mpdaltonAuthor Commented:
that is still running after 10 minutes.

i think what i might do is stop the sql server
move the data file and log file to another location
restart the server
create a new database with the same name
restore the last backup from before the delete

how does that sound ?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
yes, that is another option..
If you close that query analyzer window, then you will get a message like, there are some openatransactions.. , then you can select the appropriate option too ...

Also if you you can run the sl_who2 and you can kill the process id which is running in your database ..
0
 
mpdaltonAuthor Commented:
thanks for your help, the machine ended up finishing it's rollback before i took any further action (about 40 hours).

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now