Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Database in Transition Expand / Collapse
Author
Message
Posted Tuesday, November 09, 2010 7:19 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 27, Visits: 457
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
Post #1017907
Posted Tuesday, November 09, 2010 7:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 30, 2014 9:55 AM
Points: 20, Visits: 355
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
Post #1017921
Posted Tuesday, November 09, 2010 8:01 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 27, Visits: 457
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
Post #1017950
Posted Thursday, January 27, 2011 9:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 3, Visits: 219
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.
Post #1055067
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse