June 4, 2009 at 3:52 am
I have just done fresh install of SQL 2008 on a server and I am wondering about best practice for locations of the log files for the system databases.
For the user databases it allows you to select a different drive for the data files and the log files, but not for the system databases.
I know it is good practice to separate the two onto different disks, but is this not also the case for system databases?
I have got the article about how to move system databases, but I was wondering whether it doesn't matter that the log files and the data files are on the same disk? Is it worth the effort?
If they should be separate, why doesn't the installation allow this, since it does for all the other settings?
Thanks in anticipation,
Rachel.
June 4, 2009 at 4:36 am
In a nutshell Its not worth the effort.
To expand on that:
mssqlsystemresource you cannot move anyway in 2008
master database has very little updating, so no performance requirement to separate logs
ditto for model (even more so)
msdb you would have to be really thrashing with SSIS jobs, backups, agent jobs to get any benefit, so to my mind to keep things uniform and simple again not worth the effort. If you get suspicious msdb log is being thrashed use sys.dm_io_virtual_file_stats to confirm, but I would certainly NOT do this as a matter of course.
Tempdb is a special case, if you expect this to be heavily used you may want to whole database on its own drive plus possibly with the no of files = cpu cores \2.
So specify the location you want for your system databases as part of the install process, perhaps move tempdb after the install (which is easy to do), and keep it simple.
---------------------------------------------------------------------
June 4, 2009 at 4:43 am
Thank you very much - that's exactly what I wanted to hear!
🙂
June 8, 2009 at 3:23 am
I agree with most of what George says, but prefer to treat msdb as a user database and move it, but this is just personal preference.
I like to keep about 90 days history for when backups have run, plus normal job history of 10,000 job executions, and this can easily give you a msdb of maybe 200 MB. For me, this is big enough and active enough for msdb to be treated in the same way as user DBs are.
Microsoft have had problems in the past making their SPs work if you move master. This may not happen in the future but you have to accept there is a higher risk of future problems if you move master than for any other database.
But as George says, the level of activity on master, resource and model is so low you gain no performance or integrity advantage in moving them.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 22, 2009 at 2:08 am
More useful information - thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply