Wednesday, July 04, 2012 1:14 PM
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?
Wednesday, July 04, 2012 1:23 PMAnswerer
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/
Wednesday, July 04, 2012 1:25 PM
I'm sorry I wasn't clear. The question was should we consider moving the files, not how to move the files.
Wednesday, July 04, 2012 1:35 PMAnswerer
Wednesday, July 04, 2012 1:46 PM
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.
Wednesday, July 04, 2012 1:49 PM
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.
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
Wednesday, July 04, 2012 2:38 PMI agree with the point of view to keep data and log files of system databases untouched on a seperate drive.
Thursday, July 05, 2012 4:31 PMModerator
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.
Friday, July 06, 2012 8:19 AM
Thanks for all your replies. Funny how much different opinions there are for system database files.
For now I'll keep things as is.
Monday, July 09, 2012 5:46 PMModerator
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.
- Marked As Answer by Maggie LuoMicrosoft Contingent Staff, Moderator Thursday, August 09, 2012 3:38 AM