Solved

Sql Server 2008 Recovery Database

Posted on 2011-03-09
13
484 Views
Last Modified: 2012-05-11
Hi Could anybody explain to me why if i reboot My SQL Server one of my databases always goes into Recovery Mode?
0
Comment
Question by:pepps11976
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35081556
Do you have your database in Recover Pending state?
It may be because you don't have enough space on disk(s) where database files lies.
0
 

Author Comment

by:pepps11976
ID: 35081637
My server did come close to running out of disk space but i cured that problem and now whenever i reboot it goes into recovery mode, is there a way to fix this?
0
 
LVL 13

Expert Comment

by:agarwalrahul
ID: 35081641
If there is any transaction is in process and the system restarts, then that database goes into the recovery mode. To check is there any transaction is in process write sp_who2 in query analyzer in SSMS and its shows all the transaction happens that time with database name.
0
 

Author Comment

by:pepps11976
ID: 35081649
ok i did that and i got the following


1          BACKGROUND                          sa        .        .      NULL      RESOURCE MONITOR      0      0      03/09 10:28:16                                                                              1          0    
2          BACKGROUND                          sa        .        .      NULL      XE TIMER              0      0      03/09 10:28:16                                                                              2          0    
3          BACKGROUND                          sa        .        .      NULL      XE DISPATCHER         0      0      03/09 10:28:16                                                                              3          0    
4          BACKGROUND                          sa        .        .      NULL      LAZY WRITER           15      0      03/09 10:28:16                                                                              4          0    
5          BACKGROUND                          sa        .        .      NULL      LOG WRITER            62      0      03/09 10:28:16                                                                              5          0    
6          BACKGROUND                          sa        .        .      NULL      LOCK MONITOR          0      0      03/09 10:28:16                                                                              6          0    
7          BACKGROUND                          sa        .        .      master      SIGNAL HANDLER        0      0      03/09 10:28:16                                                                              7          0    
8          sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              8          0    
9          BACKGROUND                          sa        .        .      master      TRACE QUEUE TASK      0      0      03/09 10:28:16                                                                              9          0    
10         BACKGROUND                          sa        .        .      master      BRKR TASK             0      1      03/09 10:28:16                                                                              10         0    
11         BACKGROUND                          sa        .        .      master      BRKR EVENT HNDLR      0      47      03/09 10:28:16                                                                              11         0    
12         BACKGROUND                          sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              12         0    
13         BACKGROUND                          sa        .        .      DB_Opera_Copy      CHECKPOINT            187      39      03/09 10:28:16                                                                              13         0    
14         BACKGROUND                          sa        .        .      master      BRKR TASK             0      0      03/09 10:28:16                                                                              14         0    
15         BACKGROUND                          sa        .        .      master      BRKR TASK             0      0      03/09 10:28:16                                                                              15         0    
16         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              16         0    
17         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              17         0    
18         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              18         0    
19         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              19         0    
20         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              20         0    
21         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              21         0    
22         sleeping                            sa        .        .      master      TASK MANAGER          0      4      03/09 10:28:16                                                                              22         0    
23         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              23         0    
24         sleeping                            sa        .        .      master      TASK MANAGER          0      4      03/09 10:28:16                                                                              24         0    
25         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              25         0    
26         BACKGROUND                          sa        .        .      master      BRKR TASK             0      0      03/09 10:28:16                                                                              26         0    
27         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              27         0    
28         sleeping                            sa        .        .      master      TASK MANAGER          0      2      03/09 10:28:16                                                                              28         0    
29         sleeping                            sa        .        .      master      TASK MANAGER          0      0      03/09 10:28:16                                                                              29         0    
51         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      Search_Service_Application_PropertyStoreDB_05a367c6ef1745fcb93de0e65687a9eb      AWAITING COMMAND      0      0      03/09 10:39:12      Microsoft SharePoint Foundation                                         51         0    
52         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      Search_Service_Application_PropertyStoreDB_05a367c6ef1745fcb93de0e65687a9eb      AWAITING COMMAND      0      0      03/09 10:39:13      Microsoft SharePoint Foundation                                         52         0    
53         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      Search_Service_Application_PropertyStoreDB_05a367c6ef1745fcb93de0e65687a9eb      AWAITING COMMAND      0      0      03/09 10:39:14      Microsoft SharePoint Foundation                                         53         0    
54         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      msdb      AWAITING COMMAND      109      87      03/09 10:28:17      SQLAgent - Generic Refresher                                            54         0    
55         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      WSS_Content      AWAITING COMMAND      0      0      03/09 10:39:05      .Net SqlClient Data Provider                                            55         0    
56         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      SharePoint_Config      AWAITING COMMAND      0      0      03/09 10:34:52      .Net SqlClient Data Provider                                            56         0    
57         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      SharePoint_Config      AWAITING COMMAND      0      0      03/09 10:39:20      .Net SqlClient Data Provider                                            57         0    
58         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      Search_Service_Application_DB_b1a8fa0ca9aa4f0ba1a00b0f33170a25      AWAITING COMMAND      0      0      03/09 10:39:20      Microsoft SharePoint Foundation                                         58         0    
59         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      SharePoint_AdminContent_4d5d1221-18ef-433e-ace9-655ac03dde10      AWAITING COMMAND      0      0      03/09 10:38:45      .Net SqlClient Data Provider                                            59         0    
60         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      SharePoint_Config      AWAITING COMMAND      0      0      03/09 10:39:00      .Net SqlClient Data Provider                                            60         0    
61         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      WSS_Logging      AWAITING COMMAND      0      0      03/09 10:38:52      .Net SqlClient Data Provider                                            61         0    
62         RUNNABLE                            sa      SHAREPOINT        .      DB_Opera_Copy      SELECT INTO           16      7      03/09 10:39:17      Microsoft SQL Server Management Studio - Query                          62         0    
63         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      SharePoint_Config      AWAITING COMMAND      0      0      03/09 10:39:00      .Net SqlClient Data Provider                                            63         0    
64         sleeping                            sa      SHAREPOINT        .      DB_Opera_Copy      AWAITING COMMAND      0      0      03/09 10:39:17      Microsoft SQL Server Management Studio - Query                          64         0    
65         RUNNABLE                            sa      SHAREPOINT        .      Managed Metadata Service_1908d1430f2c4d95b3dfefe29092a6e5      SELECT                188      172      03/09 10:39:22      Microsoft SQL Server Management Studio - Transact-SQL IntelliSense      65         0    
66         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      PPS Service APP_246ead8564f6468fade1b49c6a56aa76      AWAITING COMMAND      0      3      03/09 10:34:51      .Net SqlClient Data Provider                                            66         0    
68         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      User Profile Service Application_ProfileDB_2442b681e9af48f385468d2c4a694d8a      AWAITING COMMAND      0      0      03/09 10:39:04      .Net SqlClient Data Provider                                            68         0    
69         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      SharePoint_Config      AWAITING COMMAND      0      0      03/09 10:34:37      .Net SqlClient Data Provider                                            69         0    
72         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      msdb      AWAITING COMMAND      0      7      03/09 10:30:00      SQLAgent - Job invocation engine                                        72         0    
73         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      Search_Service_Application_DB_b1a8fa0ca9aa4f0ba1a00b0f33170a25      AWAITING COMMAND      0      0      03/09 10:38:27      .Net SqlClient Data Provider                                            73         0    
74         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      Search_Service_Application_DB_b1a8fa0ca9aa4f0ba1a00b0f33170a25      AWAITING COMMAND      0      0      03/09 10:39:20      Microsoft SharePoint Foundation                                         74         0    
75         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      SharePoint_Config      AWAITING COMMAND      0      0      03/09 10:39:01      .Net SqlClient Data Provider                                            75         0    
77         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      User Profile Service Application_ProfileDB_2442b681e9af48f385468d2c4a694d8a      AWAITING COMMAND      0      0      03/09 10:35:14      .Net SqlClient Data Provider                                            77         0    
78         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      Search_Service_Application_CrawlStoreDB_a50c906307604d9b8dcea0b61baa30f3      AWAITING COMMAND      15      0      03/09 10:39:20      Microsoft SharePoint Foundation                                         78         0    
79         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      Search_Service_Application_CrawlStoreDB_a50c906307604d9b8dcea0b61baa30f3      AWAITING COMMAND      15      0      03/09 10:35:51      .Net SqlClient Data Provider                                            79         0    
80         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      ReportServerSharepoint      AWAITING COMMAND      0      0      03/09 10:39:15      Report Server                                                           80         0    
81         sleeping                            sa      SHAREPOINT        .      master      AWAITING COMMAND      62      45      03/09 10:25:15      Microsoft SQL Server Management Studio                                  81         0    
82         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      PerformancePoint Service Application_1dfe968bbd304423bd32066030b595c3      AWAITING COMMAND      0      7      03/09 10:34:44      .Net SqlClient Data Provider                                            82         0    
83         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      SharePoint_Config      AWAITING COMMAND      0      0      03/09 10:34:51      .Net SqlClient Data Provider                                            83         0    
85         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      SharePoint_Config      AWAITING COMMAND      0      0      03/09 10:39:20      .Net SqlClient Data Provider                                            85         0    
86         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      User Profile Service Application_ProfileDB_2442b681e9af48f385468d2c4a694d8a      AWAITING COMMAND      0      0      03/09 10:35:00      .Net SqlClient Data Provider                                            86         0    
89         sleeping                            LINKMICROTEK\administrator      SHAREPOINT        .      Managed Metadata Service_1908d1430f2c4d95b3dfefe29092a6e5      AWAITING COMMAND      0      0      03/09 10:39:18      .Net SqlClient Data Provider                                            89         0    
0
 
