Log File Partition

I am innstalling SQL 2000 Enterprise on Windows 2000 server. I would like
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.Diane
A lot of that really depends on your DB. Do you expect a lot of transaction
? How large is the DB going to be? How large is your OS Partition?
I usually have an 8 gb OS
and if my Data Partition is around 100 GB
Then I put my Log Partition around 25 GB.
But that is my rule of thumb. If you have a lot of transactions that could
be large then you need to adjust accordingly.
Jeff|||Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff|||Diane,
The standard 3 drive setup is to put the operating system on the C drive, th
e application(SQL Server in this case) on the D drive and the data on the E
drive, in your case the .mdf files would be on E and your .ldf files would b
e on another drive( F for e
xample).
Ed|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.

> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.

> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.

> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
drive,[vbcol=seagreen]
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
>
>|||Steve
Most sites (and most people) have different views on the perfect disk set up
.
The way we do it.
'C' Operating system (NT4 or Windows 2000/2003)
'D' Install SQL Server, System databases, User databases primary filegroups
containing system tables only
'E' User databases user tables
'F' User databases transaction logs
'G' Backups
'H' Application Data
D, E and F are usually RAID 1 + 0 all other drives are mirrored.
We find this gives us good performance and good resilience.
Regards
Johnsql