Howto: Fix an oversized modellog.ldf

modellog.ldf too big?

First try to figure out what’s using the log file:

use [model]
select (sum(size) * (8192/1024)) / 1024 from dbo.sysfiles WHERE (status & 0x40) <> 0
select ceiling((sum(size) * (8192.0/1024.0)) / 1024.0) from dbo.sysfiles
exec sp_spaceused

It is very unusual that the model database data or log files would grow at all. The database is normally used only as a template for creating new databases. Make sure you don’t have any renegade applications using the database.

You may need to back up the transaction log before shrinking.
Then backup log again with the truncate only option i.e


Sometimes modellog.ldf can get to be huge… like 14GB huge.

Here’s a safe way to fix it (i’ve ran this on production servers before):

dbcc sqlperf(logspace)
backup log model with no_log
use model
dbcc shrinkfile(modellog, 0)

1 comment to Howto: Fix an oversized modellog.ldf

Leave a Reply

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>