split data and log files for system databases

Question

split data and log files for system databases

jonwolds

Right there with Babe

Points: 718
More actions
September 14, 2006 at 10:22 am

#115348

Hi,
Is it worth moving the system database data and log files from the default location on the c:drive to disks set up specifically for sql server data and log files?
Thanks
Jon

Viewing 14 posts - 1 through 13 (of 13 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Sure!

I install SQL Server program files on C and Data Files on the Data Drive. I do have installations where the system databases are on the C: drive but I don't do in anymore. The TempDb which is a system database tends to expand when you don't really expect it. I had 10 GB disappear on a nice night when a developer who was using a READ-ONLY logon was trying something new. Select statements do have to use TempDB.

Regards,Yelena Varsha

Timothy Ford SSC Enthusiast Points: 134 More actions · Answer 2

Look at MS Knowledge Base 224071 for the how-tos. Process depends upon which database you're moving and which version of SQL your modifying.

http://support.microsoft.com/kb/224071/

- Tim Ford

jonwolds Right there with Babe Points: 718 More actions · Answer 3

Thanks for the responses. I think i'll move them as it's nice to have a bit of consistency.

Jon

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 4

I've sent an article to Steve Jones on a utility to move all the system databases. Once it's published (hopefully soon, right Steve?) it'll make moving all the system DBs as easy as

c:\>MoveSql2005SysDbs InstanceName NewPath

Charles Kincaid SSChampion Points: 13593 More actions · Answer 5

I like just have the OS and very little else on C:.

If you are splitting things then put you data files and log files on separate drives if possible. Not different partitions on the same drive. Separate physical spindles. Even better if the drives are not on the same controller. They thought that I was nuts putting the data file on an IDE drive and the log file on the SCSI drive. Then we noted that our query times dropped by an average of 20% with no other changes.

ATBCharles Kincaid

jonwolds Right there with Babe Points: 718 More actions · Answer 6

I've alway moved tempdb and put all user databases on dedicated drives but until now i'd never considered moving the other system databases.

Cheers

Jon

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 7

Given an ideal situation I prefer to have all my mdf and ldf files on the same ( seperate ) drive arrays, including the system databases, makes things tidy. Tempdb is often a busy database so leaving it on what is probably an inferior array is possibly a bad idea. I prefer o/s and sql binaries on seperate arrays too, so my base sql server has to have 5 seperate arrays. ms kb clearly documents moving system databases to new locations .. practice on a test box first.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Charles Kincaid SSChampion Points: 13593 More actions · Answer 8

I don't mind things being tidy. Neat is often a good thing. However this should not, IMNSHO, trump considerations of performance.

Having the log on a different data channel from the mdf can be a quite a bump. It also protects against data loss if the mdf drive should go down.

SQL Server can do wonderful things right out of the box. When things get big performance can drop at a frightening rate. Looking at performance considerations before then can save you from having to be on you knees before the throne (board meeting) trying to defend yourself.

Neet will help you feel better, performance will help you keep you job so that you can be neat. Both have their place. I'm just trying to look out for you.

ATBCharles Kincaid

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 9

Colin, the 5 arrays hold OS, SQL Binaries, Data, Log, and TempDB, respectively? With backups, another is needed, huh? So, 6 different drive arrays, huh? Thanks.

Chris

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

I usually start out with four arrays...

c: - system/os and binaries

d: - datafiles

e: - translogfiles

f: - misc files, backups, logs etc.. (the 'junk' array)

In some cases you want to expand from there..

Tempdb may be a good candidate for it's own array if the system is using it heavily and so on.

The specifics of variations is very dependant on each situation...

/Kenneth

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 11

And everyone agrees that the system database data and log files should be separate, as well? Like many others here, I never thought to do this. Thanks.

Chris

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 12

I do that sometimes, sometimes not. Traditionally, for all except sometimes tempdb, the issue is kinda moot from a performance standpoint, since the activity in master, msdb and model is pretty moderate.

But, from a 'standards perspective', why not? It gives consistency, and we like consistency

/Kenneth

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 13

CGSJohnson

SSCertifiable

Points: 7208

July 19, 2007 at 7:23 am

#720746

Cool beans. Thanks for the input.