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 ?
LVL 3
mpdaltonAsked:
Who is Participating?
 
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
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.