LVL 13

Expert Comment

by:agarwalrahul
ID: 35081673
is your SHAREPOINT DB goes to recovery
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35081678
Is your database in recovery for some time or is it still in that state?
Recovering is normal behaviour, time depends on transaction number in db transaction log. To shorten that time you need to take t-log backups.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:pepps11976
ID: 35081693
Its not in recovery for long, if its normall then thats fine its just when its in recovery i can only login using SA login not the one that i normally log in with.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35081757
It's probably because your 'normal' login has default database set to one that is in recovery ;)
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35081765
try shrinking the database.
Sometimes this problem is  created  in case of  large databases.

script for shrinking:-

USE [dbname]
GO
DBCC SHRINKDATABASE('dbname' )
GO

Open in new window

0
 
LVL 13

Expert Comment

by:agarwalrahul
ID: 35081769
Yes its the SQL Server normal working, if there is any transaction is in process and the system restarts then when system boots that database goes into recovery and time time taken by the DB to out from recovery is totally depends on the DB size means .MDF and .LDF files.
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 250 total points
ID: 35081847
Please avoid shrinking database or log files.
You can read about whole process here :
http://msdn.microsoft.com/en-us/library/ms191455%28v=SQL.100%29.aspx

Take care,
Daniel
0
 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 250 total points
ID: 35081873
frequent shrinking is not recommended
but its safe to do it in case of such problem.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35081998
Are you sure you can shrink database in 'recovering' state? ;)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now