locked
database in recovery pending RRS feed

  • Question

  • Dear all

    pl help to resolve my problem

    i've change the database log file from E drive to Drive due to insufficient space.

    without attaching the log file i've shutdown the sytem. becz it's gone in hand mode.

    post resuming its showing recovery pending.i'm unable to get online db.

    Tuesday, June 30, 2015 6:03 AM

Answers

  • Ok thanks now I got it. Follow these steps

    1. Stop SQl Server and move the ldf file to original location and start the SQl Server. See if this helps

    2. You did not made change to SQl Server catalog and moved ldf file so SQl Server database is still looking for ldf file on E drive while it was there on d drive.

    3 Open Query and select master database run below command to change location to new one. You can refer how to move database using alter database command. This is dummy code

    ALTER DATABASE Personnel 
    MODIFY FILE ( NAME = Personnel_log, FILENAME = 
    "D:\Data\Personnel_log.ldf") 
    GO 
    

    4. After you have change the location now take database offline or you can stop sql server as well and move the ldf file to new location

    5. Start the server server


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Tuesday, June 30, 2015 12:06 PM

All replies

  • without attaching the log file i've shutdown the sytem. becz it's gone in hand mode.

    I am pretty mush sure you did some mistake and from your question I am not able to judge what.

    Can you tell what do you mean by above lines


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Tuesday, June 30, 2015 6:33 AM
  • actually when separating the Drive of log file from E to D. i'v not define the path.

    system gone in hang mode,so i've restarted the system.

    Tuesday, June 30, 2015 6:42 AM
  • Hi there,

    sounds like you have not followed the required safety precautions.

    here is the link for your reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb0da90a-bb97-4795-9181-2897a300b747/how-do-i-move-a-transaction-log-to-another-drive?forum=sqldisasterrecovery

    Now do the following steps.

    1) locate the mdf, ldf files

    2) shut down the sql server (if you can not take the database offline or no other issues)

    3) copy these two files to some other location as a precaution.

    4) restart the server then delete the database. (make sure mdf & ldf files are deleted.)

    5) now copy back the mdf file and ldf file to the drives where you wanted to place.

    6) now attache the mdf & ldf.

    follow the link: http://www.serverintellect.com/support/sqlserver/sql-database-attach/

     

    Tuesday, June 30, 2015 7:28 AM
  • no, it's not helpful

    i'm not able to open database

    databasepropertyex showing 'suspend' mode

    how do i attach the db

    Tuesday, June 30, 2015 8:51 AM
  • no, it's not helpful

    i'm not able to open database

    databasepropertyex showing 'suspend' mode

    how do i attach the db

    Do you have backup I mean recent backup restore from backup I cannot understand what you did.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Tuesday, June 30, 2015 9:11 AM
  • I would recommend that you open a case with Microsoft, and hopefully Microsoft can offer support in your native tongue. I don't want to denigrate you, but your limited command of English is definitely a problem here.

    You have obviously made a mistake somewhere in the process, and if we give you advice based on the limited information you have provided, the risk is that you will make more mistakes leading to the point where you cannot recover your database at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 30, 2015 9:47 AM
  • I would recommend that you open a case with Microsoft, and hopefully Microsoft can offer support in your native tongue.

    Opening case with microsoft is costly business if you have senior DBA or someone experinced in DBA activities ask them to help. Hopefully you would find one

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Tuesday, June 30, 2015 9:54 AM
  • find the step which I have done 

    1> try to shrink the database(mdf file)

    2>shrink fail due to insufficient space

    3>i've just stop the sql services and cut the .ldf file from source and paste it to new location to get physical space in that drive.

    4>paste done

    5>now i've restart the system 

    6>open the sql server management studio

    7>try to connect with database with IP (wan) but it's not connected

    8>try to connect locally it's connected

    9>but database instance is showing recovery pending

    10>i've the backup as well when i try to restore the database it's throw error.

    Tuesday, June 30, 2015 11:26 AM
  • Ok thanks now I got it. Follow these steps

    1. Stop SQl Server and move the ldf file to original location and start the SQl Server. See if this helps

    2. You did not made change to SQl Server catalog and moved ldf file so SQl Server database is still looking for ldf file on E drive while it was there on d drive.

    3 Open Query and select master database run below command to change location to new one. You can refer how to move database using alter database command. This is dummy code

    ALTER DATABASE Personnel 
    MODIFY FILE ( NAME = Personnel_log, FILENAME = 
    "D:\Data\Personnel_log.ldf") 
    GO 
    

    4. After you have change the location now take database offline or you can stop sql server as well and move the ldf file to new location

    5. Start the server server


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Tuesday, June 30, 2015 12:06 PM
  • Opening case with microsoft is costly business if you have senior DBA or someone experinced in DBA activities ask them to help. Hopefully you would find one


    Yes, opening a case in a situation like this is by no means cheap. But losing a database can also be darn expensive.

    The steps in Shanky's post are the right one, although when Wasim says 10> i've the backup as well when i try to restore the database it's throw error. I get a little nervous. If he has managed to overwrite the MDF file, he has lost that data.

    What error did you get when attempting to restore the database?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 30, 2015 10:27 PM
  • I think you might have corruption. checking using below syntax

    ALTER DATABASE yourDatabasename SET EMERGENCY
    DBCC checkdb(’yourDatabasename’)

    If you find any errors run the below commands:

    ALTER DATABASE yourDatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC CheckDB (’yourDatabasename’, REPAIR_ALLOW_DATA_LOSS)
    ALTER DATABASE yourDatabasename SET MULTI_USER".

    Thursday, July 2, 2015 4:48 AM
  • thanks

    resolve it.....

    Thursday, July 2, 2015 6:14 AM
  • thanks,

    Thursday, July 2, 2015 6:26 AM
  • Hi wasim,

    Could you please share us the solution of this issue? Or you can mark helpful reply as answer in this thread.


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Tuesday, July 7, 2015 8:39 AM
  • Was it some thing like taking offline and detach and attach method followed?

    Please share the solutions which will help us , thanks.


    Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== https://social.technet.microsoft.com/Profile/pradyothanadp http://www.dbainhouse.blogspot.in/

    Tuesday, July 7, 2015 12:46 PM