Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to move an DB's LDF file to other File Group (secondary)

Answered how to move an DB's LDF file to other File Group (secondary)

  • Thursday, November 22, 2012 9:20 AM
     
     
    Sign In to Vote
    0

    Hi,

    I have an User DB called AG, in which i have 2 Data Files and 2 Ldf files, 

    now my point is the LDF files are created 1 by the time of creating the Db an year ago, and after 6 months i created another LDF file , so totally its now 2 LDF files.

    Surprisingly , though i created the 2nd LDF file for this DB , but i can find the trans are loading in 1st ldf file , alone, i can find this by the file size, but usually which ever the file created last ll be in use by SQL righgt?

    How should i solve this problem ? i want the 2nd ldf file to be used , not the 1st ldf file thats been created at the time of DB creation

    please suggest me a meathod

    hemadri

     

All Replies

  • Thursday, November 22, 2012 10:01 AM
     
     Answered
    Sign In to Vote
    0

    but usually which ever the file created last ll be in use by SQL righgt?

    --No I don't think so.

    Having multiple log files is not suggested. Log writing is sequential so multiple ldf's wouldn't help. SQL Sever would start writing to second LDF only when the first one is completely full. As for why the data is not getting in the second log file here's what I think

    1. you have two log file LDF1 and LDF2.

    2. SQL Server starts writing to LDF1.

    3. A transaction log backup (or other operation which frees up space on transaction log) happens and frees up space on LDF1

    4. SQL Sever starts writing to LDF 1 again never actually needing to go to LDF2.

    HTH

    sean

    Please mark posts as answer or helpful when they are.


     
  • Thursday, November 22, 2012 10:03 AM
     
     Proposed
    Sign In to Vote
    0

    Hi,

    There's really no merit in having 2 transaction logs.  It doesn't help performance at all and the 2nd will only be used if the first one fills up.  You should get rid of the 2nd redundant log file.  If you want to relocate the transaction log, you should do something like detach/attach


    Thanks, Andrew