Move Transaction log files


I have setup SQL 2005 on a two node cluster and it is not in production yet.
I have a seperate disk created for the log files and one for the data files.
I set the data location during setup but I can't seem to find information about how to set the log file path.
The reason for the seperate disk is performance.

I did see the default path for log files in the properties of the sql server and I did see the path of the log files in the properties of each database but I would like to be sure of the proper way to set this up before proceeding.

Also, I have found documentation about the tempdb database residing on its own spindle. If I am trying to acheive maximum performance, should I move this tempdb?

LVL 12
Who is Participating?
NightmanConnect With a Mentor CTOCommented:
Yes, putting the tempdb on it's own set of spindles will give you maximum performance. Look to create multiple files in the filegroup - one file and transaction log file per core (assuming you have multi-core processors). Size should be the same as your available memory (so on a 4-way dual core box with 16GB ram you would have 8x16GB files)

Looking up the transaction log path now
I'm sure the default transaction log path is part of the installation.
Have a look at database server-->properties
Use sp_detach_db to detach the database and move the log files physically,
then use sp_attach_db to attach them, specifying the new file location.
Sp_detach_db checks the database to make sure the files are clean and
removes the row in the Master database's sysdatabases table that references
the database. Sp_attach_db recreates the reference in sysdatabases and lets
you specify alternative file locations for the database files.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

that will move them, but not set them to a default path on database creation
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>. If I am trying to acheive maximum performance, should I move this tempdb?

>I set the data location during setup but I can't seem to find information about how to set the log file path.
there is none. by default, log files and database files go to the same (default) folder.
it is your job as dba to specify the correct file locations when creating the database(s).

to "move" the log file of a database once created, there are 2 ways:
§ back the database, and restore it, during the restore you can specify new locations for the files
§ detach the database, move the files as needed, and attach the database (using the sp_attach_db procedure) by specifying the path of all the files
I wonder if moving the model databases ldf to a different path would result in this becoming the default? Any ideas?

Note to self: Something to test.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I wonder if moving the model databases ldf to a different path would result in this becoming the default? Any ideas?
Donnie4572Author Commented:
Thanks for very quick response.
I have some experience with sql but I am not a DBA so this is why I'm lost on this.
Can you please tell me if you think this is a good plan?

Drive T is for data
Drive L is for logs

1.) Move the log files for all databases to L Drive including logs for system databases.
                (there are no production databases on this server I finished a fresh install of sql this morining)

2.) Future production database logs will be placed on L Drive.

3.) Move the tempdb database and its logs to L drive.

thanks angelIII - saves me the testing ;)

Donnie - ideally you want tempdb, data and logs all on separate spindles. It is a good plan. Make sure that they are all fault tolerant (RAID1 or RAID10). The more spindles in a LUN (or RAID container, depending on what you want to call it), the better the I/O performance.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
really ideally, you would even have multiple spindles for your database(s), as the bigger the database, the more filegroups you should have, each filegroup with 1 or more files.
for example, the primary filegroup would stay empty (effectively only storing the database object definitions, and having minimum 2 filegroups like DATA and INDEX, where you would locate the tables/clustered index and the indexes respectively.
however, as in production, you have a RAID system or even a SAN system, creating several logical drives makes no sense in regards to performance, only in terms of management.
first have more filegroups and several files per filegroup...
angelIII - multiple physical subsystems, not logical.

It really depends on your application profile. For high volume OLTP systems, split the transaction logs to a seperate LUN - this way you can optimise the controller cache (e.g. set to 100% write).

If your application profile results in heavy tempdb usage, this can create an I/O bottleneck if placed on the same physical disks as the transaction logs. In that case, best to split.

AND if you also have a heavy reporting requirement the data on even more physical disks.

You really need to understand your application profile to optimise this.

And I agree, the bigger the database the more filegroups you need.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>angelIII - multiple physical subsystems, not logical.
>creating several logical drives makes no sense

My sentence was indeed not complete.
I wanted to say:
creating multiple logical drives on 1 physical drive makes no sense, as the same spindles will be applied.

to explain:
indeed, in a SAN (or more generically, in a multi-raid controller/disk) environment, you should indeed create logical drives on physically different controllers/disks, so to be able to tune the disk layer to a optimal performance, ie for the specific usage of the disks by the database.

however, unless you have a real high-volume environment, the database size does not matter for disk layout.
only having multiple filegroups and multiple files per filegroup (and in sql server 2005 having tables partitionned with each partition on its own filegroup) configures for optimal performance from the sql server point of view.

Donnie has started by splitting the database data files and log files to different disk (systems), which is from the database point of view complete. having a dedicated disk (system) for the tempdb is even better.
you cannot do more from the database point of view, only fine-tune the disk systems.

So I think we agree on this...
Yup, we do agree ... ;)
Donnie4572Author Commented:
Please tell me if I should move the transaction logs for distmdl?
Are you going to be using replication? If not, don't worry about it.
Donnie4572Author Commented:
I have moved all .ldf files to my L drive except the following two files.


I will not be using replication.

Is it ok to leave these files on the data drive?

The MS article says that the mssqlsystemresource.mdf needs to be at the same location as the master.mdf ...but says nothing about the ldf files.

Would it be better to leave the mssqlsystemresource.ldf and master.ldf at the same location?

Thanks for all your help!

I wouldn't worry about them. They will not be used, since you are not using replication. If you really want to make 100% sure you can move them, but there should be no problem with either option
Donnie4572Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.