1

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.

2

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

  • Nope, I'm quite clear on that. The disk was almost full, nobody shrunk the files manually, there are no jobs to shrink them, and then the disk was magically 50% free again. It did this twice. I had no explanation until I found the post linked in my question, which seems to document the same behavior. – Tony Hinkle Dec 13 '17 at 2:49
  • 1
    I fixed the link in the post--check it out. – Tony Hinkle Dec 13 '17 at 2:50
  • From what i know, the only way to reduce the t-log size is issuing DBCC SHRINKFILE. are you the only DBA that have access/permission to shrink the file? any 3rd party tools that might execute shrink? remote job? schedule job? what's the compatibility level of your databases are all in 120? – user37701 Dec 13 '17 at 3:59
  • the default UserShrinkSize is 4294967295. i check it on 2012, 2014, 2016 when executing the DBCC PAGE. On DBCC FILEHEADER this is set to -1 by default i think. don't worry about that. if you think something is not right in the file header, the best course is to file a Microsoft support case because these are internals and undocumented, they should be able to help you. HTH – user37701 Dec 13 '17 at 4:48
  • Thanks--I was hoping that maybe one of the Microsoft engineers that post here might be able to find out and post an answer. I will try the method described in the post I linked to to see if that resets it back to -1. – Tony Hinkle Dec 13 '17 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.