none
Size of log file in Model database is Huge 300GB

    Question

  • MS SQL 2005 Ent

    Hi There,

    Size of log file for Model database is more than 300 GB.


    for other databases I know for regular databases if I detach them and delete the log file and reattach them if will fix the issue but for Model database I have no clue what shall I do

    Please advise how can I reduce the size of the log file.

    Thank you in Advance

    Tuesday, December 17, 2013 3:05 PM

Answers

  • There is the instruction below. what is your recommendation?

    1. ALTER DATABASE {your database} SET RECOVERY SIMPLE;

    2. CHECKPOINT -- possibly do this a couple of times to roll the VLF to beginning of file

    3. Shrink the log file using shrinkfile to a reasonable size

    4. ALTER DATABASE {your database} SET RECOVERY FULL;

    5. Perform a full backup now to reset the log chain and allow for transaction log backups

    6. Set up frequent log backups (every hour or two, or more often)

    http://www.sqlservercentral.com/Forums/Topic753854-146-1.aspx

    • Marked as answer by Alex_0s Wednesday, December 18, 2013 12:07 PM
    Tuesday, December 17, 2013 7:11 PM
  • -- Shrink Database Model Log File.

    USE model;

    GO

    -- Truncate the log by changing the database recovery model to SIMPLE.

    ALTER DATABASE model

    SET RECOVERY SIMPLE;

    GO

    -- Shrink the truncated log file to 1 MB.

    DBCC SHRINKFILE (modellog, 1);

    GO

    -- Reset the database recovery model.

    ALTER DATABASE model

    SET RECOVERY FULL;

    GO

    http://msdn.microsoft.com/en-us/library/ms189493.aspx

    • Marked as answer by Alex_0s Wednesday, December 18, 2013 12:09 PM
    Wednesday, December 18, 2013 12:09 PM

All replies

  • You can run the following command to reduce the logfile size.

    ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', SIZE = 50MB )


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Tuesday, December 17, 2013 3:20 PM
  • MS SQL 2005 Ent

    Hi There,

    Size of log file for Model database is more than 300 GB.


    for other databases I know for regular databases if I detach them and delete the log file and reattach them if will fix the issue but for Model database I have no clue what shall I do

    Please advise how can I reduce the size of the log file.

    Thank you in Advance

    Hello,

    First thing I assume your Model database is full recovery.

    Now Is it by any chance you were testing some thing and you selected model database and ran a query which created huge log.

    Below blog will be helpful

    http://shaunjstuart.com/archive/2011/02/the-mystery-of-the-model-database-transaction-log-growth/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, December 17, 2013 4:15 PM
  • If you are not concerned with losing the data within the log file then I suggest switching the database recovery model from FULL to SIMPLE. I'm assuming the recovery model for the DB is set to FULL which is a default setting. After you switch it to simple you can shrink the database log file down to more appropriate size.

    To find the current recovery model

    SELECT name, DATABASEPROPERTYEX(name, 'Recovery')
    FROM master.dbo.sysdatabases where name = 'Model'
    ORDER BY 1

    To change recovery model to simple

    ALTER DATABASE Model SET RECOVERY SIMPLE

    To shrink the log file of model. Note that the target_size is in MB

    DBCC SHRINKFILE (n'logical log file name', target_size)

    I hope this helps.

    Tuesday, December 17, 2013 4:59 PM
  • You should not use the model system database for anything.

    That is the prototype when a new database is created. Even tempdb is created from the model database.

    Log shrinking blog: http://www.sqlusa.com/bestpractices2005/shrinklog/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, December 17, 2013 5:03 PM
  • Hello

    Thank you so much for your reply

    please find the message below

    Msg 5039, Level 16, State 1, Line 1
    MODIFY FILE failed. Specified size is less than current size.

    Tuesday, December 17, 2013 5:28 PM
  • The model database is used as a template when you create new databases.  It should not be used for anything. 

    That message indicates you are using model for something.   The first thing you need to do is figure out what was incorrectly created in model and move it.  Then you can shrink it.

    Tuesday, December 17, 2013 5:37 PM
  • My backups are full for all of my databases and the SQL sever does it every night


    • Edited by Alex_0s Tuesday, December 17, 2013 5:53 PM
    Tuesday, December 17, 2013 5:50 PM
  • Hello Tom,

    Thank you so much for your reply.

    How can I find it out? there is not any table in the model database



    • Edited by Alex_0s Tuesday, December 17, 2013 7:00 PM
    Tuesday, December 17, 2013 6:40 PM
  • There is the instruction below. what is your recommendation?

    1. ALTER DATABASE {your database} SET RECOVERY SIMPLE;

    2. CHECKPOINT -- possibly do this a couple of times to roll the VLF to beginning of file

    3. Shrink the log file using shrinkfile to a reasonable size

    4. ALTER DATABASE {your database} SET RECOVERY FULL;

    5. Perform a full backup now to reset the log chain and allow for transaction log backups

    6. Set up frequent log backups (every hour or two, or more often)

    http://www.sqlservercentral.com/Forums/Topic753854-146-1.aspx

    • Marked as answer by Alex_0s Wednesday, December 18, 2013 12:07 PM
    Tuesday, December 17, 2013 7:11 PM
  • The model database is a system database, not subject to normal processing.  It normally has nothing in it.

    I would suggest setting it to "SIMPLE" recovery mode, shrink the database, then set it back to "FULL".   Then monitor it to see if it grows again.

    Tuesday, December 17, 2013 8:34 PM
  • -- Shrink Database Model Log File.

    USE model;

    GO

    -- Truncate the log by changing the database recovery model to SIMPLE.

    ALTER DATABASE model

    SET RECOVERY SIMPLE;

    GO

    -- Shrink the truncated log file to 1 MB.

    DBCC SHRINKFILE (modellog, 1);

    GO

    -- Reset the database recovery model.

    ALTER DATABASE model

    SET RECOVERY FULL;

    GO

    http://msdn.microsoft.com/en-us/library/ms189493.aspx

    • Marked as answer by Alex_0s Wednesday, December 18, 2013 12:09 PM
    Wednesday, December 18, 2013 12:09 PM
  • >6. Set up frequent log backups (every hour or two, or more often)

    Why would you do this for the model system database?

    Or we are talking about a regular database?   Thanks.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Wednesday, December 18, 2013 12:31 PM