Re: The location of transaction log file

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 12/06/04 

Date: Mon, 6 Dec 2004 23:26:22 +1100

You're definitely following a best practise by seperating data volume & tran
log volume, but whether this will improve performance of your system depends
on whether the volume that's presently performing the work of both data
volume & tran volume ( E:\ ) is actually a bottleneck in the first place.

The easiest way to check this is to use the Windows Perfmon tool & monitor
the physical disk object & Avg. Disk Read Queue Length / Avg. Disk Write
Queue Length for the E:\ counter instance. If these consistently report
counter readings more than 2-3 i/os queued per physical disk spindle then
the E:\ volume is not keeping up with the i/o workload & it's likely you'll
get a performance improvement from moving the tran log location.

Regards,
Greg Linwood
SQL Server MVP

"Vycka" <Vyciunas@one.lt> wrote in message
news:ueXbXj42EHA.2608@TK2MSFTNGP10.phx.gbl...
> Hallo,
>
> Simple question:
> How to change the location of transaction log file of existing database?
>
> For example, I have both database and its transaction log located in the E
> drive. Now I want to move transaction log in drive D.
>
> ------------------------
>
> The answer why I am doing this raises another question.
>
> My current system consist of three drives:
> C - Main OS drive
> D - SQL server system drive
> E - Data (database, transaction log and other data files (non related with
> sql)).
>
> To improve main system performance I decided to separate transaction and
> database files into two different drives. Q: Will this reconfiguration
> improve the performance? What percentage of improvement may I expect?
>
> Thank you
>
> Vytautas
>
> P.S. OS Windows Server 2003, MS SQL 2000 SP3
>

Relevant Pages

  • Re: Jetstress performance strange results...
    ... My opinion of what to put on the RAID 1 drives if you add them would be the ... Microsoft Exchange Support ... >server is in production and I've a lot of applications abvailable on it. ... Because each storage group has its own set of transaction log ...
    (microsoft.public.exchange.admin)
  • Re: The location of transaction log file
    ... If the size of the database is intemidating, you can backup the database, ... > I support the Professional Association of SQL Server and it's> community of SQL Server professionals. ... >>>> How to change the location of transaction log file of existing> database? ... >>>> My current system consist of three drives: ...
    (microsoft.public.sqlserver.server)
  • Re: Change Drive Location for Log Files.
    ... dependency to the logs physical disk resource and bring sql server back ... > of the instance/Database settings/default log directory, ... > the other active drives. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Change Drive Location for Log Files.
    ... Jeremey ... > dependency to the logs physical disk resource and bring sql server back ... >> the other active drives. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Why magnetic drums was/are worse than disks ?
    ... outside, and appeared to be two independent drives (IOW, the "inside" ... I think that some IBM disk drives, like the 3380, help ... clone that emulated two 2314s on a single spindle, with a single arm. ... allocating sort work space on both volumes on a single physical disk. ...
    (comp.arch)