Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving master LDF to log drive Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 2:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:17 AM
Points: 168, Visits: 695
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
Post #1361010
Posted Tuesday, September 18, 2012 9:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 6,630, Visits: 14,205
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"
Post #1361122
Posted Thursday, September 20, 2012 6:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:17 AM
Points: 168, Visits: 695
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.
Post #1361928
Posted Thursday, September 20, 2012 7:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 5,436, Visits: 10,116
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
Post #1361939
Posted Thursday, September 20, 2012 3:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:17 AM
Points: 168, Visits: 695
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.
Post #1362351
Posted Friday, September 21, 2012 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:26 AM
Points: 2,894, Visits: 3,278
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1362621
Posted Friday, September 21, 2012 7:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:17 AM
Points: 168, Visits: 695
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...
Post #1362664
Posted Sunday, September 23, 2012 11:12 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 7:48 PM
Points: 119, Visits: 494
Thanks

Cheers,
- Win
"Dont Judge a Book by its Cover"
Post #1363332
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse