Thursday, March 29, 2012

Database in recovery

Hi,
I'm not that familiar with SQL Server, just mainly use it for
development. Anyway I was modifying some keys in some tables last
night. Everything went fine and I continued to use the database.
When i woke this morning and turned on the computer, my database
was in recovery. I am unable to do anything to get it back.
I have tried a few things to alter the database, (ie. set offline, set
single user) but I get an error saying
"Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'trace05e' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."
when I do an sp_who I get
spid ecid status loginame
hostname
blk dbname
cmd request_id
-- -- --
----
----
----
----
--
----
----
-- --
1 0 background sa
0 NULL
RESOURCE MONITOR 0
2 0 suspended sa
0 NULL
LOG WRITER 0
3 0 background sa
0 NULL
LAZY WRITER 0
4 0 background sa
0 master
UNKNOWN TOKEN 0
5 0 background sa
0 NULL
LOCK MONITOR 0
6 0 sleeping sa
0 master
TASK MANAGER 0
7 0 background sa
0 master
TRACE QUEUE TASK 0
8 0 sleeping sa
0 NULL
UNKNOWN TOKEN 0
9 0 background sa
0 master
BRKR TASK 0
10 0 background sa
0 master
TASK MANAGER 0
11 0 suspended sa
0 master
CHECKPOINT 0
17 0 background sa
0 NULL
UNKNOWN TOKEN 0
19 0 background sa
0 master
BRKR EVENT HNDLR 0
20 0 background sa
0 master
BRKR TASK 0
51 0 sleeping NT AUTHORITY\SYSTEM
computer
0 ReportServer$sqlexpress
AWAITING COMMAND 0
53 0 sleeping computer\User
computer
0 master
AWAITING
COMMAND 0
54 0 runnable computer\User
computer
0
master
SELECT
0
56 0 sleeping computer\User
I can't find any commands to kill the last three processes.
I need to know two things.
1) How can I find out why my database went into recovery mode?
2) How can I get my database back into normal mode?
Thanks a million in advance
JDI managed to kill some processes and now I have two left, the one I am
using and one more, which is needed as a transport.
i still cannot execute this statment
RESTORE DATABASE trace05e
WITH RECOVERY
I get
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.|||Hi
I can only guess. When you start SQL Server it does perfom the opeartion (in
every databases on the server) 'recovering' by using CHECKPOINT command that
means that all transactions are resided in the cache should be REDO/UNDO
depends on its status.
<the_kiddie98@.yahoo.com> wrote in message
news:1141293232.548857.223880@.t39g2000cwt.googlegroups.com...
> Hi,
> I'm not that familiar with SQL Server, just mainly use it for
> development. Anyway I was modifying some keys in some tables last
> night. Everything went fine and I continued to use the database.
> When i woke this morning and turned on the computer, my database
> was in recovery. I am unable to do anything to get it back.
> I have tried a few things to alter the database, (ie. set offline, set
> single user) but I get an error saying
> "Msg 5011, Level 14, State 7, Line 1
> User does not have permission to alter database 'trace05e' or the
> database does not exist.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed."
> when I do an sp_who I get
> spid ecid status loginame
> hostname
> blk dbname
> cmd request_id
> -- -- --
> ----
---
> ----
---
> --
> ----
---
> -- --
> 1 0 background sa
>
> 0 NULL
> RESOURCE MONITOR 0
> 2 0 suspended sa
>
> 0 NULL
> LOG WRITER 0
> 3 0 background sa
>
> 0 NULL
> LAZY WRITER 0
> 4 0 background sa
>
> 0 master
> UNKNOWN TOKEN 0
> 5 0 background sa
>
> 0 NULL
> LOCK MONITOR 0
> 6 0 sleeping sa
>
> 0 master
> TASK MANAGER 0
> 7 0 background sa
>
> 0 master
> TRACE QUEUE TASK 0
> 8 0 sleeping sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 9 0 background sa
>
> 0 master
> BRKR TASK 0
> 10 0 background sa
>
> 0 master
> TASK MANAGER 0
> 11 0 suspended sa
>
> 0 master
> CHECKPOINT 0
> 17 0 background sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 19 0 background sa
>
> 0 master
> BRKR EVENT HNDLR 0
> 20 0 background sa
>
> 0 master
> BRKR TASK 0
> 51 0 sleeping NT AUTHORITY\SYSTEM
> computer
> 0 ReportServer$sqlexpress
> AWAITING COMMAND 0
> 53 0 sleeping computer\User
> computer
> 0 master
> AWAITING
> COMMAND 0
> 54 0 runnable computer\User
> computer
> 0
> master
> SELECT
> 0
> 56 0 sleeping computer\User
> I can't find any commands to kill the last three processes.
> I need to know two things.
> 1) How can I find out why my database went into recovery mode?
> 2) How can I get my database back into normal mode?
> Thanks a million in advance
> JD
>|||SQL Server shouldn't let anyone in to the database if it is in recovery stat
e. Perhaps it is only a
refresh problem in EM/SSMS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<the_kiddie98@.yahoo.com> wrote in message
news:1141293565.559941.65460@.v46g2000cwv.googlegroups.com...
>I managed to kill some processes and now I have two left, the one I am
> using and one more, which is needed as a transport.
> i still cannot execute this statment
> RESTORE DATABASE trace05e
> WITH RECOVERY
> I get
> Msg 3101, Level 16, State 1, Line 3
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
>|||Hi Uri,
Sorry for my ignorance but what does that mean. Is there a way I
can rollback the logs to a certain point in time?
Is there any way outside of sql server that I can gain access to
the stored procedures inside in the mdf file?
Thanks
JD
Uri Dimant wrote:
> Hi
> I can only guess. When you start SQL Server it does perfom the opeartion (
in
> every databases on the server) 'recovering' by using CHECKPOINT command th
at
> means that all transactions are resided in the cache should be REDO/UNDO
> depends on its status.
>|||nope it isn't a refresh error as I have rebooted a few times and I am
still unable to get the database to work.
I have now resorted to removing the instance and re-installing it and
trying to get back an older database. I can't run any commands like
dbcc checkdb even on master, there seems to be invisible connections
all over the place.sql

No comments:

Post a Comment