SQL Server System Database files placement

Question

Hi,

We've several SQL Server instances and the user database file are all placed on seperate drives (one for data, one for log). These drives were already present during setup, but the setup still put the data & log files for the system databases on the same drive.

Is it better to reallocate these files so that they are on seperate drives?

Kind regards,

Mark Verbaas

Answer 1

ALTER DATABASE  dbname SET OFFLINE

/*Copy the files on the drives*/

ALTER DATABASE dbname  MODIFY FILE(NAME= dbname ,FILENAME ='E:\Data\dbname.mdf')
ALTER DATABASE dbname  MODIFY FILE(NAME= dbname _log,FILENAME ='D:\Data\dbname.ldf')

ALTER DATABASE  dbname SET ONLINE

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

I'm sorry I wasn't clear. The question was should we consider moving the files, not how to move the files.

Best regards,

Mark

Answer 3

Yes, sure, you have to separate data and log files on different physical disks.No question :-)

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 4

It isn't necessary to have a separate data and log drive for system databases.  You can keep them on a single drive - preferably their own drive.  When I build a server, I will have the following volumes available:

SystemDB - contains all system database files (mdf/ldf/log), except tempdb

TempDB - move tempdb files here, mdf and ldf unless the system has an extremely high tempdb usage and then I will have another LUN for tempdb log.  I haven't built a system yet where this was needed.

UserDB - data files for user databases

Log Files - transaction log files for user databases

Backups - database backups

For smaller systems I will leave tempdb on the system database volume and will not have a separate volume for user database log files.  These systems are generally in a VM and hosted on the VM SAN which has more than enough capacity to handle the load without needing to separate the files.

---

Jeff Williams

Answer 5

I prefer to keep system database on its own drive, you don't need to separate the log and data file of system databases.The master should reside on a RAID5 or RAID1 array so it can be rebuilt in case of hardware failure.

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 6

I agree with the point of view to keep data and log files of system databases untouched on a seperate drive.

---

Geert Vanhove

Answer 7

The system databases, except for tempdb, are extremely small and not used that much.  They are not usually related to performance issues.   So leaving them on the installation drive is not an issue.

Answer 8

Hi All,

Thanks for all your replies. Funny how much different opinions there are for system database files.

For now I'll keep things as is.

Regards,

Mark

Answer 9

It is always best practice to seperate the user databases data and log file.  However, system databases are handled differently and tempdb is different still.

Moving master, model and msdb can be done, but are not standard practice.