How to control SQL Server database log file size?

Question

We are using MSSQL Server and our application has heavy data exchange every day; approx. 20K rows new data per day and the database “Log file size” keeps increasing likewise. We delete the data after processing to keep the DB size under control, however the “DB Log file” keeps on increasing.

We are manually executing following script to shrink “DB Log file” size. Also only DB owner can execute this process.

Is there permanent fix to auto-control the “DB Log file” size? Let’s say, we want it to allocate 4GB max. It should auto-wipe out the older logs.

USE db1;
GO

ALTER DATABASE db1
SET RECOVERY SIMPLE;
GO
--first parameter is log file name and second is size in MB
DBCC SHRINKFILE (db1_log, 999);

ALTER DATABASE db1
SET RECOVERY FULL;
GO

Regards, Raj

Answer 1

I see this:

SET RECOVERY FULL;

In FULL recovery mode, a full database backup does not include the transaction log. Additionally, the server will not under any circumstances remove log entries that are not backed up. This means the log file will just keep growing an growing.

Is there permanent fix to auto-control the “DB Log file” size?

Yes. To fix this problem, you need to do separate Transaction Log backups... and do them often. This will allow the log file to stay at a reasonable size... probably much less even than your 4GB.

Be careful. The first time you take a backup after making the change, you can get a rather large backup file that might take a while to finish and put significant load on the database. But after that first run, things will quiet down if you've chosen a reasonable schedule.

Additionally, once you complete your first transaction log backup on the new schedule, you can finally shrink your transaction log file. There is no point in shrinking the file before then... it's still using all that space, and even if it weren't it would just grow large again. Once you'd done this initial shrink, you really shouldn't do it again. You want your maintenance schedule set up so you don't ever need to manually shrink the file, and you only even shrink it this time to get back to a reasonable state after the earlier mistake.

Answer 2

There are 2 ways how SQL Server clear the t-log:

1. database using SIMPLE recovery model - when a checkpoint occurs, log will clear

2. database using FULL or BULK_LOGGED recovery model - when you issue a log backup

The only time the t-log will shrink if you issue SHRINKFILE.

You can pre-size your log file to 4GB and auto-growth to 4GB example.

USE [master]
GO
ALTER DATABASE [DB] MODIFY FILE ( NAME = N'DB_log', SIZE = 4096000KB , FILEGROWTH = 4096000KB )
GO

Your file growth should be set to In Megabytes and not In Percent.

you can limit the file growth to a fix size but I would not recommended to set this. this might stop the application from inserting once you reach the size.

If you can tolerate 1 day of downtime/data loss, SIMPLE recovery will work for you. But if you have a 24/7 shop and needs to recover point-in-time, you need to set your database to FULL recovery (log backups needed to cleanup your t-logs) and ask you business about RPO/RTO requirements.