Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Sql Server 2008 Recovery Database

Hi Could anybody explain to me why if i reboot My SQL Server one of my databases always goes into Recovery Mode?
0
pepps11976
Asked:
pepps11976
  • 5
  • 3
  • 3
  • +1
2 Solutions
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
pepps11976Author Commented:
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
 
Rahul AgarwalTeam LeaderCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
pepps11976Author Commented:
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
 
Rahul AgarwalTeam LeaderCommented:
is your SHAREPOINT DB goes to recovery
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
pepps11976Author Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
It's probably because your 'normal' login has default database set to one that is in recovery ;)
0
 
mayank_joshiCommented:
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
 
Rahul AgarwalTeam LeaderCommented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
mayank_joshiCommented:
frequent shrinking is not recommended
but its safe to do it in case of such problem.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Are you sure you can shrink database in 'recovering' state? ;)
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now