SQL log file not compressed in SQL Server 2012

I am dealing with someone else with a maintenance plan and have a problem with the log file, I have a database that is on one 31 GB drive and a log file that is on another 20 GB server, the database is in the full model recovery. There is a maintenance plan that runs once a day to perform a full backup, and a second plan that backs up the log file every 15 minutes. I checked the disk on which the log file was uploaded and there is still a lot of space, but the log file never gets smaller after backup, is there anything in terms of maintenance?

Thanks in advance

+4
source share
3 answers

The situation, as you describe it, seems beautiful.

Backing up the transaction log does not compress the log file. However, it truncates the log file, which means that the space can be reused:

From books on the Internet ( Trimming a transaction log ):

Log truncation automatically frees up space in the logical log for reuse in the transaction log.

In addition, from Transaction Log Management :

Log truncation, which is automatically under a simple recovery model, requires that the log is not populated. The truncation process reduces the size of the logical log file that is marked as inactive virtual log files that do not contain any part of the logical log.

This means that every time a transaction log is backed up in your script, free space is created in the file that can be used by subsequent transactions.

Following this, also need to compress the file? Generally speaking, the answer is no. Assuming your database does not suddenly have massive one-time bursts in use, the transaction log will grow to fit the typical workload.

This means that if you start to shrink the log, SQL Server just needs to grow it again ... This is intensive work with resources, affecting server performance, and no transactions can complete while the log grows.

The current size and file sizes seem reasonable to me.

+7
source

I don’t know if this applies to your situation, but earlier versions of SQL Server 2012 have an error that occurs when a simple recovery model is specified for the model. For any database created with the Simple model installed, the log files will continue to grow, trying to reach the 2.097.152 MB limit. This still applies if you subsequently change the value to Full. KB article 2830400 claims that changing to Full, and changing to Simple is a workaround - it was not my experience. Running CU 7 for SP1 was the only trick that worked for me.

The article provides links for the first updates that resolved this error: "Cumulative Update 4 for SQL Server 2012 SP1, as well as" Cumulative Update 7 for SQL Server 2012 "(if you did not install SP1).

+2
source

If you change the recovery to full, and then return to simple, compression will work successfully.

0
source

Source: https://habr.com/ru/post/1493445/


All Articles