0

I have a database on SQL Server Express used for 3 Asp.net applications. The Recovery Model is Full.

Every day I execute a backup database and log with this code

BACKUP DATABASE @databaseName TO DISK = @file WITH INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
BACKUP LOG @databaseName TO DISK = @fileLog WITH NOFORMAT, NOINIT,  NAME = N'Database-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

When the Start Mode of Application Pools is on OnDemand the LDF is reduced every day; when I use AlwaysRunning the log is not reduced.

I want to use application pools with always running. Can I use a shrink to reduce log size? Even if users are using websites?

ALTER DATABASE [DatiMachinaWeb] SET RECOVERY SIMPLE
DBCC SHRINKFILE(DatiMachinaWeb_log)
ALTER DATABASE [DatiMachinaWeb] SET RECOVERY FULL

Am I wrong? How can I solve it? Thanks

Hamada
  • 125
  • 1
  • 7
S.A.
  • 1
  • 1
  • Why are you toggling between FULL and SIMPLE recovery and shrinking the log file? The normal practice is to keep the database in FULL and backup the transaction log periodically between database backups. This will facilitate point-in-time recovery and manage log file size by removing committed transactions from the log. – Dan Guzman Jul 02 '20 at 10:47
  • When I used Application Pools with OnDemand the log was reduced every day. Since I use Application Pools with AlwaysRunning the log has never been reduced. I am using the same SQL Queries and same frequency. – S.A. Jul 02 '20 at 12:52

1 Answers1

2

Can I use a shrink to reduce log size? Even if users are using websites?

No, users will be blocked while the shrink operation is occurring. Plus, you'll have to do it over and over again unless you address the root cause of the growth.

For a really thorough treatment of why the log file keeps growing, see Why Does the Transaction Log Keep Growing or Run Out of Space?

When your websites are always running, you're producing more log records than the size of the log file in a single day, resulting in the file growth.

You have a few options:

  • Leave the database in the SIMPLE recovery model

With this approach, you don't take log backups at all. You take the full backup once per day, as you always do. If there were a crash or some other need to recover from a backup, you could lose up to one day of data.

ALTER DATABASE [DatiMachinaWeb] 
SET RECOVERY SIMPLE;
  • Take log backups more often

You can take as many log backups as you like between full backups. Taking a log backup will free up space in the log (referred to as "truncation") and prevent the log file from growing. This is really the best, and most common, option for dealing with log file growth.

  • Permanently increase the log file size to accommodate the once-per-day log backups

The default log file size in SQL Server 2019 is 8 MB. If your system does more than 8 MB worth of transactions in a day, that log file will grow (by default, it will grow by 64 MB).

Assuming the amount of transactions your system does is somewhat consistent from day to day, the growth of the log file will eventually normalize. After the first growth event, the log will be 72 MB - if you don't do more than 72 MB of transactions in a day, the log file should stay that size.

  • Lower likelihood: investigate long-running transactions

It's possible that your websites are causing the log file to not be truncated when they are in this "always running" mode. Maybe they start a transaction on startup, and don't commit or rollback until they shut down.

If the log file continues to grow, even with frequent log backups or a sufficiently large log file, you can run this query to see what is preventing log file truncation:

SELECT 
    d.[name], 
    d.log_reuse_wait, 
    d.log_reuse_wait_desc
FROM sys.databases d
WHERE 
    d.[name] = 'DatiMachinaWeb';
Josh Darnell
  • 29,228
  • 5
  • 65
  • 120