Transaction log file location on install

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Transaction log file location on install

Author

Topic

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-19 : 10:32:21

I asked this before and was told that it was an option on setup, but for the life of my I can't find it.

I have a requirement for all the transaction logs to go to a separate drive (L:), I would like to be able to specify this location as part of the install but I don't see the option. I have been successful doing so after the install but it is a bit annoying to have to do it that way when it seems like the install should have this option.
I am reaching out once more to see if anyone can tell me if I am missing something in the install process that will allow me to do this?

Thanks in advance.

Future guru in the making.

Kristen
Test

22859 Posts

Posted - 2007-09-19 : 11:29:35

Its well hidden ...

My notes say:


MAKE SURE YOU CLICK "ADVANCED" !!

Database Services 
    Data Files - MAKE SURE YOU SET THE PATH! 
        Root path only (x:\) - system adds: MSSQL.n\MSSQL\DATA

Kristen

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-19 : 11:32:37

quote:

Originally posted by Kristen

Its well hidden ...

My notes say:


MAKE SURE YOU CLICK "ADVANCED" !!

Database Services 
    Data Files - MAKE SURE YOU SET THE PATH! 
        Root path only (x:\) - system adds: MSSQL.n\MSSQL\DATA

Kristen

Unfortunately I think that is just the Data files and not the log files. I believe I did see that. I have a different drive for the Data files and the log files. I'll check it again later and confirm though, don't have a chance to run through it right now.

Future guru in the making.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-19 : 12:05:53

In SQL Server Management Studio:
1. Right-click on the server in the Object Explorer, and click Properties on the drop down menu.
2. Click Database Settings in the Server Properties dialog box
3. Under Database default locations, enter the desired path in the Data and the Log text boxes, and then click OK

Edit: Didn't see you wanted it during setup. Oh well, this is how you can change it. For the install, you have limited options as Kristen already explained.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-09-19 : 12:43:00

I presume you can change the default location for Data / Log after install, as was possible with SQL 2000?

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-19 : 13:38:59

quote:
Originally posted by Kristen

I presume you can change the default location for Data / Log after install, as was possible with SQL 2000?

Kristen

Yes. See my prior post on this thread.

CODO ERGO SUM

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-19 : 14:10:00

Right, after install it can be moved. But this takes a few steps to move the system database log files since you have to copy the physical files to the new location and restart. It seems like they would have built the ability into the install though, I guess not.

Future guru in the making.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-19 : 14:18:56

quote:
Originally posted by Zoroaster

Right, after install it can be moved. But this takes a few steps to move the system database log files since you have to copy the physical files to the new location and restart. It seems like they would have built the ability into the install though, I guess not.

Future guru in the making.

I would not bother moving the system database log files, except for tempdb. There very little chance it will give you any performance benefit.

I set the system databases to go on one the drives where the data files will go for the user databases, set the tempdb locations, and then set the defaults for the user databases.

CODO ERGO SUM

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-19 : 14:28:52

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by Zoroaster

Right, after install it can be moved. But this takes a few steps to move the system database log files since you have to copy the physical files to the new location and restart. It seems like they would have built the ability into the install though, I guess not.

Future guru in the making.

I would not bother moving the system database log files, except for tempdb. There very little chance it will give you any performance benefit.

I set the system databases to go on one the drives where the data files will go for the user databases, set the tempdb locations, and then set the defaults for the user databases.

CODO ERGO SUM

Ok, I wasn't sure about that. I always understood it to be a good practice to separate the logs and datafiles but wasn't sure about the system databases. It definitely seemed odd that it would not be an option on install if it is a best practice!

Future guru in the making.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-19 : 15:02:06

The master, model, and msdb databases usually have very low transaction rates and they are not in full recovery mode, so there is very little benefit to moving their transaction log files to another drive. I doubt there is a situation where you would notice a benefit.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-09-19 : 15:12:46

"Yes. See my prior post on this thread"

Sorry, was in a rush and mis-read your post as setting the location for a specific database, not the server default.

"Dyslexics of the World Untie"!

Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-19 : 15:14:53

"I would not bother moving the system database log files, except for tempdb"

Hmmm ... it offends my sense of sensibilities to have MDF and LDF on separate drives, and set up all my Restore Routines etc to enforce that only to find that MASTER.MDF / LDF are in the same folder ...

... worse when that folder is "C:\program files\SomethingOrOther\"

Kristen

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-19 : 15:50:37

quote:
Originally posted by Kristen

"I would not bother moving the system database log files, except for tempdb"

Hmmm ... it offends my sense of sensibilities to have MDF and LDF on separate drives, and set up all my Restore Routines etc to enforce that only to find that MASTER.MDF / LDF are in the same folder ...

... worse when that folder is "C:\program files\SomethingOrOther\"

Kristen

Your last post has confused me Kristen, are you saying you don't advocate separating mdf and ldf files in any case? Or are you saying move none or all but not either or?

Future guru in the making.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-19 : 15:54:52

Just leave mdfs and ldfs together for master, model, and msdb. It provides no performance improvement to separate them, so isn't worth the time doing it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-19 : 15:56:42

quote:
Originally posted by tkizer

Just leave mdfs and ldfs together for master, model, and msdb. It provides no performance improvement to separate them, so isn't worth the time doing it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Ok, and Michael mentioned the TempDB, should I move it to it's own drive altogether?

Future guru in the making.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-19 : 22:58:09

Yes, better to.

Kristen
Test

22859 Posts

Posted - 2007-09-20 : 01:39:13

"are you saying you don't advocate separating mdf and ldf files in any case? Or are you saying move none or all but not either or?"

I'm saying for tidiness sake I would prefer to have all databases using the same strategy. But in reality we have all the system MDF / LDF in the same folder, now I think about it.

And for the avoidance, yes I would want LDFs on a separate drive.

And no stinking RAID5 anywhere

And O/S on a different drive. And ideally TEMPDB on a different drive, and also backups - which would benefit from having several drives for multi-file backups.

Kristen

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-20 : 08:11:36

quote:
Originally posted by Kristen

"are you saying you don't advocate separating mdf and ldf files in any case? Or are you saying move none or all but not either or?"

I'm saying for tidiness sake I would prefer to have all databases using the same strategy. But in reality we have all the system MDF / LDF in the same folder, now I think about it.

And for the avoidance, yes I would want LDFs on a separate drive.

And no stinking RAID5 anywhere

And O/S on a different drive. And ideally TEMPDB on a different drive, and also backups - which would benefit from having several drives for multi-file backups.

Kristen

Ok, thanks for the advice. I have all of it in place except the TEMPDB, does it warrant it's own drive or can I pair it with the backup drive or the log drive?

Future guru in the making.

Kristen
Test

22859 Posts

Posted - 2007-09-20 : 19:48:52

It just depends on how much hardware you have / can afford

Not sure I would pair it with the Log drive, which is essentially sequential write, whereas the TEMPDB MDF will be random-access, like the database MDFs.

But that's only a gut feeling, I have not tried it

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-20 : 23:04:43

Don't share tempdb with db log files.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources