September 18, 2012 at 2:00 pm
I normally leave sys db's in the default location (not tempdb). However, I have a production 2008 instance, where the sys db log files (.LDF's) reside on my data file drive/LUN.
When testing on a SQL 2012 box I was able to alter/modify the files and move them fine with the exception of the master.ldf file. I had to change the path on the startup params w/in SQL config manager.
Everything restarted and moved ok. Is there anything I am missing or any "gotcha's" with this move? Will this affect upgrades or service packs?
Thanks
September 18, 2012 at 9:40 pm
There's little point moving the masters log file. Best to always keep the master files in one location as with model and msdb too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 20, 2012 at 6:43 am
Perry Whittle (9/18/2012)
There's little point moving the masters log file. Best to always keep the master files in one location as with model and msdb too
I'm not really seeing any activity on those log files, so I'll follow your advice.
September 20, 2012 at 7:04 am
SkyBox (9/18/2012)
Everything restarted and moved ok. Is there anything I am missing or any "gotcha's" with this move? Will this affect upgrades or service packs?
No, it won't affect anything like that. If your system databases data files are on your system drive (for example in C:\Program Files\Microsoft SQL Server\....) then I would recommend moving them to a drive dedicated to database files. This ensures that SQL Server doesn't get choked by growth of things like application logs, and conversely that any growth of your system databases won't eat up all the disk space that the operating system should be using. You may find that your msdb database grows quite large, especially if you don't control your job, backup and maintenance plan histories.
John
September 20, 2012 at 3:33 pm
John Mitchell-245523 (9/20/2012)
SkyBox (9/18/2012)
Everything restarted and moved ok. Is there anything I am missing or any "gotcha's" with this move? Will this affect upgrades or service packs?then I would recommend moving them to a drive dedicated to database files.
John
My sys database files are properly located on my data file drive (not C:). It's my system database transaction log files that also reside on that data drive rather than the dedicated log drive.
The only reason I started the post is because one reads/writes sequentially and the other randomly, but there's not much activity that I can see on those sys log files so it shouldn't be affecting the data drives performance.
September 21, 2012 at 6:47 am
There are no performance or integrity advantages in moving the locations of Master, Model, and MSSystemResource databases.
If you make heavy use of msdb then consider moving its files to where you store your user databases, otherwise leave that alone also.
Leaving these files in their original locations may look 'untidy', but many people run into problems when trying to move them. Also, Microsoft have a poor track record of making SPs and CUs work when these files are moved, but SQL 2008 R2 onwards (so far!) has been OK. A DBA should have more important things to do than put their system at risk doing work that gives no advantages.
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
September 21, 2012 at 7:34 am
EdVassie (9/21/2012)
There are no performance or integrity advantages in moving the locations of Master, Model, and MSSystemResource databases.If you make heavy use of msdb then consider moving its files to where you store your user databases, otherwise leave that alone also.
Leaving these files in their original locations may look 'untidy', but many people run into problems when trying to move them. Also, Microsoft have a poor track record of making SPs and CUs work when these files are moved, but SQL 2008 R2 onwards (so far!) has been OK. A DBA should have more important things to do than put their system at risk doing work that gives no advantages.
Thanks for the advice - this is why I like to research something before I change it...
September 23, 2012 at 11:12 pm
Thanks
Cheers,
- Win
"Dont Judge a Book by its Cover"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply