Force SQL Server Database offline

I had a user kill a process that is now doing a rollback that is locking up
the server. Is there any way to force that databse offline? I tried using
Alter Database offline in Query analyser and in Enterprise Manger. get 5070
error (user connected)1) Please define "locking up the server".
2) I would imagine that you can't alter database state (i.e. take it
offline) while it is undergoing a transaction rollback. I could be wrong on
this though.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>I had a user kill a process that is now doing a rollback that is locking up
> the server. Is there any way to force that databse offline? I tried
> using
> Alter Database offline in Query analyser and in Enterprise Manger. get
> 5070
> error (user connected)|||Yes it is in a rollback state. I guess locking up is the wrong choice of
words where. The transction is blocking other work on the DB that is more
important. I don't care about the state that the database would be left in.
"TheSQLGuru" wrote:

> 1) Please define "locking up the server".
> 2) I would imagine that you can't alter database state (i.e. take it
> offline) while it is undergoing a transaction rollback. I could be wrong
on
> this though.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
> news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>
>|||I don't think there is anything that can be done to stop a rolling back
transaction from affecting other work. Even pulling the plug on the server
will simply reset the rollback and it will restart when the server comes
back online. At least that is my understanding.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...[vbcol=seagreen]
> Yes it is in a rollback state. I guess locking up is the wrong choice of
> words where. The transction is blocking other work on the DB that is more
> important. I don't care about the state that the database would be left
> in.
> "TheSQLGuru" wrote:
>|||Kevin,
Not that I recommend this, but... At least in SQL Server 2000 and earlier,
shutting down the SQL Server and restarting it could indeed be faster than
allowing a long rollback to continue. It seems that the roll-forward was
much faster than the rollback. The few times that I did this (in
desperation) I found no evidence of corruption. (But it is the sort of
thing to make a DBA nervous, which is why I don't recommend it.)
One Upon A Time... I uncovered one possible reason for a very long rollback
in SQL Server 2000:
I had an extremely busy database and it was very trigger heavy. It appeared
that all of the trigger activity, which in 2000 scanned the transaction log,
actually hindered the rollback log processing from making much headway.
This is inferred from monitoring a very long rollback. (Obviously, I did
not restart the SQL Server this time.) The scenario was:
1. Long, out-of-control process had to be terminated. KILL SPID
2. Monitoring through the afternoon with KILL SPID WITH STATUSONLY it slowly
rewound to about 80% remaining to rollback.
3. At the end of the day, as the load on the database disappeared, it
finished rolling back in less than 30 minutes.
Assuming you those numbers were trustworthy: 3.5 hours to rollback 20%, then
0.5 hours to rollback 80%.
In SQL Server 2005 over the last 18 months I have not had a similar
experience, for what it is worth.
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13lbf5bgbd4uf8a@.corp.supernews.com...
>I don't think there is anything that can be done to stop a rolling back
>transaction from affecting other work. Even pulling the plug on the server
>will simply reset the rollback and it will restart when the server comes
>back online. At least that is my understanding.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
> news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...
>|||Russell;

> At least in SQL Server 2000 and earlier,
> shutting down the SQL Server and restarting it could indeed be faster than
> allowing a long rollback to continue.
How did you know that restarting SQL was faster in finishing a rollback? How
did you know that the rollback would not have taken as long to finish if you
had allowed it to finish without restarting SQL? Just curious.
Linchi
"Russell Fields" wrote:

> Kevin,
> Not that I recommend this, but... At least in SQL Server 2000 and earlie
r,
> shutting down the SQL Server and restarting it could indeed be faster than
> allowing a long rollback to continue. It seems that the roll-forward was
> much faster than the rollback. The few times that I did this (in
> desperation) I found no evidence of corruption. (But it is the sort of
> thing to make a DBA nervous, which is why I don't recommend it.)
> One Upon A Time... I uncovered one possible reason for a very long rollbac
k
> in SQL Server 2000:
> I had an extremely busy database and it was very trigger heavy. It appear
ed
> that all of the trigger activity, which in 2000 scanned the transaction lo
g,
> actually hindered the rollback log processing from making much headway.
> This is inferred from monitoring a very long rollback. (Obviously, I did
> not restart the SQL Server this time.) The scenario was:
> 1. Long, out-of-control process had to be terminated. KILL SPID
> 2. Monitoring through the afternoon with KILL SPID WITH STATUSONLY it slow
ly
> rewound to about 80% remaining to rollback.
> 3. At the end of the day, as the load on the database disappeared, it
> finished rolling back in less than 30 minutes.
> Assuming you those numbers were trustworthy: 3.5 hours to rollback 20%, th
en
> 0.5 hours to rollback 80%.
> In SQL Server 2005 over the last 18 months I have not had a similar
> experience, for what it is worth.
> RLF
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13lbf5bgbd4uf8a@.corp.supernews.com...
>
>|||Even if you restart the service , SQL Server will recover each database on
startup. And on this praticular database with redo/undo transactions it may
take ages. If you have the backup along with log backup I'd reccomend to
stop the server, drop the database --restart and restore the database.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3FDF4345-EF14-48AA-935A-0F4F36DA223F@.microsoft.com...[vbcol=seagreen]
> Russell;
>
> How did you know that restarting SQL was faster in finishing a rollback?
> How
> did you know that the rollback would not have taken as long to finish if
> you
> had allowed it to finish without restarting SQL? Just curious.
> Linchi
> "Russell Fields" wrote:
>|||Linchi,
Yes, that is a good question. The answer is, we had some code in those days
that caused this problem more than once. So, I got to practice both paths -
(a) restart the server, and (b) wait it out - at least two or three times.
Then the problem finally got solved.
So, despite everything we hold dear, it really was much (more than an hour)
faster. I do understand the logic of roll-forward and rollback, and the
arguments for why it is a bad idea and why it should not have worked. (But
it did.)
And, No, I have not done it in many years now. I just tell people that they
have to wait and that the code must be revised. :-)
RLF
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3FDF4345-EF14-48AA-935A-0F4F36DA223F@.microsoft.com...[vbcol=seagreen]
> Russell;
>
> How did you know that restarting SQL was faster in finishing a rollback?
> How
> did you know that the rollback would not have taken as long to finish if
> you
> had allowed it to finish without restarting SQL? Just curious.
> Linchi
> "Russell Fields" wrote:
>