none
Help!! a 19GB SQL 2005 DB (in recovery) for 20 hours(not finished yet)!!! RRS feed

  • 問題

  • Hi all, please help!!
    What should I do!

    background:
    - Xeon E5405 @ 2GHz * 4 pcs
    - 4 GB Memory
    - Windows 2003 server (Standard Ed.)
    - SQL 2005 Server (Workgroup Ed.)
    - a single Production DB (19GB)

    Yesterday, I have to re-run a stored procedure to re-calculate a 600MB+ Summary table. Before that, I "TRUNCATE TABLE".
    Surely, "TRUNCATE TABLE" gave me a very impressive quick response "Command(s) completed successfully."
    And then I trial-ran the SP to calculcate one-month data . It was successful.

    After the trial, I truncated again. And then ran the SP to calculate 1.5-year data.
    However, although the Query Analyzer ( or say the SQL Managment Studio) replied "Command(s) completed successfully."
    I only got the result from the trial run -- one-month data.

    I was afraid there were something happened. So, I re-try the "TRUNCATE TABLE" and re-run the SP.
    However, a same result!

    I guess it should be something strange with the SQL SERVER. So, I restart the SQL SERVER in management studio.
    I can't really remember whether it had taken a long time to restart or not. Finally, I restarted the SQL SERVER SERVICE in force.....

    NIGHTMARE comes..... :""(
    When the SQL SERVER restarted, my Production DB showed "Production_DB (in recovery)" !!!
    Oh dear! I could not connect to it again!!
    It can't "TAKE OFFLINE" or "DETACH".
    It just keep in status silently.....

    I tried to run the "dbcc checkdb(Production_DB) with all_errormsgs, no_infomsgs" in "MASTER DB"....zzzZZZZZ
    It has nearly ran for 25 hours .

    IS THE SQL SERVER REALLY RECOVERING THE DB BY ITSELF?

    I found nothing special in the TASK MANAGER. CPU keeps 1% and no special high DISK I/O process. 
    I checked the "FILE Read Byte/Sec" &  "FILE Wrtie Byte/Sec" of "SYSTEM" in "PERFORMANCE" of administrative tools.
    Both keeps 1000 - 2000 Byte/Sec.

    Oh dear, what can I do? Just wait?
    Please help!!
    • 已編輯 Qboy 2009年6月29日 上午 06:01
    2009年6月29日 上午 05:59

解答

  • Recovery time depends on how many transactions need to rollback, you can force to bypass rollback but that may cause data integrity issue. Based on server log you posed, seems there was deadlock that caused memory dump. May need open case with Microsoft to fine out why. By the way, server seems has more than one user databases and you enabled db mirroring. Also looks like distributed queries were involved. Those may lead to longer recovery time.
    • 已標示為解答 KeFang Chen 2009年6月30日 上午 07:24
    2009年6月29日 下午 02:57

所有回覆

  • Dear all

    I have restarted the SQL SERVER service. Surely, the problemed db -- RMS_DB  still shows (In Recovery).
    So, how can I know it is really recovering or not?
    Or what can I do now? Just wait? I have already wait for another 18+hours yet.....

    Please advise.

    Please find the following updated log:

    Date,Source,Severity,Message
    06/28/2009 19:14:43,spid51,Unknown,Using 'xpstar90.dll' version '2005.90.4035' to execute extended stored procedure 'xp_enumerrorlogs'. This is an informational message only; no user action is required.
    06/28/2009 19:13:39,spid4s,Unknown,External dump process return code 0x20000001.External dump process returned no errors.
    06/28/2009 19:13:39,spid4s,Unknown,Stack Signature for the dump is 0x0000013A
    06/28/2009 19:13:39,spid4s,Unknown,* Short Stack Dump
    06/28/2009 19:13:39,spid4s,Unknown,* -------------------------------------------------------------------------------
    06/28/2009 19:13:39,spid4s,Unknown,* *******************************************************************************
    06/28/2009 19:13:39,spid4s,Unknown,*
    06/28/2009 19:13:39,spid4s,Unknown,*
    06/28/2009 19:13:39,spid4s,Unknown,* Unresolved deadlock
    06/28/2009 19:13:39,spid4s,Unknown,*
    06/28/2009 19:13:39,spid4s,Unknown,* 06/28/09 19:13:39 spid 4
    06/28/2009 19:13:39,spid4s,Unknown,* BEGIN STACK DUMP:
    06/28/2009 19:13:39,spid4s,Unknown,*
    06/28/2009 19:13:39,spid4s,Unknown,* *******************************************************************************
    06/28/2009 19:13:39,spid4s,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0005.txt
    06/28/2009 19:13:39,spid4s,Unknown,**Dump thread - spid = 4 PSS = 0x03FEE7D8 EC = 0x03FEE7E0
    06/28/2009 19:13:39,spid4s,Unknown,Using 'dbghelp.dll' version '4.0.5'
    06/28/2009 19:13:39,spid4s,Unknown,Deadlock monitor failed to resolve this deadlock.Server may require restart to recover from this condition
    06/28/2009 19:13:39,spid4s,Unknown,waiter id=process9686b8 mode=S requestType=wait
    06/28/2009 19:13:39,spid4s,Unknown,waiter-list
    06/28/2009 19:13:39,spid4s,Unknown,owner id=process9686b8 mode=X
    06/28/2009 19:13:39,spid4s,Unknown,owner-list
    06/28/2009 19:13:39,spid4s,Unknown,pagelock fileid=1 pageid=347 dbid=6 id=lock3e97100 mode=X
    06/28/2009 19:13:39,spid4s,Unknown,resource-list
    06/28/2009 19:13:39,spid4s,Unknown,inputbuf
    06/28/2009 19:13:39,spid4s,Unknown,executionStack
    06/28/2009 19:13:39,spid4s,Unknown,process id=process9686b8 waitresource=PAGE: 6:1:347 waittime=13687 ownerId=375 transactionname=SplitPage lasttranstarted=2009-06-28T19:13:25.837 XDES=0x3dfdcb8 lockMode=S schedulerid=2 kpid=5308 status=background spid=13 sbid=0 ecid=0 priority=0 transcount=0
    06/28/2009 19:13:39,spid4s,Unknown,process-list
    06/28/2009 19:13:39,spid4s,Unknown,deadlock victim=process0
    06/28/2009 19:13:39,spid4s,Unknown,deadlock-list
    06/28/2009 19:13:39,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x03DFDCB8 Mode: S SPID:13 BatchID:0 ECID:0 TaskProxy0x04160F20) Value:0x3f07900 CostN/A)
    06/28/2009 19:13:39,spid4s,Unknown,Requested By:
    06/28/2009 19:13:39,spid4s,Unknown,Input Buf: No Event:
    06/28/2009 19:13:39,spid4s,Unknown,SPID: 13 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1
    06/28/2009 19:13:39,spid4s,Unknown,Owner:0x03F09340 Mode: X Flg:0x0 Ref:0 Life:04000000 SPID:13 ECID:0 XactLockInfo: 0x03DFCE0C
    06/28/2009 19:13:39,spid4s,Unknown,Grant List 1:
    06/28/2009 19:13:39,spid4s,Unknown,PAGE: 6:1:347 CleanCnt:3 Mode:X Flags: 0x2
    06/28/2009 19:13:39,spid4s,Unknown,Node:1
    06/28/2009 19:13:39,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
    06/28/2009 19:13:39,spid4s,Unknown,Wait-for graph
    06/28/2009 19:13:39,spid4s,Unknown,Deadlock encountered .... Printing deadlock information
    06/28/2009 19:13:25,spid11s,Unknown,Service Broker manager has started.
    06/28/2009 19:13:25,spid11s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
    06/28/2009 19:13:25,spid11s,Unknown,The Service Broker protocol transport is disabled or not configured.
    06/28/2009 19:13:25,spid8s,Unknown,Starting up database 'tempdb'.
    06/28/2009 19:13:25,spid14s,Unknown,Recovery is writing a checkpoint in database 'AdventureWorks' (7). This is an informational message only. No user action is required.
    06/28/2009 19:13:25,spid14s,Unknown,0 transactions rolled back in database 'AdventureWorks' (7). This is an informational message only. No user action is required.
    06/28/2009 19:13:25,spid15s,Unknown,Recovery is writing a checkpoint in database 'AdventureWorksDW' (8). This is an informational message only. No user action is required.
    06/28/2009 19:13:25,spid15s,Unknown,0 transactions rolled back in database 'AdventureWorksDW' (8). This is an informational message only. No user action is required.
    06/28/2009 19:13:25,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
    06/28/2009 19:13:25,Server,Unknown,Dedicated admin connection support was established for listening locally on port 1434.
    06/28/2009 19:13:25,Server,Unknown,Server is listening on [ 127.0.0.1 1434].
    06/28/2009 19:13:25,Server,Unknown,Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
    06/28/2009 19:13:25,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    06/28/2009 19:13:25,Server,Unknown,Server is listening on [ 'any' 1433].
    06/28/2009 19:13:25,Server,Unknown,A self-generated certificate was successfully loaded for encryption.
    06/28/2009 19:13:25,spid14s,Unknown,21 transactions rolled forward in database 'AdventureWorks' (7). This is an informational message only. No user action is required.
    06/28/2009 19:13:25,spid15s,Unknown,6 transactions rolled forward in database 'AdventureWorksDW' (8). This is an informational message only. No user action is required.
    06/28/2009 19:13:24,spid8s,Unknown,Clearing tempdb database.
    06/28/2009 19:13:24,spid15s,Unknown,Starting up database 'AdventureWorksDW'.
    06/28/2009 19:13:24,spid14s,Unknown,Starting up database 'AdventureWorks'.
    06/28/2009 19:13:24,spid13s,Unknown,Starting up database 'RMS_DB'.
    06/28/2009 19:13:24,spid12s,Unknown,Starting up database 'StoreManager'.
    06/28/2009 19:13:24,spid11s,Unknown,Starting up database 'msdb'.
    06/28/2009 19:13:24,spid5s,Unknown,Server name is 'HLSIQSERVER'. This is an informational message only. No user action is required.
    06/28/2009 19:13:24,spid8s,Unknown,Starting up database 'model'.
    06/28/2009 19:13:24,spid5s,Unknown,The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
    06/28/2009 19:13:24,spid5s,Unknown,Starting up database 'mssqlsystemresource'.
    06/28/2009 19:13:24,spid5s,Unknown,SQL Trace ID 1 was started by login "sa".
    06/28/2009 19:13:24,spid5s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    06/28/2009 19:13:24,spid5s,Unknown,0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
    06/28/2009 19:13:24,spid5s,Unknown,1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
    06/28/2009 19:13:24,spid5s,Unknown,Starting up database 'master'.
    06/28/2009 19:13:24,Server,Unknown,Database mirroring has been enabled on this instance of SQL Server.
    06/28/2009 19:13:24,Server,Unknown,Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    06/28/2009 19:13:22,Server,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    06/28/2009 19:13:22,Server,Unknown,Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
    06/28/2009 19:13:22,Server,Unknown,Detected 4 CPUs. This is an informational message; no user action is required.
    06/28/2009 19:13:22,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    06/28/2009 19:13:22,Server,Unknown,-l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    06/28/2009 19:13:22,Server,Unknown,-e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    06/28/2009 19:13:22,Server,Unknown,-d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    06/28/2009 19:13:22,Server,Unknown,Registry startup parameters:
    06/28/2009 19:13:22,Server,Unknown,This instance of SQL Server last reported using a process ID of 5124 at 2009/6/27 下午 01:43:53 (local) 2009/6/27 上午 05:43:53 (UTC). This is an informational message only; no user action is required.
    06/28/2009 19:13:22,Server,Unknown,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
    06/28/2009 19:13:22,Server,Unknown,Authentication mode is MIXED.
    06/28/2009 19:13:22,Server,Unknown,Server process ID is 4284.
    06/28/2009 19:13:22,Server,Unknown,All rights reserved.
    06/28/2009 19:13:22,Server,Unknown,(c) 2005 Microsoft Corporation.
    06/28/2009 19:13:22,Server,Unknown,Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Workgroup Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    2009年6月29日 上午 06:01
  • Recovery time depends on how many transactions need to rollback, you can force to bypass rollback but that may cause data integrity issue. Based on server log you posed, seems there was deadlock that caused memory dump. May need open case with Microsoft to fine out why. By the way, server seems has more than one user databases and you enabled db mirroring. Also looks like distributed queries were involved. Those may lead to longer recovery time.
    • 已標示為解答 KeFang Chen 2009年6月30日 上午 07:24
    2009年6月29日 下午 02:57