Log file partition

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).
>
> EdComments 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...
> > 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
>
>|||Stev
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 onl
'E' User databases user table
'F' User databases transaction log
'G' Backup
'H' Application Dat
D, E and F are usually RAID 1 + 0 all other drives are mirrored
We find this gives us good performance and good resilience
Regard
John

Locks output from sysprocesses table

Can you explain the lock information output from
sysprocesses table that I received?
SPID waittype waittime lastwaittype
waitresources
155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
160 0x0000 0 PAGELATCH_UP 2:1:31965
166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
183 0x0000 0 PAGELATCH_UP 2:1:31988
198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
217 0x0000 0 PAGELATCH_UP 2:1:31984
218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
Thank You,
MikeHi Mike
Right now it is showing that none of your processes are waiting for
anything. All of them have a waittype and waittime of 0 which means they are
NOT waiting. THe lastwaittype column, as the name implies, is the last thing
the process waited on, but there is no indication of how long ago or what
duration that wait was. The LCK-* waits are normal locks being waited for,
and the PAGEIOLATCH* waits are waiting for IO.
This looks totally boring and nothing to worry about.
Did you have some specific concerns about it?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1886001c41b62$9579a9e0$a301280a@.phx
.gbl...
> Can you explain the lock information output from
> sysprocesses table that I received?
> SPID waittype waittime lastwaittype
> waitresources
> 155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 160 0x0000 0 PAGELATCH_UP 2:1:31965
> 166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
> 183 0x0000 0 PAGELATCH_UP 2:1:31988
> 198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
> 217 0x0000 0 PAGELATCH_UP 2:1:31984
> 218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
> 219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
> 228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
> 230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
> Thank You,
> Mike