0

I host website and sometimes get an error: The log file for database '%.*ls' is full. and have to contact my hosting provider for cleaning database log file.

How I can disable logging or configure it to be sure that log file will not fill up?

(I am using Community Server engine so sql optimization must not be done.)

0

2 Answers 2

4

The log file is dependent on the database recovery model. The default is Full which means you'll be doing backups of the database and transaction log. When you backup the transaction log, it will truncate. If you never backup the transaction log, it will grow forever. If you only want to backup the full database, then change the recovery model to simple.

Once this is changed you'll want to shrink the log files (right-click database in sql management studio, Tasks > Shrink > Files). You can set them to auto-shrink but once you've fixed the underlying recovery model problem you may not need to.

2
  • Well said. I'd recommend updating the stats after the file shrink, as it can be heavily fragmented afterwards. I'd also recommend against auto-shrink. If the recovery model is still set to full, the log file will stay nice and small -- depending on the frequency of the transaction log backup. More frequent transactional backups = tidier and smaller log file.
    – Mike L
    Jan 28, 2010 at 16:59
  • 1
    I'd also recommend against auto-shrink. I've never seen it cause anything but problems - it shrinks both the database and log files, leaves almost no free space for growth, and tends to perform shrinks at inopportune times. The rest of Sam's advice is good, however. Jan 28, 2010 at 17:06
2

This is referring to the transaction log, not an error log, and that cannot be disabled on SQL Server. Every transaction writes to the transaction log before writing a change to the database itself to protect the integrity of the database. If the database is using the full recovery model (which it sounds like yours is), the transaction log records will only be overwritten after a transaction log backup (not a full backup); if you change the recovery model to simple, the log records are kept for a shorter period of time, and transaction log backups are not required. Depending on your hosting provider, however, this may be out of your control; you would have to check to see how much control they give you over the database settings, and request the change if you don't have direct control.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .