June 4, 2008 at 7:38 am
One of my db's went suspect last night due to some ghost spids and the log running out of space.
tried to detach it and go to emergency mode, but no go because one of my ghost spids is blocking this. few months back i tried to select a lot of data, canceled and it hung and haven't had a chance to restart SQL on this machine.
did a test restore of the data to another server and then tried to drop this database, but still doesn't work. anyway to force it without restarting SQL or rebooting? we have a reboot scheduled for Friday night, but wanted to do this before that.
my first choice is to detach and then reattach with a new log file that i'll create. second choice is restore from tape
June 4, 2008 at 9:53 am
one of the check boxes on dettach box is for drop connections
If you check that is should allow you to dettach
June 4, 2008 at 10:01 am
tried that, don't work
June 4, 2008 at 3:05 pm
Have you tried setting the database to single-user mode?
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE or WITH NOWAIT
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 5, 2008 at 7:27 am
still the same thing
going to reboot it tomorrow night to clear the lock and then detach and will try to reattach with a new ldf
June 5, 2008 at 11:05 am
Did you try killing the spid manually with 'kill '?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 5, 2008 at 11:10 am
tried that as well
i think it's at rollback 0% or something like that. been that way for months. been too lazy to reboot it because so many apps point to it and would need to be restarted. and the person who supports the apps is always busy
the suspect db is just a log database that all the apps write to and we already repointed them to another server. nothing critical
June 6, 2008 at 8:24 pm
rebooted and the db came back all OK, took 15 minutes to recover
and i was hoping to try something i haven't done before
June 7, 2008 at 8:40 am
Bummer. I've seen this quite a few times where there's no way to clear the spid except to reboot.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 4, 2008 at 6:33 pm
Another way to recover suspect SQL database...
i had this problem sometimes with my customers. Finally have a solution. Reading physical file .mdf byte by byte can resolve this. But this is a long way and needs some work-profession.
July 4, 2008 at 9:44 pm
how do you do it?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply