Post reply

create database ldf file placement in sql 2005 is this a bug?

  • December 13, 2006 at 12:17 pm

    #118241

    I am testing my first sql2005 install finally but have noticed something strange.

    If my logfile location for the model database is set to a diffent location from the data file I would have expected any databases created to have the same structure.

    IE I moved the ldf file for model to another drive. When I execute sp_helpdb 'model' the location is correct

    when I execute "Create database testdb"

    then run sp_helpdb 'testdb' the log file is located in the data file path.

    if I excute create database and specify file locations everything is ok.

    Any ideas?


    Trevor Hampson
    DBA

  • December 13, 2006 at 2:16 pm

    #677911

    Opps.  I meant the propereties for your server, not your database.

  • December 13, 2006 at 2:16 pm

    #677910

    Pull up the properties for your database.  Go to the Database Settings tab.  There you will find a section: Database default locations.  That is where you set the default locations for databases to be created.

  • December 13, 2006 at 2:22 pm

    #677916

    Sorry forgot to mention that I tried that as well. if Model is the template for the database creations why would it not use how model is defined.

    Thanks for the reply.

     


    Trevor Hampson
    DBA

  • December 13, 2006 at 3:56 pm

    #677940

    I have set the default locations on both SQL 2000 and SQL 2005 that way, and any databases I create through EM or SSMS have been created accordingly.  The only thing I have ever used model for was to add user-defined functions or types that I wanted to use in new databases without having to remember to run the scripts to create them when I created a new database.

  • December 13, 2006 at 4:33 pm

    #677947

    We use the server property 'Database Settings' to set where the data and log default locations are and it always works for us. We use both 2000 and 2005.

    -SQLBill

  • December 14, 2006 at 7:09 am

    #678067

    the model database, or any database for that matter, does not contain within itself it's own storage location...that is kept in master....the model database is used for the default schema and objects that will be created.

     

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 15, 2006 at 12:41 pm

    #678432

    Thanks for the responses.


    Trevor Hampson
    DBA

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

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