Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
- Jesse
Jesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
Herbert
"Jesse A" wrote:

> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Databases
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
> on that database.
> I don't really need to complete the update (partial complete would be fine).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jesse

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
--
- JesseJesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
--
Herbert
"Jesse A" wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Databases
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
> on that database.
> I don't really need to complete the update (partial complete would be fine).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jesse

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working
'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
--
- JesseJesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
Herbert
"Jesse A" wrote:

> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Database
s
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'worki
ng'
> on that database.
> I don't really need to complete the update (partial complete would be fine
).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jessesql

Database corrupted?

Hi all,

Had a disk problem here, and all apps connected to our database hung. Nothing was responding, rebooted, and mssqlserver hangs on startup.

If i move the mdf and ldf files of the database in question to another folder, mssqlserver starts up ok, with the db marked as suspect.

It would seem the the mdf and ldf are corrupted.

I am restoring a backup from yesterday, but this will take hours.

Any suggestions trying to recover it as it sits?

Thanks in advance for any ideas.I had the same problem just last week. The only fix was to restore from last good backup and bring the db forward as far as you can with transaction logs.|||Odds are near certain that you aren't hung, it is just doing a truly massive recovery operation when the server restarts. The only answer I know is to let it recover, then you can go merrily on your way with your data intact.

-PatP|||Let it recover, it that complete, and run a dbcc checkdb to verify.|||Odds are near certain that you aren't hung, it is just doing a truly massive recovery operation when the server restarts. The only answer I know is to let it recover, then you can go merrily on your way with your data intact.

-PatP

The restore completed....now its time for the post-mortem.

The problem I was having was that I could not access the database using Query Analyzer, any application software, or Enterprise Manager. Any attempt to connect would result in a time-out, or just hang when trying to connect.

If the DB is not responding, how can one determine if a recovery operation is in progress?|||I use sp_who. Check for a low numbered (usually 10) spid doing a recovery in the database in question.

-PatP|||In answer to your next question "How do I run sp_who when I can't connect through Query Analyzer?", the answer is to try the OSQL command line utility, which sometimes works when all others fail.|||Good anticipation blindman :)

Thanks for the suggestions. I will file this information away for future reference.