How to shrink a large modellog.ldf file

Problem: My disk space is full on my server.  My SQL Server modellog.ldf is over 8 GB each and growing exponentially.  What should I do?


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.


To simply shrink the file, do the following:


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


Then you can run the database shrink command :


ALTER DATABASE model SET RECOVERY SIMPLE
GO
USE model
GO
DBCC SHRINKFILE('modellog', 2)
GO