Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: The log file for database 'model' is full

    Hi all
    Can someone point me in the right direction for solving my problem? My sql 2000 stopped working yesterday with the following error. I have tried running various sqlservr.exe parameters but none of them will start the service. I can't find how to "Back up the transaction log for the database to free up some log space" from a command prompt. Any help will be most gratefully received.


    ERROR:
    2009-03-20 09:26:03.61 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
    Dec 17 2002 14:22:05
    Copyright (c) 1988-2003 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

    2009-03-20 09:26:03.61 server Copyright (C) 1988-2002 Microsoft Corporation.
    2009-03-20 09:26:03.61 server All rights reserved.
    2009-03-20 09:26:03.61 server Server Process ID is 1580.
    2009-03-20 09:26:03.61 server Logging SQL Server messages in file 'd:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
    2009-03-20 09:26:03.61 server SQL Server is starting at priority class 'normal'(4 CPUs detected).
    2009-03-20 09:26:03.66 server SQL Server configured for thread mode processing.
    2009-03-20 09:26:03.66 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
    2009-03-20 09:26:03.74 server Attempting to initialize Distributed Transaction Coordinator.
    2009-03-20 09:26:04.80 spid3 Starting up database 'master'.
    2009-03-20 09:26:04.81 server Using 'SSNETLIB.DLL' version '8.0.760'.
    2009-03-20 09:26:04.81 spid5 Starting up database 'model'.
    2009-03-20 09:26:04.81 spid3 Server name is 'ABCDEVGIS'.
    2009-03-20 09:26:04.81 spid8 Starting up database 'msdb'.
    2009-03-20 09:26:04.81 spid9 Starting up database 'sde'.
    2009-03-20 09:26:04.85 server SQL server listening on 10.6.1.54: 1433.
    2009-03-20 09:26:04.85 server SQL server listening on 127.0.0.1: 1433.
    2009-03-20 09:26:05.02 server SQL server listening on TCP, Shared Memory, Named Pipes.
    2009-03-20 09:26:05.02 server SQL Server is ready for client connections
    2009-03-20 09:26:05.03 spid5 Error: 9002, Severity: 17, State: 6
    2009-03-20 09:26:05.03 spid5 The log file for database 'model' is full. Back up the transaction log for the database to free up some log space..
    2009-03-20 09:26:05.03 spid5 Could not write a CHECKPOINT record in database ID 3 because the log is out of space.
    2009-03-20 09:26:05.03 spid5 Clearing tempdb database.
    2009-03-20 09:26:05.24 spid5 Error: 9002, Severity: 17, State: 6
    2009-03-20 09:26:05.24 spid5 The log file for database 'model' is full. Back up the transaction log for the database to free up some log space..
    2009-03-20 09:26:05.24 spid5 Could not write a CHECKPOINT record in database ID 3 because the log is out of space.
    2009-03-20 09:26:05.24 spid5 WARNING: problem activating all tempdb files. See previous errors. Restart server with -f to correct the situation.

    Thanks very much
    Regards
    Liz

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    From the SQL Server log entry you've provided, it would appear that your system databases reside on the D drive. I would look there to see if there are any files that could be deleted or relocated to free up disk space so that when you restart SQL Server, the model database can grow it's log file so the remainder of the initialization process can continue.

    Since the model database is installed in full recovery mode, this begs two questions:
    1. are you backing up your system databases so that the transaction log can be marked for reuse?
    2. why are changes being made to the model database causing transaction log growth?

    The model database is used as a template for any new databases created on the server. It is installed with an initial size of about 2 - 5 MB, and there is really no reason for anyone to tinker with it without thinking thru the possible ramifactions and certainly not for the novice dba to consider.

    There is another possibility ... you could move the location of tempdb, but this is not for the novice to consider. So clean off the D drive of 10 - 20 MB and try starting SQL Server again. If it fails again, post the complete error log wrapped in code tags (see the "#" symbol at the top of the message block).

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Mar 2009
    Posts
    5

    Model log full

    Thanks for the reply Tomh53.
    As you've no doubt guessed I'm no sql expert. sql has been running on this server quite happily since 2004 with default settings for all of the dbases apart from my data one. I have never knowingly changed settings on any of the system dbases. Space is not an issue - it has 5.34gb free on the d drive and 1.25 on the c. The model.mdf is 64kb and the modellog.ldf is 15040kb. When you say post the full log do you mean the sqldump? If so, here it is. The ERRORLOG only contained what I posted before.

    I have all my data backed up - would it be simpler for me to re-install sql?
    Thanks very much
    Regards
    Liz
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Did you set limits to the size of the log files on the system dbs ?

  5. #5
    Join Date
    Mar 2009
    Posts
    5

    model log file problem

    Hi PMASchmed
    I installed this 5 years ago and in ignorance just accepted the defaults so I don't know if the log files have limits. Is there anyway I can check the settings whilst sql doesn't want to start?
    Thanks
    Regards
    Liz

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first advice would be to get an experienced DBA to help you fix things in the short term and set them up so that this problem doesn't occur again. That isn't likely to happen today, and it will probably cost you something between $100 and $1000, but as solutions go it is by far the safest in the short term and probably both the least trouble and the cheapest in the long term.

    If that isn't an option, my next suggestion would be to use Microsoft Support. There are several kinds of support that they offer, different levels of service with different prices... This will resolve your problem, and may help you avoid repeats of the same problem too.

    If you want to try to solve the problem yourself using help from the internet, you can certainly do that too. The problem is that it will take longer, you will put a lot more effort into the solution and will probably miss some of the contributing problems, and you will probably have to fix this problem or a similar one again fairly soon.

    -PatP

  7. #7
    Join Date
    Mar 2009
    Posts
    5

    Poorly Model Database

    Thanks for that Pat - unfortunately paying for a fix is not an option. I have therefore unistalled, reinstalled and recovered my data from my backup. Thanks for everyone's suggestions and help.
    Regards
    Liz

  8. #8
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58

    just a thought

    if your model database was growing and used up all your space, i wonder if someone has hacked in and starting copying data to your model from your server that they can scoop out later.. I agree with Pat there should be no reason for the model db to grow as it is supposed to be a template, hmm, I would definitely have a dba look at this as it could happen again and your data may not be secure....

  9. #9
    Join Date
    Mar 2009
    Posts
    5

    Model Log Problem

    Thanks for the suggestion Burkular, I will look into it straight away. Although it was always lieing about their not being enough space. I am monitoring it closely and it seems to be behaving properly at the moment.
    Thanks
    Regards
    Liz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •