none
SQL Server 2000 - Restore command running for over 2 hours - logs show this error!

    Question

  • Hi there,

    I use SQL Server 2000. Using T-SQL I am trying to restore a db (11GB). It has been over 2 hours and the command is still running.

    When I check for logs all I see is as per this screenshot:

    Any help will be greatly appreciated! Let me know if I can provide more information.

    Thanks so much.

    Friday, July 26, 2013 5:38 AM

Answers

  • Oh, I am sorry, here are the complete error logs:

    http://www.screencast.com/t/nAAWJmPq

    Thanks so much.

    I found this from ur errorlog

    2013-07-25 13:20:58.47 spid11    Device activation error. The physical file name 'X:\MSSQL\DATA\GoodDW.mdf' may be incorrect.,0
    2013-07-25 13:20:58.47 spid11    Device activation error. The physical file name 'X:\MSSQL\LOG\GoodDW_Log.LDF' may be incorrect.,0

    2013-07-25 13:20:59.78 spid52    Database 'GoodDW' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.,0

    SO i assume you are trying to restore this DB from backup..Did you checked whether your backup with which ur restoring is consistent backup,please post result of the query below...run this command in different query analyzer window

    restore verifyonly from disk='backup location'


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, July 26, 2013 5:46 PM

All replies

  • Hi, on an old server we have SQL Server 2000.

    Using the GUI - Enterprise Manager - I am trying to restore db1.bak. When I click on OK button after that a window pops with a Stop button. On this window the progress bar remains gray and does not move at all not even a whit.

    DB size is 11GB while for other DBs I was able to do this for much larger DBs.

    Any help is appreciated.

    Thanks.

    Friday, July 26, 2013 4:32 AM
  • Hi can you check what your SPID is doinf with SP_who2.

    Also one suggestion you would have been used the WITH STATS=10    (where stats means status that gives restore for every 10% completion)using your restore T-SQL instead of GUI where the GUI always you cannot believe.

    Also note that how you are restoring, if you the database name already exists then you have to perform overwrite with replace option but replace means overwrite it is.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    • Edited by Rama Udaya Friday, July 26, 2013 4:39 AM updated
    Friday, July 26, 2013 4:38 AM
  • SP_WHO2

    Can  you see running this command what restore session's state(suspended or running). and see the wait type.


    Srinivasan

    Friday, July 26, 2013 5:40 AM
  • Thanks Srinivasan,

    This is what I get when I run SP_who2:

    I was logged in as BFW\sqlsupport - Also - Just in case this is useful - I do not see the BFW\sqlsupport and BFW\Administrator when I check in Enterprise Manager > Security > Logins.

    Please let me know if I can provide more information.

    Thanks so much.



    • Edited by ran009 Friday, July 26, 2013 5:54 AM Clarification
    Friday, July 26, 2013 5:49 AM
  • Thanks Rama,

    This is what I get when I run SP_who2:

    I was logged in as BFW\sqlsupport - Also - Just in case this is useful - I do not see the BFW\sqlsupport and BFW\Administrator when I check in Enterprise Manager > Security > Logins.

    Please let me know if I can provide more information.

    Thanks so much.


    • Edited by ran009 Friday, July 26, 2013 5:54 AM Clearer explanation
    Friday, July 26, 2013 5:53 AM
  • Is this all processes listed by sp_who2 ,i cannot see SPID for restore which you are running.You are runnig restore through script or GUI.

    select * from sysprocesses where spid>50

    I was logged in as BFW\sqlsupport - Also - Just in case this is useful - I do not see the BFW\sqlsupport and BFW\Administrator

    For this i assume BFW\SQLSUPPORT and BFW\ADMINISTRATOR is windows account which is using win authentication to login as Builtin Administrator is enabled in your SQL server


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, July 26, 2013 8:13 AM
  • what permission do you have on SQL server &  also you can use the T-SQL as well for restore-

    RESTORE (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms186858.aspx

    By the way how you are doing the restore in EM, can you share here.

    see-
    http://technet.microsoft.com/en-us/library/cc966495.aspx
    To restore a complete database backup to the same database

     but let us know how you are doing the restore,Dont mind BTW

    Also are you getting any error finally?check in the sql errorlog as well.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    • Edited by Rama Udaya Friday, July 26, 2013 8:14 AM updated
    Friday, July 26, 2013 8:14 AM
  • Sorry for late reply - The RESTORE SESSION is listed in the screenshot below.

    Please do let me know if I can provide more information.

    Thanks so much.

    Friday, July 26, 2013 2:52 PM
  • Hi Rama,

    I tried restore using - RESTORE command as well - no difference.

    I just notice that MSSQLServerADHelper is stopped and cannot be started when I try. Could this be the reason?

    In the SQL Logs I can see this:

    Please do let me know if I can provide any more information.

    Thanks.

    Friday, July 26, 2013 2:58 PM
  • From above screenshot restore is showing runnable, it seems restore is going on and its even not blocled by any SPID so all looks good. If you just got though to compare restore speed from 2005/2008 its less in 2000(personally i have also noticed) and i assume and have read also there was huge advancement in 2005 onwards in DB engine code .So be patient and from time to time keep looking sp_readerrorlog for confirming that no error is there.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, July 26, 2013 2:58 PM
  • Hi Shanky,

    Actually we have tried to leave this running for over two hours and still we seem to be getting nowhere.

    The DB size is only 11GB while other databases of greater sizes have been completed in much much less time.

    All this is so weird.

    Thanks so much.

    Friday, July 26, 2013 3:01 PM
  • Can u post  Sp_readerrorlog result

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, July 26, 2013 3:07 PM
  • Sure, here it:

    Please do let me know if I can provide more information.

    Thanks so much again.

    Friday, July 26, 2013 3:10 PM
  • I asked for complete error log and you should have provided it in text format and that too complete..anyways.

    1. Your restore is slow as it may have to recover lots of trn logs

    2.Instant file initialization is not there for 2000 so it starts from scratch

    3.Check Disk queu length value of disk where your file is being restored.

    4 I am sure your DB will be in full recovery model this also increases restore time


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, July 26, 2013 3:19 PM
  • Oh, I am sorry, here are the complete error logs:

    http://www.screencast.com/t/nAAWJmPq

    Thanks so much.

    Friday, July 26, 2013 3:32 PM
  • can you pleasepaste the T-SQL for which you are trying to restore & also did you get any error, if incase its not shoing any progress did you check  in sp_who2 does exists any block.

    Also did you see any progress in

    select * from sysprocesses

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, July 26, 2013 4:51 PM
  • Please paste the complete T-sql  to see what option you are using & how you are doing ?

    what is the SQL server service pack for sql 2000.

    Before restoring few things you need to check like

    restore filelistonly from disk='your backup file path name'  ---check the file size the reason why Iam saying that

    you might be having large intial size for the file & that need to keep trying to expand but it will if the space donot have that much to create it.

    2.Also do you see any other external load on OS level?

    3.are you restoring from the Network or from local machine?

    If nothing shows progress then run the SQL server side trace to see whats going and why it is not progressing at all.

    4.Checkout with your other administrators like N/W,OS &Stroage team to see any issues with drives/mountpoints where the backup file exists .


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, July 26, 2013 5:16 PM
  • Oh, I am sorry, here are the complete error logs:

    http://www.screencast.com/t/nAAWJmPq

    Thanks so much.

    I found this from ur errorlog

    2013-07-25 13:20:58.47 spid11    Device activation error. The physical file name 'X:\MSSQL\DATA\GoodDW.mdf' may be incorrect.,0
    2013-07-25 13:20:58.47 spid11    Device activation error. The physical file name 'X:\MSSQL\LOG\GoodDW_Log.LDF' may be incorrect.,0

    2013-07-25 13:20:59.78 spid52    Database 'GoodDW' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.,0

    SO i assume you are trying to restore this DB from backup..Did you checked whether your backup with which ur restoring is consistent backup,please post result of the query below...run this command in different query analyzer window

    restore verifyonly from disk='backup location'


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, July 26, 2013 5:46 PM
  • SInce Iam not able to open your screenshot, not sure.Addition to above, incase if you are taking full backup from other server & trying to restore here(like on other server) then it looks for the same path locations if the path its differnt then it throws an error during that time you need to use MOVE option in your restore to new location.

    or you might have  any file got corrupted ot issue with your disks as well where the file exists.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, July 26, 2013 6:00 PM