Hello DBAs,
I think there are several different ways to move a
transaction log file to another disk. What would you do?
what method will you go with?
Currently DBs and T-logs are located in same disk, and I
learned that if they are located different disks would
increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
SunnyThis is a multi-part message in MIME format.
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Detach the database using sp_detach_db. (Don't bother updating the =statistics.) Move your log to the new drive, then re-attach, using =sp_attach_db.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"litnadsl" <lintadsl@.yahoo.com> wrote in message =news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
Hello DBAs,
I think there are several different ways to move a transaction log file to another disk. What would you do? what method will you go with?
Currently DBs and T-logs are located in same disk, and I learned that if they are located different disks would increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
Sunny
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Detach the database using =sp_detach_db. (Don't bother updating the statistics.) Move your log to the new =drive, then re-attach, using sp_attach_db.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"litnadsl"
wrote in =message news:031f01c3514e$f6=3ce230$a301280a@.phx.gbl...Hello DBAs,I think there are several different ways to move a =transaction log file to another disk. What would you do? what method will you =go with?Currently DBs and T-logs are located in same disk, and I =learned that if they are located different disks would increase SQL server performance from SQL trainning class.Please advice me on this, =new DBA here.Thanks in advance.Sunny
--=_NextPart_000_0295_01C3512E.6C124CF0--|||If you have a RAID system then you do not need to store the log file to a
different disk.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Detach the database and then when you do sp_attachdb then specify different
locations.
This you can do with user databases.
For system databases, you need to follow different approach.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||For user databases, follow Tom's description. For master, model, tempdb,
there're more steps involved including setting up trace flags and modifying
startup parameters. Please refer to the following MS KB articles:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
Richard
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Danut
I'm a little puzzled how you think a RAID system affects
the way a Transaction log is sequentially written to? (As
that is the main reason for putting it on a seperate disk)
Regards
John|||Well, I'm not expert but I read some articles on MSDN. Disk stripping offers
the best performance, adding the parity will make it falut tolerant so you
get both.
When you write to a RAID logical drive it will write it to more than one
physical drive in parallel and this is what makes it performant.
Well, let's look into MSDN:
"Both Microsoft Windows NT® and Microsoft Windows® 2000-based disk striping,
and striping with parity, can improve performance. Disk striping with parity
also protects against data loss in the event of media failure."
"Disk striping writes data in stripes across a volume (created from areas of
free space). For more information about volumes, see the Windows NT or
Windows 2000 documentation.
These areas are all the same size and are spread over an array of disks (up
to 32 disks). Striping writes files across all disks, so data is added to
all partitions in the set at the same rate.
Windows NT-based disk striping and Windows 2000 volume sets implement RAID
0. Disk striping provides the best performance of all Windows NT Server
disk-management strategies, but does not provide any fault-tolerance
protection.
Disk striping with parity is similar to disk striping. Disk striping with
parity adds a parity-information stripe to each disk partition in the
volume. This provides fault-tolerance protection equivalent to that of disk
mirroring, but requires much less space for the redundant data. Windows
NT-based disk striping with parity and Windows 2000 RAID-5 volumes implement
RAID 5. "
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0c6901c351f3$d490d470$a501280a@.phx.gbl...
> Danut
> I don't think you have got the idea here yet. RAID 5 uses
> an extra disk for parity checking. Say you have four disks
> in your RAID 5 array. You only have three disks to store
> data as you use one quarter of the space to hold parity
> data. Sql server uses this so that if one disk fails it
> can use the parity data to tell what data is missing. When
> the disk is replaced it can then rebuild the data.
> Performance wise RAID 5 is the worst of the raid systems,
> it turns one write into two reads and two writes to
> maintain the parity information.
> RAID 5 is not that bad if the database is mostly used for
> reads, with little write activity. For databases with high
> write activity, you are much better off using RAID 10 or
> RAID 01.
> Transaction logs are written too sequentially. If you keep
> your Transaction logs on seperate mirrored drives the
> heads are not moving all over the place they are writting
> where they already are. Putting transaction logs and
> database data files together on RAID 5 disks is just about
> the worst thing you can do performance wise. It might make
> administration easy, but it is a very bad idea.
> The only exception to that is if you are using OLAP with
> no update activity on the database.
> Regards
> John|||I know what is does, I've been a DBA for years, I'm just
telling you in simple terms
RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
WRITE A LOT
HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
SLOWS PERFORMANCE
You will not find anyone on here that disagees with any of
those statements.|||I will attest to what John is saying. Regardless of the Raid used it is
detrimental to performance to have the log file on the same array as the
data. The data is written randomly and the log is sequential and these two
just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
than a Raid 5 but in any case it should always be on it's own array.
--
Andrew J. Kelly
SQL Server MVP
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> I know what is does, I've been a DBA for years, I'm just
> telling you in simple terms
> RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> WRITE A LOT
> HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> SLOWS PERFORMANCE
> You will not find anyone on here that disagees with any of
> those statements.|||Ok. I got it.
Thanks!
Danut
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uZdPL2fUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> I will attest to what John is saying. Regardless of the Raid used it is
> detrimental to performance to have the log file on the same array as the
> data. The data is written randomly and the log is sequential and these
two
> just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
> than a Raid 5 but in any case it should always be on it's own array.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> > I know what is does, I've been a DBA for years, I'm just
> > telling you in simple terms
> >
> > RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> >
> > TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> > WRITE A LOT
> >
> > HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> > SLOWS PERFORMANCE
> >
> > You will not find anyone on here that disagees with any of
> > those statements.
>
