none
Log is out of space

    Question

  • Hello,
     
    I'm running SQL Server 2005 Developer edition on Windows 7 Ultimate.
     
    Suddenly I cannot start the SQL server anymore. If I start the service manually, it starts then shuts down. The error log says:
     
    The transaction log for database 'model' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
    2010-08-16 05:48:14.84 spid8s      Could not write a checkpoint record in database ID 3 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
    2010-08-16 05:48:14.84 spid8s      Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
    2010-08-16 05:48:14.84 spid8s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
     
    The space on the disk is not an issue, I have plenty of space. I don't know how this could happen, I have a job running that truncates all logs daily.
     
    But my main question is: What can I do now? To truncate the log I need to start the SQL server, and I cannot do this.
     
    I would appreciate your suggestions.
     
    Thank you.
    Monday, August 16, 2010 4:24 PM

Answers

  • With SQL Servers not running.

    Bring up command prompt.

    Navigate to where SQL Server Binaries are installed (Usually in C:\Program Files\Microsoft SQL Server\MSSQL.1\Binn\)

    Run following command :

      sqlservr.exe -m

    Leave the command window open.

    Go to another command window and connect using SQLCMD to extend the t-log on model db or to do backup. 

     

    But I am curious on how the model db t-log got full anyways; normal operations should have nothing going to model ever.

     

    Monday, August 16, 2010 5:51 PM
  • Found this thread http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/17c2744c-2e18-45c8-8b88-0e0b5db6461c that may be of some help.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, August 16, 2010 6:09 PM
  • The reason SQL Server is not starting is because tempdb cannot be created.

    Tempdb cannot be created because of the issue with model; tempdb is cloned from model on startup.

    You will need to restart SQL Server with trace flag 3609 to bypass tempdb creation.

    This will allow the server to start, you can then resize the model db log file, remove the trace flag and restart SQL Server.

     


    ajmer dhariwal || eraofdata.com
    Monday, August 16, 2010 9:46 PM
    Answerer
  • You can also make use of the trace flag 3608 where it only recovers the master database.

     

    1. Go to cmd prompt and issue NET STOP MSSQLSERVER

    2. Start SQL server in single user mode / maintenance mode with the following trace flags NET START MSSQLSERVER /c /c /T3608

    3. Now connect to the SQL engine with new query button on the SSMS and issue the following command – here you may uncomment the rest of the parameters and use the best one for your scenario

    USE master;

    GO

    ALTER DATABASE model

    MODIFY FILE

    (

        NAME = modellog,

        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.ldf',

        SIZE = 50MB)

        --MAXSIZE = 100MB,

        --FILEGROWTH = 5MB

     

    4. Now stop your SQL server – NET STOP MSSQLSERVER

    5. Again start your SQL server with normal mode NET START MSSQLSERVER

     


    Thanks, Leks
    Tuesday, August 17, 2010 2:36 AM
    Answerer

All replies

  • Do you apply the latest (SP3) service pack on the server? If not, start from that.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, August 16, 2010 4:42 PM
  • Thank you.

    Yes, I did apply all latest service packs.

    Monday, August 16, 2010 4:50 PM
  • Can you locate where are all your databases right now and do you have space available (and how much ) on that drives?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, August 16, 2010 5:02 PM
  • I'll check the exact amount of space when I get home, but I know that this partition is about 120 Gb and less than a half full.
    Monday, August 16, 2010 5:07 PM
  • You might SQL Server in SINGLE USER mode before you are able to fix the model database.

    Thanks.

    Monday, August 16, 2010 5:24 PM
  • Thank you, I'll try all this.

    How do I put SQL Server in the SINGLE USER mode? Never done this before.

    Monday, August 16, 2010 5:29 PM
  • With SQL Servers not running.

    Bring up command prompt.

    Navigate to where SQL Server Binaries are installed (Usually in C:\Program Files\Microsoft SQL Server\MSSQL.1\Binn\)

    Run following command :

      sqlservr.exe -m

    Leave the command window open.

    Go to another command window and connect using SQLCMD to extend the t-log on model db or to do backup. 

     

    But I am curious on how the model db t-log got full anyways; normal operations should have nothing going to model ever.

     

    Monday, August 16, 2010 5:51 PM
  • Found this thread http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/17c2744c-2e18-45c8-8b88-0e0b5db6461c that may be of some help.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, August 16, 2010 6:09 PM
  • Thank you, I'll try all this tonight.
    Monday, August 16, 2010 6:12 PM
  • From your thread this might help as well:

    "Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it."

    This is the strange thing I've noticed: The SQL Server Agent is running, although the SQL Server iself is not. Usually the Agent cannot run without SQL Server.

    Monday, August 16, 2010 6:16 PM
  • The reason SQL Server is not starting is because tempdb cannot be created.

    Tempdb cannot be created because of the issue with model; tempdb is cloned from model on startup.

    You will need to restart SQL Server with trace flag 3609 to bypass tempdb creation.

    This will allow the server to start, you can then resize the model db log file, remove the trace flag and restart SQL Server.

     


    ajmer dhariwal || eraofdata.com
    Monday, August 16, 2010 9:46 PM
    Answerer
  • Thank you very much for all your suggestions.

    As you advised,  I started the SQL Server from the command line with the trace flag 3609. Then I truncated all my logs and was able to start the server normally. It's working now.

    Peter

    Tuesday, August 17, 2010 1:48 AM
  • You can also make use of the trace flag 3608 where it only recovers the master database.

     

    1. Go to cmd prompt and issue NET STOP MSSQLSERVER

    2. Start SQL server in single user mode / maintenance mode with the following trace flags NET START MSSQLSERVER /c /c /T3608

    3. Now connect to the SQL engine with new query button on the SSMS and issue the following command – here you may uncomment the rest of the parameters and use the best one for your scenario

    USE master;

    GO

    ALTER DATABASE model

    MODIFY FILE

    (

        NAME = modellog,

        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.ldf',

        SIZE = 50MB)

        --MAXSIZE = 100MB,

        --FILEGROWTH = 5MB

     

    4. Now stop your SQL server – NET STOP MSSQLSERVER

    5. Again start your SQL server with normal mode NET START MSSQLSERVER

     


    Thanks, Leks
    Tuesday, August 17, 2010 2:36 AM
    Answerer
  • Thank you, yes, this could be another solution.
    Tuesday, August 17, 2010 2:54 PM
  • Cheers! Meh I don't know how I forgot about the trace flag sorry.  I was thinking there might be something needed.  Glad to hear you got it working :)

    Tuesday, August 17, 2010 3:05 PM