SQL Server – Unable to shrink Transaction Log file

Background

Received an alert stating that we have gotten below our threshold of 10% on one of DB Servers.

Thanks goodness no late night phone calls.

Only saw it coming to work this morning.

The good and bad about smartphone.

TroubleShooting

Windows Desktop

Folder

Image

Explanation

  1. ppsivr_8.ldf is 28 GB

SQL Metadata

Outline

Here are dynamic management views ( DMVs) that we will access…

  1. dbcc sqlperf(logspace)
  2. sys.database_files
  3. sys.databases
  4. sys.dm_exec_requests & sysprocesses

 

Review Transaction Log File Sizes

SQL


dbcc sqlperf(logspace)

Output

 

Explanation

  1. ppsivr
    • Log Size MB
      • 27,699 MB
      • 27 GB

Review Database File Sizes

SQL


-- replace with targeted database
use [database]

SELECT 
		  [name]

		, [type]
			= tblSDF.[type_desc]

		, [filename]
			= tblSDF.[physical_name]

		, [allocatedMB]
			= tblSDF.size / 128

		, [usedMB]
			= CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)
				/ 128

		, [availableSpaceMB]
				 = ( size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) )
					/ 128

FROM sys.database_files tblSDF

order by
		tblSDF.size desc
;

Output

Review Database Transaction Log Re-usability State

SQL


declare @dbid   int
declare @dbname sysname

set @dbname = 'ppsivr'
set @dbid = db_id(@dbname)

select 
		    tblSD.[database_id]
		  , tblSD.[name]
		  , tblSD.[log_reuse_wait_desc]

from   sys.databases tblSD

where  (
			   (@dbid is null)

			or ( tblSD.[database_id] = @dbid)
		
	   )

Output

Explanation

  1. log_reuse_wait_desc
    • The database is not re-cycling its transaction log due to an ‘Active Transaction’

Review Current DB Requests

Code



declare @dbid   int
declare @dbname sysname

set @dbname = 'ppsivr'
set @dbid = db_id(@dbname)

select 
		  tblSDER.[session_id]

		, [database] 
			= db_name(tblSDER.[database_id])

		, [openTransaction]
			= tblSP.[open_tran]

		, tblSDER.[status]

		, tblSDER.[command]

		, tblSDER.[percent_complete]

		, tblSDER.wait_type

		, tblSDER.last_wait_type

		, tblSDER.[reads]

		, tblSDER.[writes]

from   sys.dm_exec_requests tblSDER

inner join master..sysprocesses tblSP

	on tblSDER.[session_id] = tblSP.[spid]

where  (

	         ( tblSDER.[open_tran] != 0 )

	      or ( tblSDER.[database_id] = @dbid)

      )



Output

Explanation

  1. User Transactions
    • It does not appear that we have user transactions that are current using our targeted database
  2. Background
    • WAIT_XTP_OFFLINE_CKPT_NEW_LOG
      • It does does not apply that WAIT_XTP_OFFLINE_CKPT_NEW_LOG updates the percent_complete column as so it is a bit difficult to track its current progress

 

Remediation

Here are the things we tried:

  1. dbcc shrinkfile
  2. Take database offline and bring back online

Attempt to Shrink Transaction Log Files

SQL


use [ppsivr]
go

dbcc shrinkfile('ppsivr_log')
go

Output

Explanation

  1. We want to take a good look at Current Size and Used Pages
    • In our case they are same
    • What is in our Transaction Log File?

 

Take Database Offline and bring it back online

Force termination of ongoing sessions

SQL


use master
go

ALTER DATABASE [ppsivr]
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	;

alter database [ppsivr] set offline;

alter database [ppsivr] set online;

ALTER DATABASE [ppsivr]
	SET MULTI_USER WITH ROLLBACK IMMEDIATE
	;

 

Summary

But, nothing worked!

We will come back and talk about what worked.

References

  1. Microsoft
    • Support.Microsoft.com
      • FIX: Offline checkpoint thread shuts down without providing detailed exception information in SQL Server 2014
        Link

Microsoft – SQLServer – Database \ Datafile Management – Data\Log File Shrinking

Microsoft – SQLServer – Database \ Datafile Management – Data\Log File Shrinking

Sometimes one ends with MS SQL Server Log \ Data files growing quite a bit.

This is especially true with log files.

The reasons for big log files are a bit plentiful:

  1. Un-committed Transactions
  2. Database Mirroring partner in-admissibility

Well, once the original reason why the database data\log file got so big is addressed; you may now be emboldened to shrink the data\log files.

What to do, how to address ?

You can try using dbcc shrinkfile.

Over the years, MS has done quite a bit of Engineering on it.

Some of those enhancements are brought to light by Bob Dorr.  At the time of his Blog

posting, Bob worked has a “Senior SQL Server Escalation Engineer” in the “Microsoft

Customer Service and Support (CSS) SQL Escalation Services” group.

The areas that were most relevant to the issue that I was addressing (at the time) were:

1) Increase DBA visibility.  As DBCC Shrink works it avails its status via the sys.dm_exec_requests; specifically the command column

a] DbccSpaceReclaim – Clean up deferred allocations and purge empty extents
preparing for data moves.

b] DbccFilesCompact – Move pages beyond the target to before the target and
truncate file as required.

c]  DbccLOBCompact – Compacting the LOB data

2) Progress data is also visible in the same DMV (sys.dm_exec_requests).  The

columns\attributes shown are percent_complete, estimated_completion_time,

cpu_time, total_elapsed_time.

3) The shrinkage is performed & committed in batches.  This is beneficial in terms of if

you are a bit queasy or frustrated and terminated your work, you do not lose everything.

The current unit of work is 32 pages.

As named transactions are used, you can follow along by querying the

sys.dm_tran-active_transactions view.

4) As “DBCC Shrinkfile” is a physical operation, it directly interacts with the Hardware;

specifically the Storage Sub-system.  As it moves data around, other operations are quite

impacted and so if possible suspend other I/O “aggressive” operations.

5) Also, DBCC Shrinkfile is single-threaded and non-reentrant.  That is for each database,

only a single dbcc shrinkfile operation can be on-going at any one time.

References:

1) How It Works: SQL Server 2005 DBCC Shrink* May Take Longer Than SQL Server 2000
http://blogs.msdn.com/b/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx