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
DBCC SQLPERF(LOGSPACE)
select ceiling((sum(size) * (8192.0/1024.0)) / 1024.0) from dbo.sysfiles
exec sp_spaceused
DBCC SQLPERF(LOGSPACE)

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

BACKUP LOG MODEL WITH TRUNCATE_ONLY

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)