SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database in Transition


Database in Transition

Author
Message
jswong05
jswong05
Say Hey Kid
Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)

Group: General Forum Members
Points: 701 Visits: 476
Microsoft SQL Server 2005 - 9.00.4266.00 (X64) Oct 7 2009 17:38:17
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

name xx_Test
dbid 46
sid 0x010500000000000515000000596FB2542C1A072CB776D02C046E0000
mode 0
status 4194328
status2 1090519040
crdate 2010-09-03 15:24:58.030
reserved 1900-01-01 00:00:00.000
category 0
cmptlevel 90
filename F:\SQLData\xx_Test.mdf
version 611
cannot detach
cannot take offline
cannot change to single user mode
the run-away process is being killed and is showing as rollback but 0% progress by kill SPID command 2nd time.

not sure what status this is: Status = 4194304 + 16 + 8
restart sql server service, it went away.

Jason
http://dbace.us
:-P
K Xavier
K Xavier
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 379
After killing a process, if the database goes into transition just see the estimated rollback time by
just running the same KILL statement of that SPID. There you can see the estimated rollback time in seconds

wait till it is done. If you restart the server services,the database will automatically go into suspect mode due to rollback was abruptly stopped.

Here are the steps to bring back a database from a Suspect mode in SQL 2005

First bring the Database into Emergency Mode
then run the DBCC checkdb (if you find any error repair it else)
next alter database and set to single user mode

then set the DB to multi user
jswong05
jswong05
Say Hey Kid
Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)

Group: General Forum Members
Points: 701 Visits: 476
That had been verified. The rollback was not going anywhere for hours. Some contractor issued "DROP ASSEMBLY [SqlClassLibrary]".... Instead waiting for hours, we have to get it back even from recovery because it was affecting the entire DEV/QA server and all people. The suspect mode was before restart, not after restart, if you call 4194328 suspect. The key is that particular status code 4194304 = autoshrink 16 = torn page detection 8 = trunc. log on chkpt

AS I mentioned, none of those textbook commands work at the time,
cannot set emergency mode
DBCC checkdb does not return
cannot set single_user or multi_user

You may not have seen this until you see one.
What I have not told you is someone turned on "autoshrink" on this dev host (too many monkeys in the kitchen), that background process cannot be killed. The drop command probably came from a untested script that developer did not know what that portion does.

Jason
http://dbace.us
:-P
Badelal Yadav
Badelal Yadav
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 228
Dear All,

Just needs to kill the process, which is running on that database.
Then Refresh the database is will goes in off line take is online and finished.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search