Log file shrinking automatically due to UserShrinkSize property?
I couldn't add a comment so I will try to comment here:

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


database using SIMPLE recovery model - when a checkpoint occurs, log will clear
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.


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



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'




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.


HTH