Transaction Log drive in simple recovery

Question

Transaction Log drive in simple recovery

Luis Cazares

SSC Guru

Points: 183706
More actions
August 18, 2012 at 2:41 pm

#262960

I'm creating a database on a server with 3 hard drives. One is mainly for the OS and I shouldn't use it, the second and third are available with enough space for the database expected size. Because the company is not interested on making log backups I'll set the recovery model to Simple.
My question is: should I use one disk for data and another one for the log? or should I divide data in 2 files (one on each drive) and the log in the "less used" drive?
Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?
How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Single drives, no RAID? If that's the case (and they're the same size) I'd probably prefer to set them RAID 1 (for redundancy) and put all files on one drive. Not really good choices either way.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vikrant S Patil SSCrazy Points: 2294 More actions · Answer 2

Theoretically speaking and from a performance perspective you should segregate location of

1) Data Files.

and

2) Log files.

This is because the IO associated with a data file for a standard OLTP database is random is nature whereas the IO associated with log files are sequential in nature.

Ideally, you should also place the TempDB files ( both data and log) on separate drives well but then you do not have that option as you do not have sufficient no of drives at this point in time.

But the important thing to note is that the final decision is very subjective to each environment and therefore I would like to mention that I also agree 100% with the earlier post as well regarding RAID suggestion and location of database files.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Thank you very much for your answers.

I'm new at administration and I guess I'll have to do some research on RAID and other parts referring to infrastructure of SQL Server.

Any suggestions on anything else I should study?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

This is decent - http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Disclaimer: I worked on that book.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass