Post reply

Moving the Logfile

  • April 29, 2005 at 9:09 am

    #88855

    Hi Guys,

    I am fairly new to SQL Server but have plenty off background in Oracle, Ingres, and RDB/VMS.

    A few years ago, contractors put togather a system for my current company using SQL Server, I am now charged with maintaining it.

    I noticed that the Transaction Logfile is on the same raid set as the Datafiles. I thought everyone was taught in Database 101 that you never place data and logs on the same volume.

    How do I move the Logfile to another drive.

    Do I close the DB, backup the DB, delete the Logfile, recreate the logfile in another location, then restart the DB??? 

    Can I do this using the SQL Server Enterprise Manager???/

    Any help would be appreciated.

     

    Thanks,

     Gary

  • April 29, 2005 at 9:29 am

    #555986

    Option 1. sp_detach_db / sp_attach_db

    OPtion 2. Backup / Restore

    You can use EM to do both but option 1 is the simplest in your situation.

    See BOL for details.

    BOL is Book Online which is installed as part of SQL Server by default.

  • April 29, 2005 at 10:50 am

    #556030

    Allen,

    Thank You for the swift reply.

    As I said I am new to this forum, what do you mean by BOL.

    Also instead of doing the detach and attach using stored procedures, can I do them using the Enterprise Manager?? If so what is the proper sequence of events to move the Logfile?

    Thanks,

     

  • April 29, 2005 at 11:48 am

    #556050

    From EM --> Right Click your database --> All tasks --> Detach DB --> Make sure all connection to DB is 0 and Click OK.

    Move th elog file to new drive.

    From EM --> Right click Databases --> All Tasks --> Attach DB.

     

     

  • April 29, 2005 at 12:17 pm

    #556063

    Allen,

    Sorry to be such a pain in the a--!

    But after I detach the DB, "to move the log file to new drive" Can I:

    Right click on the DB and select Properties,

    Select the Transaction Log tab,

    Delete the "Current" Log information,

    Add the "New" Log information,

    move the "physical" log file to the new location,

    then Re-Attach the DB

    Once again sorry for being a pain!

    Thanks,

    Gary

  • April 29, 2005 at 1:04 pm

    #556069

    After you Detach, the Database Properties is not available (you won't be abe to see it in EM any more until you attach)

    By the way when you attach you will have to specify the "new" location of the log file

    Oh and BOL = Books OnLine

     

    hth

     

     


    * Noel

Viewing 6 posts - 1 through 5 (of 5 total)

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