Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
1  
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
add comment

migrated from stackoverflow.com Mar 19 '12 at 15:11

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

3 Answers

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
add comment

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
add comment

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).

share|improve this answer
add comment

Your Answer

 
discard

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.