8

Is there any special procedure to clear up large log files in Sitecore databases? Our integration server has a huge (25GB) log file which I would like to dispose of, but not sure of the procedure.

On an aside, is a very large log file symptomatic of any particular problems?

Improve this question
1
  • Nobody's said it explicitly yet, so I will: YOU MUST NOT DELETE THIS FILE. Log files are of critical operational importance to a SQL database. This is not a log in the sense of the Sitecore log, this is how SQL Server records database operations required for atomic transactions, backup and replication. In "Full recovery" mode, you will require both the data and log backups to restore (logs provide incremental backup/recovery). In "Simple recovery" mode you only need the data backup.
    – Richard Hauer
    Sep 15 '16 at 13:20
22

A very large log file is symptomatic of the problem of no backups taking place. Under normal circumstances SQL log files get truncated on backup.

It is quite normal that a CI environment gets quite many updates, and if your SQL is running in "Full" mode, this will grow your transaction log.

I would propose setting it to "simple". It will be slightly quicker and you'd not have unlimited growth.

Full Recovery Model without log backups is bad.

So, that's the most common reason for uncontrolled log growth? Answer: Being in Full Recovery mode without having any log backups.

This happens all the time to people.

Why is this such a common mistake?

Why does it happen all the time? Because each new database gets its initial recovery model setting by looking at the model database.

Model's initial recovery model setting is always Full Recovery Model - until and unless someone changes that. So you could say the "default Recovery Model" is Full. Many people are not aware of this and have their databases running in Full Recovery Model with no log backups, and therefore a transaction log file much larger than necessary. This is why it is important to change defaults when they don't work for your organization and its needs)

Source: https://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space

Improve this answer
1
  • Simple mode would be my recommendation as well - along with normal backups (several times a day depending on the frequency of edits).
    – sestocker
    Sep 14 '16 at 13:36
6

It depends how you've set up your database. You can set the recovery model to simple to get rid of most of the log file content. Go to the database with SQL Management Studio, right click on the database and select properties. In the popup, select the Options tab and change the recovery model accordingly.

Alternatively, you can do this with a SQL command as well:

ALTER DATABASE YOURDB SET RECOVERY SIMPLE;

If you don't want to fiddle with the recovery model, then you'll need to take a backup to flush the database transaction log.

Afterwards, you may need to shrink the database.

DBCC SHRINKDATABASE (YOURDB, 10);
GO
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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