My log file is too large

Question

My log file in SQL Server used up all the space on my disk. I run full backups every night, but the log file keeps growing. What can I do?

Answer 1

Delete the log file?

Edit: Apparently deleting the log file is bad, it's just just a log to SQL server. I'm leaving this up to reiterate what not to do.

Answer 2

You could use some kind of log-rotation, and keep only log of a fixed amount of time, say the last 7 days. This should be more than enough. Or you could reset the log every night, because you should have it in your backup.

Answer 3

On most busy systems, you need to look at scheduling log backups throughout the day, and then your nightly full backup. This is pretty common practice.

Answer 4

You have to backup up your logs as well as the main database

Answer 5

This should shrink it:

dbcc shrinkfile('databasename_log', 0)

Answer 6

try this:

dump transaction <dbname> with no_log

and then shrink the logfile by setting the autoshrink option in the sql server settings or by.

I think you can use dbcc to shrink it too, but i dont remember the syntax.

Answer 7

Hi,

If you have a job scheduled to conduct full backups this is good and should be your starting point however, you also need to be carrying out regular transaction log backups.

Backing up the transaction log causes the space to be relcaimed. Once you have defined your regular transaction log backup schedule, you will likely be in a potition to consider shrinking the transaction log to a more apporpriate size. As it will no longer be growing indefinately.

The backup strategy for full recovery consists of:

* Database backups.

* Differential backups (optional).

* Transaction log backups.

I suggest you consult the following Microsoft reference.

http://msdn.microsoft.com/en-us/library/aa173551(SQL.80).aspx

Answer 8

In some cases you might find the the logfile will not properly truncate even though a log backup is run. You can do a backup with TRUNCATE_ONLY to check it. When you run this it should truncate the transaction log:

BACKUP LOG dbname WITH TRUNCATE_ONLY

The cause of this problem is an open transaction in an earlier part of the log. SQL will not truncate the log past this transaction, potentially causing a large, ever increasing log. You need to find out which transactions are left open and why. You can monitor your log space with:

DBCC SQLPERF (LOGSPACE)

Information on long running transactions can be found using:

DBCC OPENTRAN

Or:

select * from sys.dm_tran_database_transactions