SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Placing data file , log file and temp file in 1 drive or separate drive ?


Placing data file , log file and temp file in 1 drive or separate drive ?

Author
Message
WhiteLotus
WhiteLotus
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10469 Visits: 1094
Hi Guys,

I am confused with placing data file , log file and temp file
When I did browsing , I found some articles saying it is best to place those files in 3 different drives as it will improve performance , but other article told me to put them together in 1 drive as it will reduce the failure rate of each volume ( less drive to be maintained )

- If I put all DATA files in 1 single drive independently , what will happen if it blows up ? Database is Read only and no insert ,update , and delete will be allowed ? BUT I am still able to do point in time recovery as LOG file is on other drive ?
- If I put all LOG files in 1 single drive independently , what will happen if it blows up ? Database is read only ?
- If I put all Temp files in 1 single drive independently , what will happen if it blows up ? We are not able to perform any join , sorting ?

Please advice
Much appreciated
pietlinden
pietlinden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53366 Visits: 16981
Putting all the files together on one drive invites a single point of failure disaster, so that's just plain wrong. Say they're all on the same drive, and the drive blows out. What do you do? If you had a backup and the log files, you'd just restore the old backup and then restore the transaction log, and the database would be brought current. If you have everything on the same drive, you can't do that.
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)

Group: General Forum Members
Points: 133338 Visits: 19370
If your database is read-only then your log file is not going to be used much and you're not going to need point-in-time recovery, so there's no harm in having data and log on the same drive. If you lose the drive, you just restore from backup. It's still a good idea to have tempdb on a separate drive, though. If your server is a VM, speak to your SAN administrator about whether there's any performance benefit of having separate drives.

John
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85538 Visits: 9624
The data and log files for any given db must always be on separate drives. That is, that a single physical drive failure cannot destroy both data and log files for the same db. Otherwise a single failure will destroy the whole db.

However, there is really no need to put all logs on a single drive. For example, for Db1, you could have data on driveA and log on driveB, and for Db2, you could have log on driveA and data on driveB. But no db should have its data file(s) and log both on driveA or driveB only.

Performance-wise, it can be best to spread tempdb across all drives, but if you have a dedicated drive available for tempdb, that can also work out well.

If a log file runs out of space, the db becomes in effect read-only. But if the log file is otherwise unavailable/damaged, the db will be unavailable also. A db must have a log file.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37354 Visits: 7124
[quote]
WhiteLotus - Thursday, November 8, 2018 11:00 PM
Hi Guys,

I am confused with placing data file , log file and temp file
When I did browsing , I found some articles saying it is best to place those files in 3 different drives as it will improve performance , but other article told me to put them together in 1 drive as it will reduce the failure rate of each volume ( less drive to be maintained )

Is your SQL Server using directly attached storage or is it using a SAN? If you use a SAN then any separate "drives" your server sees may just be logical drives carved out of the same physical array of disks, so it doesn't really matter then. I suppose to answer your other questions, it depends on the RAID level the disk array is setup with, and what you mean by "it blows up" as to what the effects will be.

WhiteLotus
WhiteLotus
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10469 Visits: 1094
Chris Harshman - Friday, November 9, 2018 11:48 AM
[quote]
WhiteLotus - Thursday, November 8, 2018 11:00 PM
Hi Guys,

I am confused with placing data file , log file and temp file
When I did browsing , I found some articles saying it is best to place those files in 3 different drives as it will improve performance , but other article told me to put them together in 1 drive as it will reduce the failure rate of each volume ( less drive to be maintained )

Is your SQL Server using directly attached storage or is it using a SAN? If you use a SAN then any separate "drives" your server sees may just be logical drives carved out of the same physical array of disks, so it doesn't really matter then. I suppose to answer your other questions, it depends on the RAID level the disk array is setup with, and what you mean by "it blows up" as to what the effects will be.


Thanks for the reply
I just found out from my sys admin , it is using SAN ,and if the drive is down ,SAN will automatic point other drive to get up and perform
Related Temp files , will performance gets better if i put it on dedicated drive ?
WhiteLotus
WhiteLotus
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10469 Visits: 1094
a
WhiteLotus - Sunday, November 11, 2018 4:57 PM
Chris Harshman - Friday, November 9, 2018 11:48 AM
[quote]
WhiteLotus - Thursday, November 8, 2018 11:00 PM
Hi Guys,

I am confused with placing data file , log file and temp file
When I did browsing , I found some articles saying it is best to place those files in 3 different drives as it will improve performance , but other article told me to put them together in 1 drive as it will reduce the failure rate of each volume ( less drive to be maintained )

Is your SQL Server using directly attached storage or is it using a SAN? If you use a SAN then any separate "drives" your server sees may just be logical drives carved out of the same physical array of disks, so it doesn't really matter then. I suppose to answer your other questions, it depends on the RAID level the disk array is setup with, and what you mean by "it blows up" as to what the effects will be.


Thanks for the reply
I just found out from my sys admin , it is using SAN ,and if the drive is down ,SAN will automatic point other drive to get up and perform
Related Temp files , will performance gets better if i put it on dedicated drive ?

What I mean by Blow up is it filling up the disk space ( no space left )

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)

Group: General Forum Members
Points: 881841 Visits: 47868
Everyone says that they're supposed to be on "separate drives". If these things are on a SAN, you'll have a bunch of "logical drives" and there's absolutely no guarantee that the files will actually be on separate physical drives unless the SAN guy sets it up that way. And I can tell you for sure that the SAN guy isn't going to dedicate a 650 GB drive to your paltry TempDB or other small drive requirement.

Also, if the head-end of the SAN goes south for the winter, all the drives are dead so it wouldn't matter if they were on real live separate "spindles" or not.

Backups are whole 'nuther story. If you're storing backups on the same SAN as the actual data, you're making a HUGE mistake!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
WhiteLotus
WhiteLotus
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10469 Visits: 1094
Jeff Moden - Sunday, November 11, 2018 5:21 PM
Everyone says that they supposed to be on "separate drives". If these things are on a SAN, you'll have a bunch of "logical drives" and there's absolutely no guarantee that the files will actually be on separate physical drives unless the SAN guy sets it up that way. And I can tell you for sure that the SAN guy isn't going to dedicate a 650 GB drive to your paltry TempDB or other small drive requirement.

Also, if the head-end of the SAN goes south for the winter, all the drives are dead so it wouldn't matter if they were on real live separate "spindles" or not.

Backups are whole 'nuther story. If you're storing backups on the same SAN as the actual data, you're making a HUGE mistake!


Hi Jeff,
Thanks for the response
Do you mean it is ok to put all data file , log file and temp file in 1 drive ? as these things are on a SAN
We put backup on different san
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)SSC Guru (881K reputation)

Group: General Forum Members
Points: 881841 Visits: 47868
For the sake of "management purposes", people will have the SAN guy set up different "drive letters" so they can more easily keep track of their data files, log files, and do some cool things with TempDB. On a SAN, that's normal "just" all logical junk. It's a very rare thing where the logical "drive letters" actually match physical drives.

So, yes... go ahead and do the separation of different files/uses by drive letter. Just understand that there's not going to be a performance improvement because you probably won't be talking separate drives just for your drive letters. And, other systems are also sharing those same disks so the chances of you have 1 or more physical drives dedicated to your drive letters falls somewhere between slim and none and slim just left the building. BigGrin

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search