Best Practice for LDF location

Question

Best Practice for LDF location

rummings

SSCarpal Tunnel

Points: 4575
More actions
May 14, 2012 at 9:05 am

#261343

I read an article that stated that the .mdf and .ldf should be on different disks. Each time I create a new database, I make sure to adhere to this policy. Occassionally, software vendors need to create databases from their software... this takes the location in the Model. I read that it was a Best Practice not to change the location in the Model for the .ldf location. Can anyone verify or deny this? I cannot find the article now to show my Manager.
Thank you in advance!
Charlie

Viewing 12 posts - 1 through 11 (of 11 total)

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

Geoff A SSChampion Points: 11511 More actions · Answer 1

you always could let the app create the databases and then move the files by de-attaching and re-attachning them. the app will not care (or at least is should not care)

i have to do this for 2 apps i support. i have actually scriptted the whole process to happen when the file is created.

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

Are you saying that you have two disks on your server and are thinking of manually moving the log file for model?

I don't think that the databases take their location from model. They should only take the schema, size, and options that are set. The database locations should come from the defaults for your instance. The instance properties, Database Properties tabs has default locations for data and logs.

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 3

I am not really sure about that Steve. i just checked the instance "Database Settings" and they have the Data going to the J: drive and the Log going to the U: drive. The Model has both going to the J: drive. When new Databases are created manually or software... the data and log is set to the J: drive.

I read a Best Practice that recommended not changes in the default location settings, but I cannot find that article now to justify my standards.

Thanks for the reply.

Charlie

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 4

Hmm, I ran a test and moved my model log. Creating a new database doesn't put the log in the new spot.

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 5

I believe you.... just saying what our system settings are. I have no problem setting the location when I create new DBs or moving if set up by software... but my Manager wants me to prove my plan.

Thanks again for your time and information.

Charlie

Geoff A SSChampion Points: 11511 More actions · Answer 6

i have had this issue with a product from a vendor called GFI.

it creates a new database based on time or number of rows in the staging table.

no matter what i had set as Database Settings --> Database default locations, it always would create a new database wherever master / model lived. It was very frustrating. whatever the app did, it did not care what i wanted.

I found the only way around it was to de-attach the new database and re-attach where i wanted it.

not the best solution, but it worked.

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 7

What's your plan? Not allowing changes to model?

I wouldn't say that's a plan, or even a best practice. Model exists to make a template for new databases. I know people that have some requirements for every database, and they move it.

If that's your requirement for model, I'd move the model log. There's nothing wrong with doing that.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 8

rummings (5/14/2012)
I read an article that stated that the .mdf and .ldf should be on different disks. Each time I create a new database, I make sure to adhere to this policy. Occassionally, software vendors need to create databases from their software... this takes the location in the Model. I read that it was a Best Practice not to change the location in the Model for the .ldf location. Can anyone verify or deny this? I cannot find the article now to show my Manager.
Thank you in advance!
Charlie

Hi,

If I understand you correctly, you want to have a link that justifies that why mdf and ldf files should be on a seperate physical drive or LUN (if SAN)

Refer to the expert link:

http://www.brentozar.com/archive/2009/02/when-should-you-put-data-and-logs-on-the-same-drive/

Also, to make sure that any new db created will be on different dive (in your case)

go to Server properties (by rt click) --> database settings --> database default locations

here specify the default locations for data and log files.

And you are done ... 😀 Any new db created will be on the location you specified.

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

The default locations for the files of new databases is not the location of model's files. It's the SQL Server default data folder and default log folder.

Change those and any new database is created in the location that you specified, unless the create database statement explicitly specifies a file location.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

rummings (5/14/2012)
I read an article that stated that the .mdf and .ldf should be on different disks. Each time I create a new database, I make sure to adhere to this policy. Occassionally, software vendors need to create databases from their software... this takes the location in the Model. I read that it was a Best Practice not to change the location in the Model for the .ldf location. Can anyone verify or deny this? I cannot find the article now to show my Manager.
Thank you in advance!
Charlie

It doesn't actually use MODEL for the location of the mdf or ldf files. It uses some registry settings that you can change by right clicking on the server instance in SSMS, selecting [Properties], and then [Database Settings].

Personally, the word "disk" isn't quite the right word when speaking of where to put these files. In the world of sans and huge disks that have been partitioned, you can have many logical disks that all share the same spindles and heads. Despite what some folks say about sans, the more spindles and heads you can get involved for either file type, the faster things are going to run.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

Crud. I really do need to learn to scroll down. Gail already answered this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)