How to Decrease the Size of My SQL Server Log File

How do I decrease the size of my sql server log file?

Welcome to the fickle world of SQL Server log management.

SOMETHING is wrong, though I don't think anyone will be able to tell you more than that without some additional information. For example, has this database ever been used for Transactional SQL Server replication? This can cause issues like this if a transaction hasn't been replicated to a subscriber.

In the interim, this should at least allow you to kill the log file:

1. Perform a full backup of your database. Don't skip this. Really.
2. Change the backup method of your database to "Simple"
3. Open a query window and enter "checkpoint" and execute
4. Perform another backup of the database
5. Change the backup method of your database back to "Full" (or whatever it was, if it wasn't already Simple)
6. Perform a final full backup of the database.

You should now be able to shrink the files (if performing the backup didn't do that for you).

Good luck!

How to reduce SQL log file size

Check out this post

In the interim, this should at least allow you to kill the log file:

1. Perform a full backup of your database. Don't skip this. Really.
2. Change the backup method of your database to "Simple"
3. Open a query window and enter "checkpoint" and execute
4. Perform another backup of the database Change the backup method of your database back to "Full" (or whatever it was, if it wasn't already Simple)
5. Perform a final full backup of the database.

How to reduce log file size of MS SQL database

How to reduce log file size of MS SQL database

Shrinking is the only option here, but you need to clear out the log by backing it up first. Otherwise, there won't be any free space to shrink. After you shrink it once, you can keep it from getting blown out by either

• Backing up your transaction logs more regularly. How often depends on your RPO
• Setting the database to Simple Mode. This is only suggested if your RPO is large enough to where your FULL BACKUPs and DIFFERENTIALS would cover it.

.ldf File Shrink or Reduce Size - Recommended Solution

1) What recovery model is your database?
1.1) If its FULL recovery model, then are doing Transaction Log backups?
If not then thats the reason why your logs are growing.
You can either make the transactional log backups, or change the recovery
mode to Simple and release the empty space.

Do not shrink your files.

How to reduce database log file size frequently?

Check this article.

According to that article the transaction log backup is the only way how to reduce the size of the transaction log in the FULL recovery mode.

Shrinking will help only in a situation when you for example have forgotten to take transaction log backups and your transaction log file grows and then you take the backup and you need to shrink the physical log file size once so that it is not extra huge. But shrinking will not delete any transaction log data, only shrinks empty space allocated in the file.

So shrinking the transaction log file frequently is usually not a good idea.

See technet:

Shrinking database and log files can be set to occur automatically.
However, we recommend against automatic shrinking, and the autoshrink
database property is set to FALSE by default.

On the other hand taking transaction log backups frequently when your database is in FULL recovery mode is a good idea.

If you do not need FULL recovery mode with transaction log backups, you can also switch your database to SIMPLE recovery mode. In a SIMPLE recovery mode you do not need to backup transaction logs, but in case of disaster you need to use your last full backup.

---

Related Topics