Case study of SQL AlwaysOn transaction log file shrink

Case study of SQL AlwaysOn transaction log file shrink.

Usually backup transaction log will move the LVF head lower, make it possible to shrink transaction log, but with sqlalwaysOn configuration, I found that it not work as always, I need to specifically fill up the highest LVF so that transaction log head can move to lower side. This is the case study of that.

The following blog explained lazy truncation of sql AlwaysOn：

https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/27/lazy-log-truncation-clearing-of-sql-transaction-log-vlf-status-deferred/

To find out the file size:

SELECT DB_NAME(database_id) AS DatabaseName,

Name AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB

FROM sys.master_files

--WHERE DB_NAME(database_id) = 'AdventureWorks'

order by 4 desc

GO

We use Sp_CentralAdmin database as example to shrink logfile:

Use Sp_CentralAdmin

go

Dbcc loginfo

go

Noticed that all the LVF has status=2.

Though I run sqlserver transaction backup, it does not change from status=2 to 0.

Try to shrink the logfile, but the file size remain 259MB

USE [Sp_CentralAdmin]

GO

DBCC SHRINKFILE (N'Sp_CentralAdmin_log' , 1)

GO

Run dbcc loginfo again, noticed many LVF now has status=0, but the last LVF has status=2, it’s trouble maker, though I run sqlserver transaction log a few times, the last LVF fSeqNo=257 status remain 2, and file cannot be truncated.

I googled, there is no way to force sqlserver to switch to next LVF like oracle can do.

I will try to fill up this LVF, that way it should swtich to next LVF.

select a.*,b.physical_name into t from sys.databases a, sys.master_files b

DECLARE @cnt INT = 0;

WHILE @cnt < 1000

BEGIN

 update t set name='blabla'

 --select * from t

   SET @cnt = @cnt + 1;

END;

Noticed that there are more LVF status changed to 2, but the tail LVF remain 2

Now run another transaction log backup, and shrink logfile.

It worked. Only 6 LVF left. Log file is shrunk to 36MB

Checked secondary replica, the transaction logfile is also shrunk to same size.

The following are some queries I use…

drop table #t

create table #t (RecoveryUnitid bigint,FileId bigint,FileSize bigint,StartOffset bigint,FSeqNo bigint,Status bigint,Parity bigint,CreateLSN decimal(20))

insert into #t exec('DBCC loginfo')

select max((startoffset+filesize)/1024/1024) logfilesizeMB,count(*) NumLVF from #t

SELECT * FROM #t where status<>0

select max(startoffset+filesize)/1024/1024 as logfileheadMB, min(startoffset+filesize)/1024/1024 as logfiletailMB from #t where status <>0 

-- active LVF has status=2, other LVF can be reused and potentially shrunk , tail should be lower than head, the head is from where logfile can be truncated.

-- for SQLAlwaysOn because of lazy log truncation, check this link --  https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/27/lazy-log-truncation-clearing-of-sql-transaction-log-vlf-status-deferred/

--however, dbcc shrinkfile cannot shrink logfile sometimes, the last LVF is active though others has status =0 after a few transaction logfile backup.

select log_reuse_wait_desc,* from sys.databases -- if log_reuse_wait_desc=NOTHING, then the LVF can be re-used.

 --DBCC SHRINKFILE (AdventureWorks2012_log, 1)

 DBCC loginfo

 DBCC OPENTRAN

 select * from sys.dm_db_log_space_usage