Move Transaction log files

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

I'm sure the default transaction log path is part of the installation.
Have a look at database server-->properties

riaancornelius

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.

that will move them, but not set them to a default path on database creation

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
For detailed info on TempDB

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

>I wonder if moving the model databases ldf to a different path would result in this becoming the default? Any ideas?
no.

ASKER

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.

Donnie

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.

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.

@Nightman
>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 ... ;)

ASKER

Please tell me if I should move the transaction logs for distmdl?
Thanks

Are you going to be using replication? If not, don't worry about it.

ASKER

I have moved all .ldf files to my L drive except the following two files.

mssqlsystemresource.ldf
distmdl.ldf

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