Sql-server – Log file shrinking automatically due to UserShrinkSize property

sql serversql server 2014transaction-log

We have a SQL Server 2014 system that is apparently shrinking one or more log files automatically. We are getting a low free disk space alert (triggered at <5% free) at 1:00 AM, but when we check the system in the morning there is about 50% free. We manage about 160 SQL Servers between the various environments and they're all set up with a good degree of standardization. Auto shrink is NOT enabled. I have found what was causing all of the database writes to cause the log files to grow, and that has been addressed, so the cause of the log file growth is not the subject of this question.

I found this SQL Server 7 era post, Transaction log shrinking mysteriously, which states that transaction logs will be shrunk automatically if UserShrinkFile is set to anything other than -1. This seems so explain what we've noticed, but with a bit of a wrinkle. There is no UserShrinkFile property in the header, but there is a UserShrinkSize property, and on three of the databases it is set to a positive integer.

Oddly, I cannot find anything salient about UserShrinkSize. Anywhere. The only place I've found reference to it is posts that just list it along with everything else in the header. I assume it is the newer version of UserShrinkFile, and that it works that same way as stated in the linked article above. However, getting some confirmation of these assumptions would be fantastic, and this would be a lovely addition to utilities that check for configuration items that might cause unwanted behavior.

It would be good to know how this property affects the system, as it obviously doesn't just shrink the log files immediately all the time. Right now all three of the databases that have a positive UserShrinkSize value have log files ranging from 40 – 200 GB with 99% free space, so it's leaving me wondering whether it only shrinks them when it can't grow the log file.

Best Answer

I couldn't add a comment so I will try to comment here:

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. Maybe you are confused about log shrinking and log clearing?..

Update:

I was able to reproduce the positive value on UserShrinkSize column. The value on UserShrinkSize is the number of (8kb) pages needed to be shrink. -1 means default (none). So when you see a positive value, that means it tries to shrink the log but it can't because there's active transaction going on.

Tested on SQL Server 2016:


USE master
GO
DROP DATABASE IF EXISTS [UserShrinkSizeTest];
GO
CREATE DATABASE [UserShrinkSizeTest]
 ON  PRIMARY 
( NAME = N'UserShrinkSizeTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER16\MSSQL\DATA\UserShrinkSizeTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'UserShrinkSizeTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER16\MSSQL\DATA\UserShrinkSizeTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 8192000KB )
GO
ALTER DATABASE [UserShrinkSizeTest] SET COMPATIBILITY_LEVEL = 130
GO
ALTER DATABASE [UserShrinkSizeTest] SET RECOVERY FULL
GO

Build your table:


USE [UserShrinkSizeTest];
GO

DROP TABLE IF EXISTS [dbo].[RecordsToProcess];
GO
CREATE TABLE dbo.RecordsToProcess
(AccountID char(8000)
,UserID int
,ServerID int
,HomeID int
,UserTypeID int
,UTCDateCreated DATETIME2(7))
GO

open a new query window and execute the insert:


USE [UserShrinkSizeTest];
GO

BEGIN TRAN
INSERT INTO dbo.RecordsToProcess (AccountID, UserID, ServerID) VALUES (REPLICATE('abcd', 5000000),1,1)
GO 50000

While the insert window is executing, open another window for shrink and execute it at the same time:


USE [UserShrinkSizeTest]
GO
DBCC SHRINKFILE (N'UserShrinkSizeTest_log' , 80)
GO

Now, you can check the UserShrinkSize:


DBCC TRACEON(3604)
DBCC fileheader([UserShrinkSizeTest]) 
--DBCC PAGE ([UserShrinkSizeTest],1,0,3)
DBCC TRACEOFF(3604)

as you can see below, the value is now positive. 65024 pages needed to be shrink. enter image description here

you will see the change in value when you issue the SHRINKFILE and the t-log can't be shrink due to active transaction.

enter image description here

you can also check the changes using the fn_dblog function:


USE [UserShrinkSizeTest]
GO
SELECT Operation, Context, [Description] FROM fn_dblog(NULL,NULL)
WHERE [Description] LIKE '%Shrink%' AND Context = 'LCX_FILE_HEADER'

enter image description here

There is no magic about the UserShrinkSize, the value have changed because we issued/attempted DBCC SHRINKFILE during active transaction. Before shrinking the log, make sure to check the log_reuse_wait_desc and see what's holding the t-log. Investigate on why the t-log did not clear and why t-log expanded unexpectedly.

We should not worry about the UserShrinkSize value rather we should focus on how to properly manage the t-log. properly size the log, check long running transaction that cause log to grow, etc. If we take care of t-log the value will always be -1. :)

To bring back the value to -1, from my example I need to rollback the inserts. Then issue a SHRINKFILE on t-log (pre-size it to an appropriate size) and the log_reuse_wait_desc is now showing as NOTHING. enter image description here

HTH

Related Question