Help!! a 19GB SQL 2005 DB (in recovery) for 10+ hours!!!(not finished yet)

  • 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, the 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 12 hours (it is around GMT 10:00:00 now).

    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!!

  • Eros (6/27/2009)


    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, the same result!

    Maybe check the code in the proc. Truncate table completely wipes all data from a table, it'll always do exactly that. So if the empty table's been repopulated with the wrong info, the cause is likely in the code that's populating it.

    IS THE SQL SERVER REALLY RECOVERING THE DB BY ITSELF?

    Possibly. Check the latest error log. What does it say? (post the entire file here if you're unsure)

    If there's entries at the end that say something like this:

    Recovery of database '<DB name> is x% complete (approximately y more seconds) (Phase 2 of 3).

    then the db is been recovered. If that's not there and there are other errors and messages, there may be a problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dear Gail,

    Thx for your reply.

    I found nothing like 'Recovery of database ...' in the error log.

    But I found that one of my service program keep on trying to login to that problemed db --called RMS_DB.

    You can find the latest log here

    I have turned off that service for 20+ minutes. But there is no new entry to the LOG yet.

    Now, the DBCC has been running for 20 hours.

    dbcc checkdb(rms_db) with all_errormsgs, no_infomsgs

    And I refreshed the database but the problemed db -- RMS_DB still shows (In Recovery).

    Poor that there is no PROFILER in this Workgroup Edition.

    I don't know what the server is exactly doing.

    Should I keep wait or try to restart the SQL SERVER in single user mode?

  • Try restarting the service again, not single user, just normally. If things still don't come right, I would suggest that you contact Microsoft's CSS people. They will charge, but they're likely to get the problem resolved faster than we would here.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dear Gail,

    I have restarted the service. Surely, the db still shows (In Recovery).

    So, how can I know it is really recovering or not?

    Or what can I do now? Just wait?

    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 TaskProxy:(0x04160F20) Value:0x3f07900 Cost:(N/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 CorporationWorkgroup Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

  • Call Microsoft's Customer support. You've got stack dumps there, I don't have the tools to interrogate those and I don't have SQL source code. Customer support has both.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dear Gail,

    I have send the online enquiry to the MS Customer Support and hope they reply me soon. Hope they can solve quickly.

    Anyway, thanks for your advice.

    Best Regards,

    Eros

  • Hi Eros

    Given the time taken so far to fix the issue, have you considered that it might be quicker to restore the database from the latest backup?

    Chris

  • Can you see the deadlock? Can you kill it?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply