Big size of database logs SQL Server 2008

Question

I have a database which is running under Microsoft SQL Server 2008. Now, I have seen that the log of the database (the .ldf file) is growing to big size. The database file (.mdf) has a size of 630MB and the log file has a size of 12GB. I wonder what the reason for this can be. Is there a tool which let me seeing into the log where I can see, what is the reason for this big size?

What can I do to prevent that the log is growing to this big size?

Answer 1

You need to do log backups at least daily (we do them every 15 mintues). Read in books online about how to backup the log (not the daatbase, the log, these are two separate types of backups) and you should find directions for how to truncate the log without backing it up which you will likely need since you have let it get this size. The make sure you have a current backup of the database before you start. Then truncate the log and immediately set of regularly schedueld backups so this doesn't happen again.

Answer 2

This may be caused by

1. open transaction that you can see it by @@TRANCOUNT or DBCC OPENTRAN.

2. you have many CRUD operation in your database so you database log grows too much.

If you don't need your log file you can detach your database and then rename your log file and try to attach again the database and it SQL Server will create new log file.

Answer 3

When was the last time you did a transaction log backup on your database? Here's a quick way to find out:

select top 1
    b.database_name, 
    b.backup_finish_date,
    d.recovery_model_desc
from msdb.dbo.backupset b
inner join sys.databases d
on b.database_name = d.name
where b.type in ('l')
and b.database_name = 'YourDatabase'    -- replace with your database name
order by b.backup_finish_date desc

What this does is gets the date of the last transaction log backup. I also included the recovery model just so you have verification of the recovery model.

The moment that you take a full database backup of a database in Full Recovery Model, the transaction log will grow and only truncate when you backup the transaction log. If you do not take a transaction log backup, then your t-log will grow until it reaches max size or consumes your available disk space (whichever comes first).