Moving Logs & TempDB

Question

Post reply

Moving Logs & TempDB

Richard L. Lichvar

SSC Enthusiast

Points: 190
More actions
August 22, 2005 at 11:01 am

#87318

Newbie question:
New hardware purchased and drives set-up to optimize SQL Server by putting logs on a separate drive for "sequential" files and putting tempdb and the system pagefile on a drive for "random" access files. (See "Bare Metal Tuning" by Kevin Kline in SQL Server Magazine). To do the initial port of apps from the old machine, we simply let location of these objects default. Now we want to use the optimization strategy. How can I safely move/point logs to the appropriate drive and move tempdb to it's appropriate drive?
Many thanks in advance for your help!
RichLich

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Read this article, by Steve Jones, about moving TEMPDB:

http://www.sqlservercentral.com/columnists/sjones/atemporarymove.asp

As for moving the logs, just detach the database, reattach using the WITH MOVE option. Refer to the BOL for more information.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Check out this article, by Chris Kempster, about moving databases....you can use the syntax he provides, just use the MOVE with the log file and not the .mdf.

http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp

-SQLBill

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

Just to make sure. You are aware that you will need a separate physcial drive for each database's log file, right?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Richard L. Lichvar SSC Enthusiast Points: 190 More actions · Answer 4

Mmmm. Nope, not aware of that. I was under the impression, from Kevin Klein's article and follow-up blog entry that you could put the log files for all DBs for at least a single instance all on the same drive.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 5

No. The reason why it is beneficial to put a log file on a single drive is that the log is always written to sequentially by SQL Server. That means the disk head never needs to 'jump around' and you get a high throughput. If you place several log files on a single disk that means you lose this benefit, since the disk head needs to move around to the end points of those files.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Richard L. Lichvar SSC Enthusiast Points: 190 More actions · Answer 6

Fortunately, the apps don't have to be super high-performance. I believe simply separating the sequential files, even though there will be some "jumping around," from the random access files, will provide us the level of performance we require. Again, I was simply following the general recommendations in the article I cited, newbie that I am.

RichLich