Post reply

move transactions log to another physical server

  • July 6, 2006 at 1:34 am

    #60659

    Hi Folks,

      I have a few questions re the location of transaction log.

      Currently, my transaction log file and data file are on the same server but different hard drives (for example, the data file is in F drive and log file is in the E drive)

       The concern is when I lose the entire server, I lose both data file and transaction log.  The most current data that I can restore is from last night backup.

        Is it possible to move the location of the log file, for example, E drive of a 2nd server?  If so, how I can do it?    

        Thanks for your help.

     

     

     

  • July 6, 2006 at 3:07 am

    #647693

    The answer to your question is I don't know whether it's actually possible.  But even if it were, I wouldn't want to do it.  You'd have performance issues with transmitting the data across your network to the transaction log, and, worse still, you'd lose availability if the network went down or got too busy.

    If you're concerned about losing everything with the server, then I recommend that you set up log shipping or replication to your second server so that you have a standby with all the data on it.  Or just backup up the log on a regular basis and have the backups copied straight to your second server.

    John

  • July 7, 2006 at 1:18 am

    #647888

    John,

      Thanks for your suggestions on this issue.

      Currently, my data file is 2Gb, index file is 600 Mb and transactions log file is 1.7 Gb (the index is re-built daily).  The SQL server is 2000 stanadard edition. 

      The full database backup runs daily and takes 7 minutes.  The transactions log backup runs daily and takes 4 mintutes. 

       If I configure the "log shipping" or "backup the transactions log in the 2nd server" in regular basis (every 20 minutes), do you think the performance will be affected?

      Does it mean the only option is "replication"?  Thanks.

  • July 7, 2006 at 2:41 am

    #647900

    You'd have to test the impact of taking transaction log backups every 20 minutes.  I would be surprised if it had a noticeable effect on performance, though.  Don't forget that if you take log backups that often, they're not going to be as large as they will if you take them daily.  Therefore I think that you will be measuring the time they take in seconds rather than minutes.

    One more thing - if you have Standard Edition, you can't use SQL Server's built-in log shipping facility, which is only available in Enterprise Edition.  However, you can set it up yourself instead by scheduling backup and copy jobs.

    John

  • July 7, 2006 at 10:36 am

    #648037

       If I configure the "log shipping" or "backup the transactions log in the 2nd server" in regular basis (every 20 minutes), do you think the performance will be affected?

     

    I backup my transactjion log every hour and it ususally takes around 2 or seconds.

    Steve

  • July 7, 2006 at 1:51 pm

    #648063

    You will not be able to move the log files (or data files) to a network drive.  SQL Server will not support either on network drives.  Only backup files can go there.

    Frequent Tlog backups to network drives will not be noticeable, probably only 1-2 seconds.

    Good practice to do your full backup to the same place.  That way your recovery/rebuild files are in same place.



    Mark

  • July 20, 2006 at 3:42 am

    #650338

    Folks,

    Thanks for all your help.  My problem is solved.

    It is true that it takes a few seconds to backup the transactions log.

    I am able to a write visual basic script to copy the backup files from my server 1 to server 2 and delete the backup files in the server 2 after certain days.

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

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