production database down....hangs on startup..URGENT

I had a database hang earlier today and couldn't get it back. I tried to
stop and start the services but that hung too. Once I had ran out of
options, I rebooted the server (soft reboot). The services started
correctly and all the DB's looked ok...except one. I can't touch it in
anyway. I see there is a blocking process (under locks / process ID) but
it's a system process so I can't kill it.
I have no idea what to try. Can someone give me some help? Please?
Thanks for any info.
BruceSorry, left out some info: we're running SQL 2000 on a Windows 2000 box.
Bruce
"news.microsoft.com" <sbthor69@.hotmail.com> wrote in message
news:%23FVG%23NI7GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I had a database hang earlier today and couldn't get it back. I tried to
>stop and start the services but that hung too. Once I had ran out of
>options, I rebooted the server (soft reboot). The services started
>correctly and all the DB's looked ok...except one. I can't touch it in
>anyway. I see there is a blocking process (under locks / process ID) but
>it's a system process so I can't kill it.
> I have no idea what to try. Can someone give me some help? Please?
> Thanks for any info.
> Bruce
>|||news.microsoft.com wrote:
> I had a database hang earlier today and couldn't get it back. I tried to
> stop and start the services but that hung too. Once I had ran out of
> options, I rebooted the server (soft reboot). The services started
> correctly and all the DB's looked ok...except one. I can't touch it in
> anyway. I see there is a blocking process (under locks / process ID) but
> it's a system process so I can't kill it.
> I have no idea what to try. Can someone give me some help? Please?
> Thanks for any info.
> Bruce
>
A database will rarely "hang", how did you determine that it was hung in
the first place? Did you check for blocking before doing anything else?
My guess is that there was a large transaction in progress that was
causing blocking, giving the appearance that the database was "hung".
Now that you've stopped/started/rebooted, SQL is "recovering" that
database, and is in the process of rolling back that large transaction.
If you look at the SQL Server error log, you will see messages about
recovery.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for the reply. You're exactly right. I didn't check for blocking
before trying to restart the services. I would have waited, but I started
to panic when I couldn't even see the database. I see that it has blocks,
but when I try to select it in enterprise manager or query analyzer, the
client froze.
Since that has happened, I can't even see the SQL Server error log for that
DB. Should I just sit on it for now and wait for it to come back? Or are
there some tricks I can try to make sure all is ok and it's recovering
itself?
Bruce
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:452BC3AE.4040206@.realsqlguy.com...
> news.microsoft.com wrote:
> A database will rarely "hang", how did you determine that it was hung in
> the first place? Did you check for blocking before doing anything else?
> My guess is that there was a large transaction in progress that was
> causing blocking, giving the appearance that the database was "hung". Now
> that you've stopped/started/rebooted, SQL is "recovering" that database,
> and is in the process of rolling back that large transaction. If you look
> at the SQL Server error log, you will see messages about recovery.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||news.microsoft.com wrote:
> Thanks for the reply. You're exactly right. I didn't check for blocking
> before trying to restart the services. I would have waited, but I started
> to panic when I couldn't even see the database. I see that it has blocks,
> but when I try to select it in enterprise manager or query analyzer, the
> client froze.
> Since that has happened, I can't even see the SQL Server error log for tha
t
> DB. Should I just sit on it for now and wait for it to come back? Or are
> there some tricks I can try to make sure all is ok and it's recovering
> itself?
You can (and should) view the error log using Notepad, it's found within
the MSSQL directory tree, or wherever SQL is installed. It's odd that
Enterprise Manager and QA both froze, the only time I've ever seen this
is on a development server that had the Auto-Close option enabled on the
databases.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||... and SQL Server outputs progress report for the recovery phase in the er
ror log file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:452BC644.3020804@.realsqlguy.co
m...
> news.microsoft.com wrote:
> You can (and should) view the error log using Notepad, it's found within
> the MSSQL directory tree, or wherever SQL is installed. It's odd that
> Enterprise Manager and QA both froze, the only time I've ever seen this
> is on a development server that had the Auto-Close option enabled on the
> databases.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks for the help. You guys were right on target. The log file showed
the recovery process for the DB in question. After Tracy told me to wait
patiently (and I did), it recovered successfully.
Thanks again.
Bruce
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uk8K1gI7GHA.4232@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> ... and SQL Server outputs progress report for the recovery phase in the
> error log file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:452BC644.3020804@.realsqlguy.com...|||news.microsoft.com wrote:
> Thanks for the help. You guys were right on target. The log file showed
> the recovery process for the DB in question. After Tracy told me to wait
> patiently (and I did), it recovered successfully.
>
FYI, if this happens again, where the server appears to "hang", check
for blocking. It is extremely rare that you should have to reboot the
server like this. Look for a process that is blocking others, determine
what that process is doing, and use KILL to terminate it if necessary.
This will stop the process and rollback (this may take a while) the
changes that it made. This rollback is the same process that you just
witnessed. Read about the KILL command in Books Online to understand
the implications of using it...
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql