Re: The location of transaction log file

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 12/06/04 

Date: Mon, 6 Dec 2004 08:19:23 -0500

I would change the location by using sp_detach_db, , copy the log file to a
new location, then sp_attach_db using the new filenames.. This shouldn't
take but a minute... 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Vycka" <Vyciunas@one.lt> wrote in message
news:OJIpKN52EHA.2540@TK2MSFTNGP09.phx.gbl...
> Thank you very much for your answer. I'll do this monitoring to find out
the
> bottlenecks.
>
> But what about my first question - How to change the location of
transaction
> log file of existing database?
>
> Vytautas
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:ODCwJ742EHA.3408@tk2msftngp13.phx.gbl...
> > 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: 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: Log Shipping restore failing
    ... shipping with a full backup-restore. ... I support the Professional Association for SQL Server ... corrupted transaction log file without screwing up the whole chain of ... because this one log file won't restore. ...
    (microsoft.public.sqlserver.server)
  • Re: Log file Size..
    ... This is what I just found about this subject of your growing log file. ... proberly an SQL Server 2005 problem I found on the Best practices technet ... transaction log space on the principal cannot be reused, ... seems the broken Database Mirroring I believe. ...
    (microsoft.public.sqlserver.clients)
  • Re: how to reduce log file size?
    ... How to Shrink the SQL Server 7.0 Transaction Log ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ... Log File Grows too big ...
    (microsoft.public.sqlserver.server)
  • Re: one transaction log per filegroup?
    ... SQL Server uses only one virtual log file per database at a time, ... multiple transaction log files will not help. ... Mike Epprecht, Microsoft SQL Server MVP ...
    (comp.databases.ms-sqlserver)