Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Mar 19 '12 at 15:11

This question came from our site for professional and enthusiast programmers.

Perhaps you do not backup log - funny but full database backup does not backup log. Even if you do, it might be worthwhile to check this document. – Nikola Markovinović Mar 18 '12 at 18:25
up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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
from msdb.dbo.backupset b
inner join sys.databases d
on b.database_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).

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.