Solved

Move Transaction log files

Posted on 2006-10-21
19
4,238 Views
Last Modified: 2008-01-09
Hi,

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?

Thanks,
Donnie
0
Comment
Question by:Donnie4572
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 4
  • +1
19 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 250 total points
ID: 17780333
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
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17780339
I'm sure the default transaction log path is part of the installation.
Have a look at database server-->properties
0
 
LVL 9

Expert Comment

by:riaancornelius
ID: 17780341
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.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 29

Expert Comment

by:Nightman
ID: 17780345
that will move them, but not set them to a default path on database creation
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17780347
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 17780360
>. If I am trying to acheive maximum performance, should I move this tempdb?
yes.
http://msdn2.microsoft.com/en-us/library/ms345408.aspx

>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
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17780407
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17780426
>I wonder if moving the model databases ldf to a different path would result in this becoming the default? Any ideas?
no.
0
 
LVL 12

Author Comment

by:Donnie4572
ID: 17780428
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
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17780435
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17780660
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...
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17780887
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17780912
@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...
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17780923
Yup, we do agree ... ;)
0
 
LVL 12

Author Comment

by:Donnie4572
ID: 17790362
Please tell me if I should move the transaction logs for distmdl?
Thanks
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17790523
Are you going to be using replication? If not, don't worry about it.
0
 
LVL 12

Author Comment

by:Donnie4572
ID: 17792907
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!

0
 
LVL 29

Expert Comment

by:Nightman
ID: 17793921
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
0
 
LVL 12

Author Comment

by:Donnie4572
ID: 17794845
Thanks!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question