| Groups | Blog | Home |
sql server new users : How to Change location of System ldf files
restoration of all user DBs so that Data files will be on RAID 5 D: and Log files will be on a faster drive L:, but I would like to have all system and user data files in the same location and all system and user log files in the same location. This all of sudden brings up a GOOD question, as I stated that is what I WOULD LIKE, but is it really necessary, and what is best practice??? I would really appreciate everyone's opinion here. Thanks you all very much for your time..
In most situations, there is really not much activity in the system =
databases -except TempDb. TempDb may benefit from a faster drive. Log files. Ideally, you would want a log file on a drive to itself. = Since the writes are sequential, better efficiency is accomplished when = the drive heads are not jumping around servicing other needs. A 'Best = practice' is to put the log files of any high performance OLTP databases = on dedicated drives (mirrored if possible).=20 Assuming one high traffic, high performance database, my preferred = arrangement is: datafiles on drive/LUN [X:], log file(s) on separate = drive/LUN [Y:\], TempDb on a third drive/LUN [Z:\]. SQL Server program = files on [C:\], all system databases/logs on [D:\] --=20 Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience.=20 Most experience comes from bad judgment.=20 - Anonymous [quoted text, click to view] "WANNABE" <breichenbach AT istate DOT com> wrote in message =
news:uCwNH0swGHA.5068@TK2MSFTNGP02.phx.gbl... > I'm preparing a new server to replace an old server, and have scripted = the=20 > restoration of all user DBs so that Data files will be on RAID 5 D: = and Log=20 > files will be on a faster drive L:, but I would like to have all = system and=20 > user data files in the same location and all system and user log files = in=20 > the same location. This all of sudden brings up a GOOD question, as I = > stated that is what I WOULD LIKE, but is it really necessary, and what = is=20 > best practice??? I would really appreciate everyone's opinion here. = Thanks=20 > you all very much for your time..=20 >=20
Thanks Arnie, That's part of what I was looking for, but if install has =
both data and log files on the same drive, how do I configure the server = to place these files as you specify below. I will do backup / restore = with move for the production db, but that doesn't work on system files. Second Question>> Backups will be run to disk (during a low production = time), then the tape backup will run on the disk. I was thinking that = the best place to store those would be the same drive as the log files, = but as I think this over a bit more... There will also be periodic log = backups during peak production, would these go to the same drive/LUN as = the data or another as yet undetermined drive/LUN?? Thanks again.. :-) [quoted text, click to view] "Arnie Rowland" <arnie@1568.com> wrote in message = In most situations, there is really not much activity in the system =news:eFvIfSuwGHA.4576@TK2MSFTNGP03.phx.gbl... databases -except TempDb. TempDb may benefit from a faster drive. Log files. Ideally, you would want a log file on a drive to itself. = Since the writes are sequential, better efficiency is accomplished when = the drive heads are not jumping around servicing other needs. A 'Best = practice' is to put the log files of any high performance OLTP databases = on dedicated drives (mirrored if possible).=20 Assuming one high traffic, high performance database, my preferred = arrangement is: datafiles on drive/LUN [X:], log file(s) on separate = drive/LUN [Y:\], TempDb on a third drive/LUN [Z:\]. SQL Server program = files on [C:\], all system databases/logs on [D:\] --=20 Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience.=20 Most experience comes from bad judgment.=20 - Anonymous [quoted text, click to view] "WANNABE" <breichenbach AT istate DOT com> wrote in message =
news:uCwNH0swGHA.5068@TK2MSFTNGP02.phx.gbl... > I'm preparing a new server to replace an old server, and have = scripted the=20 > restoration of all user DBs so that Data files will be on RAID 5 D: = and Log=20 > files will be on a faster drive L:, but I would like to have all = system and=20 > user data files in the same location and all system and user log = files in=20 > the same location. This all of sudden brings up a GOOD question, as = I=20 > stated that is what I WOULD LIKE, but is it really necessary, and = what is=20 > best practice??? I would really appreciate everyone's opinion here. = Thanks=20 > you all very much for your time..=20 >=20
On Fri, 18 Aug 2006 12:18:06 -0500, "WANNABE" <breichenbach AT istate
[quoted text, click to view] DOT com> wrote: >Thanks Arnie, That's part of what I was looking for, but if install has > both data and log files on the same drive, how do I configure the server > to place these files as you specify below. I will do backup / restore > with move for the production db, but that doesn't work on system files. Hi WANNABE, Best is to specify the correct location for the system databases while installing SQL Server. Moving them at a later time is not an easy procedure. It's documented in Books Online. Here's a link to the subject in the 2005 version of BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/72bb62ee-9602-4f71-be51-c466c1670878.htm [quoted text, click to view] >Second Question>> Backups will be run to disk (during a low production > time), then the tape backup will run on the disk. I was thinking that > the best place to store those would be the same drive as the log files Definitely not. As Arnie already indicated in his reply, you want to avoid having anything on the log's drive that could cause the head to move to get the best possible performance for data modifications. [quoted text, click to view] >, but as I think this over a bit more... There will also be periodic log > backups during peak production, would these go to the same drive/LUN as > the data or another as yet undetermined drive/LUN?? If you can spare another drive for the backups, then that would be ideal. If that's not possible, then make sure that the backups go to any drive other than a) a drive with log files (for the reason above) b) the same drive as the corresponding data file (for two reasons: redundancy [what's the point of a backup on the same disk as the original] and performance [one disc can read while the other is writing]). If your tempdb has high levels of activity, avoid that disc as well so that it can use it's full capacity to speed up your queries and transactions. That leaves with either of these possibilities (assuming you're using the drives as laid out in Arnie's reply): 1. Backup system databases to X: and user databases to D: 2. Backup system and user databases to C: 3. Any combination of the above. --
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |