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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 ?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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

and let me know the results
Aneesh RetnakaranDatabase AdministratorCommented:
Also run 'sp_who2' to find which are the proceesses running, you need to watch for the columns
DBName ,Command ,ProgramName
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 ?
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 ..
mpdaltonAuthor Commented:
thanks for your help, the machine ended up finishing it's rollback before i took any further action (about 40 hours).

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.