Log File Location

Question

Log File Location

Mark Schurmann

SSC Enthusiast

Points: 135
More actions
July 16, 2010 at 1:59 pm

#387373

I've read many articles and postings about the need to locate log files on separate disks from the data files to optimize performance. I am installing Sql Server 2005 on a new server and it seems that Microsoft makes this rather difficult. The TempDb data and log files are reasonably easy to move, however the log files for the system databases and the ReportServer database does seem to cooperate.
I am attempting to install Data files to disks mapped to the E: drive, Log files to F:, Tempdb files to G: and Tempdb Log to H:
During installation I can change the installation path for data files to E:. This will install all data and log files on E. After installation is complete, I am able to relocate the tempDB data and log files without problem and I am about the change the location of any new databases appropriate data and log drive by going to the Instance Properties/Database Settings.
If I try to change the location of any of the system database log files or the Report Server log files, the database or instance corrupts.
Am I being overzealous in trying to separate the system database log files?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Mark Schurmann (7/16/2010)
I've read many articles and postings about the need to locate log files on separate disks from the data files to optimize performance.

It is not just for performance but certainly to ensure you can recover in case your "database" disk fails.

Here is the reason. If you have filegroups and t-log in the same drive and drive crashes you got nothing beyond your last backup.

If you have your t-log in a different drive you can attempt to rollforward your recovery until the last second before the disk crashed.

You are not overzelous, t-log should not be in the same disk other datafiles are sitting.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Mark Schurmann SSC Enthusiast Points: 135 More actions · Answer 2

Noted. But why then does Microsoft make is so painfully difficult to move them elsewhere. I cannot find anyway to perform this task with the system databases.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

this is a bugbear of mine. Do not try and separate the data and log files for system databases. They are almost exclusively read only databases so you gain nothing by separating them. Keep it simple, once your system databases are installed leave them there, especially for SQL 2005 where master must be kept with mssqlsystemresource databases.

the rules about separate data and log are for user databases.

the only exception to this is tempdb as it can be a heavily used database.

you should be able to move the log files for reporting services databases although again these are not heavy write activity databases. Make sure the reporting service is down when you do this. Perhaps there was a mistake in your moving process, how did you attempt it?

---------------------------------------------------------------------

Mark Schurmann SSC Enthusiast Points: 135 More actions · Answer 4

I think I came to the same conclusion on the system databases. As for the ReportServer databases. I did not stop the Report Server before for executing

Alter database ReportServer modify file

(name=ReportServerLog, filename='F:\ReportServerLog.ldf')

This command succeeded but when I restarted SqlServer the database could not be accessed.

Thanks for the info. I think I will leave the ReportServer log files with the data.

Mark

george sibbald SSC Guru Points: 104210 More actions · Answer 5

these are the instructions on how to do it in an emergency

http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

- but my advice to you is if you dont have to, DON'T.

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 6

Mark Schurmann (7/16/2010)
I think I came to the same conclusion on the system databases. As for the ReportServer databases. I did not stop the Report Server before for executing
Alter database ReportServer modify file
(name=ReportServerLog, filename='F:\ReportServerLog.ldf')
This command succeeded but when I restarted SqlServer the database could not be accessed.
Thanks for the info. I think I will leave the ReportServer log files with the data.
Mark

did you actually copy and paste the file to new location? SQL does not do that for you.

---------------------------------------------------------------------

AndrewSQLDBA SSC-Insane Points: 21829 More actions · Answer 7

Mark

What they are talking about, is that the Logs and the data files need to be on different spindles, not different drive letters. There is a huge difference.

If you have your logs on drive H and the data on drive F, but if those drive letters are only partitions on the same physical spindle, and that spindle stops turning, then you lose everything.

I could not imagine that the report server would be getting hit that hard, that you would need to change the drives like that.

Andrew SQLDBA