Solved

Move Transaction log files

Posted on 2006-10-21
19
4,233 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
  • 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
 
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 142

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 142

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 142

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 142

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now