Post reply

New databases not following model

  • June 9, 2005 at 2:24 pm

    #164527

    I am running SQL Server 2000. This database server has been in production for a few weeks with no issue. 8 DB's were created a few days ago and I noticed in the logs that they were set to auto close. I checked model and it does not have that setting, the recovery model is also not following model db all of a sudden. Now all new db's are all set to simple with auto close option . Model is set to bulk-logged. I checked the databases that were created a few weeks ago and they are all fine. Any ideas???

    Thanks

  • June 9, 2005 at 2:34 pm

    #564393

    strange... what happens if you create a dummy db just now?

  • June 9, 2005 at 2:47 pm

    #564396

    New DB's that I create have a simple recovery and are set to auto close. I have tried changing model from bulk and logged to full and simple and new db's do the same. New DB's are not following model.

    Thanks

  • June 9, 2005 at 2:55 pm

    #564400

    I'm no expert on this topic, but reading from the books online, I can't see anything about the recovery model..

    Check this topic in the books online : CREATE DATABASE

    Maybe that'll give you more info.

  • June 9, 2005 at 4:09 pm

    #564422

    What service pack are you on?

  • June 9, 2005 at 6:10 pm

    #564442

    Well I am running 8.00.760(SP3). I just realized looking at the version that this is SQL Server Personal edition. I think the wrong version was installed don't know if this is the issue or not but can this be upgraded to standard or do I need to backup db's and do a clean install?

  • June 10, 2005 at 6:54 am

    #564564

    the standard and enterprise versions can only be installed on the server OS platforms...win2k Server/ advanced server or win2003.

    on 2000/xp editions, it automatically installs personal edition instead.

    personal edition, in order to conserve resources, defaults databases to AUTOCLOSE=true; personally, i use this cursor to change all my databases on my development machine:

    declare

    @isql varchar(2000),

    @dbname varchar(64)

    declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')

    open c1

    fetch next from c1 into @dbname

    While @@fetch_status <> -1

     begin

     select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'

     select @isql = replace(@isql,'@dbname',@dbname)

     print @isql

     exec(@isql)

     select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'

     select @isql = replace(@isql,'@dbname',@dbname)

     print @isql

     exec(@isql)

     fetch next from c1 into @dbname

     end

    close c1

    deallocate c1

     

     

    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!

  • June 10, 2005 at 7:17 am

    #564574

    I also noticed this a while back and wrote about it in my blog.

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

  • June 10, 2005 at 7:32 am

    #564582

    This is fun stuff.  I had a problem with databases being set to auto-close.  Determined that databases created in MSDE default to auto-close.

    I suppose Personal Ed. does the same. 

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

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