Need help moving a transaction log.

Question

Need help moving a transaction log.

dale.schwabjr

SSC-Addicted

Points: 442
More actions
November 21, 2011 at 4:44 am

#143072

Hi, I am very new to SQL.
I am trying to get more performance out of our SQL 2005 32 bit server. I just added another SCSI
drive to the server to host the transaction log. I did not make any type of raid...just one drive (T:\).
The folders on my server are C:\Sqldata and c:\program files\Microsoft SQL Server\MSSQL.1\Data
1st question.
Is it just the transaction log that I should move to its own drive?
2st question.
Should I move the tempdb to the T:\?
3rd question.
I also read that I should set a maximum size for the transaction log instead of letting it grow.
If I am going to do this, should I just create a new transaction log on the T:\ with a pre-set limit?
Thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

As per SQL Server Database best practices, you should be having separate drives for DATA files, LOG files and TempDB (so at least 3 drives).

Regarding T-Log file size, it depends on your database size (you have not mentioned that) and also depends on the log backup strategy you have at your end..

Cannot suggest more without the missing details..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Bru has the right idea. Depending on the load of I/O for your files, I'd move either the log files or tempdb to reduce contention. That assumes you have I/O contention.

However, without RAID, if that drive fails, your server stops. That's not necessarily what people want. I'd be tempted to move tempdb, since that's easier to move back if you have a failure. If the log goes bye-bye, you're doing a restore.

So

#1 - not without a RAID drive

#2 - yes

#3 - log size depends on activity. Make regular log backups to figure out your peak size between log backups. Give some pad, set the log there. As far as max size, I don't set it. I monitor and adjust as needed, but I leave autogrow in place to take everything it can in an emergency.