SQL Server – Database Files – Minimum File Size

Background

There is a setting that is unfortunately difficult to determine within Transact SQL.

That setting is the smallest size a data or log file can be shrunk down to.

GUI

SQL Server Management Studio ( SSMS )

Database Property

Image

bigfix_Enterprise_initialSizes_20180711_1119AM

Code

Syntax


dbcc page
(
      {'dbname' | dbid}
    , filenum
    , pagenum
    [, printopt={0|1|2|3} ]
)

Explanation

  1. DBCC PAGE
    • Database
    • FileID
    • Pagenum
      • 0
    • printopt
      • 3 – page header plus detailed per-row interpretation

SQL

Data files

Code


set nocount on;
go

set XACT_ABORT on
go

declare @database		    sysname
declare @dbid			    int

declare @fileID			    int
declare @fileIDAsString		varchar(255)

declare @fileSymbolicName   sysname
declare @filePhysicalName	nvarchar(600)

declare @sql				nvarchar(255)

declare @FORMAT_SQL			varchar(255)

declare @tblDBCCPage Table
(

	  [parentObject] sysname not null
	, [object]		 sysname not null
	, [field]		 sysname not null
	, [value]		 varchar(255) null	

	, [computedInKB]
		as case
				when ([field] like '%size%') then
						[value] * 8

				else null
		  end

	, [computedInMB]
		as case
				when ([field] like '%size%') then
						(
							[value] * 8
						)
						/ ( 1024)

				else null
		  end
)

set @FORMAT_SQL = 'DBCC PAGE(''%s'',%s,0,3) with tableresults, no_infomsgs';

set @database = 'BFEnterprise';

set @fileID = 1
set @fileID = 1
set @dbid = db_id(@database);

select
		  @fileSymbolicName = tblSMF.[name]
		, @filePhysicalName = tblSMF.physical_name

from   sys.master_files tblSMF 

where  tblSMF.database_id = @dbid

and    tblSMF.[file_id] = @fileID

set @fileIDAsString = cast(@fileID as varchar(10));

if (@fileID is not null)
begin

	DBCC TRACEON(3604) with no_infomsgs;

	exec master..xp_sprintf
			  @sql output
			, @FORMAT_SQL
			, @database
			, @fileIDAsString

	print @sql

	insert into @tblDBCCPage
	(
		  [parentObject]
		, [object]
		, [field]
		, [value]
	)
	exec(@sql)

	-- exec sp_helpdb [model]
	select
		      [database] = @database
			, [fileSymbolicName] = @fileSymbolicName
			, [filePhysicalName] = @filePhysicalName
			, [parentObject]
			, [object]
			, [field]
			, [value]
			, [computedInKB]
			, [computedInMB]

	from   @tblDBCCPage tbl

	where  (
			( field in
				(
					'MinSize'
					, 'Size'
				)
			)

	DBCC TRACEOFF(3604) with no_infomsgs

end -- if (@fileID is not null)

Output

bigfix_Enterprise_Code_20180711_1130AM.png

Log File

Explanation

Unfortunately, same does not work for log files.

You will get the error pasted below.

Output


DBCC PAGE('BFEnterprise',2,0,3) with tableresults, no_infomsgs
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1585190943824412672 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -1.
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 13792724832485376 (type Unknown), page ID (2:3) contains an incorrect page ID in its page header. The PageId in the page header = (102:7274528).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 12947986374328320 (type Unknown), page ID (2:6) contains an incorrect page ID in its page header. The PageId in the page header = (117:7077985).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 9007714657435648 (type Unknown), page ID (2:7) contains an incorrect page ID in its page header. The PageId in the page header = (32:7536756).
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7036874954637312 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -14.

<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Crediting

Edward Dortland
File Header Properties
Link

Summary

Unfortunately, the word size is imprecise and thus need to be surrounded to facilitate context.

When Database properties are viewed through SSMS, for each data and log file, there is a number identified as “Initial Size“.

I think one has to be careful and read more into what it means and how it is sourced.

References

  1. Edward Dortland
    • Stack Overflow
      • File Header Properties
        Link
  2. Paul Randal
    • Server & Tools Blogs > Data Platform Blogs > SQL Database Engine Blog
      • How to use DBCC PAGE
        Link

SQL Server–Error–“Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space”

Background

Upon starting SQL Server, we received the error posted below.

Error

Image

msdb110_upgrade_20171003_1115PM (cropped up)

Textual

Msg 945

Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details

Troubleshooting & Remediation

Outline

  1. Preparation
    • Make a note of Service Accounts used for SQL Server Engine and SQL Server Agent
    • Identify Folder’s location and filenames for msdb data and log files
    • Identify location of msdb install files
      • instmsdb.sql
  2. Inspect
    • Review Accounts’ access
      • Review Accounts NTFS Permissions on msdb data and log files
  3. Get out the way
    • Stop MS SQL Server Engine and Agent services
  4. Get Set
    • Safe keep msdb files
      • Copy msdb data and log files to a neutral location
  5. Go!
    • Start MS SQL Server using trace files
      • Trace Flags
        • -T3608
    • Detach msdb database
      • Using SQLCMD or Management Studio, issue sp_detach_db ‘msdb’
    • Rename existing msdb data and log files
    • Create msdb database
  6. Rollout
    • Stop SQL Server
    • Remove SQL Server Trace Flags
    • Retrace Trace Flags
      • -T3608
  7. Restart SQL Server Engine
  8. Validate
    • Access msdb database

Actual Steps

Preparation

  • Make a note of Service Accounts used for SQL Server Engine and SQL Server Agent
  • Identify Folder’s location and filenames for msdb data and log files
  • Identify location of msdb install files
    • instmsdb.sql

Make Note of SQL Service Accounts

SQL Server Configuration Manager
Image

Explanation
  1. Services
    • SQL Server
      • Local System
    • SQL Server Agent
      • [domain]\svc_sql

 

Identify Folder’s location and filenames for msdb data and log files

Identify the folder where system databases ( master, model, and msdb ) are stored.

Dynamic Management Views

If SQL Server is running in normal mode or within trace flags ( -T 3605 ), please get folders and names of msdb files.

SQL
select 

          [dbid] 
            = tblSMF.database_id
            
        , [database] 
            = db_name(tblSMF.database_id)
            
        , [fileID] 
            = tblSMF.file_id            
            
        , [fileSymbolicName]
            = tblSMF.[name]
        
        , [filePhysicalName]
            = tblSMF.[physical_name]
            
        --, tblSMF.*            
        
from   sys.master_files tblSMF

where  tblSMF.[database_id] <= 4

order by
        tblSMF.database_id asc
        

Image

Explanation
  1. Database
    • msdb
      • MSDBData & MSDBLog noted

 

SQL Server Configuration Manager
Image

Explanation
  1. Startup Parameter
    • -d
      • -dE:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

 

Identify location of msdb install files

Syntax

[InstallDriveLetter]:\Program Files\Microsoft SQL Server[Version].[InstanceName]\MSSQL\Install

Sample

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install

 

Inspect

Review Accounts’ access

Review Accounts NTFS Permissions on msdb data and log files
Outline

Here are the steps to take to read out the “Effective” permissions

  1. To view effective permissions on files and folders
    1. Open Windows Explorer, and then locate the file or folder for which you want to view effective permissions.
    2. Right-click the file or folder, click Properties, and then click the Security tab.
    3. Click Advanced, click the Effective Permissions tab, and then click Select button.
      • In the Select Window
        • Enter the name of a user or group, and then click OK.
    4. The selected check boxes indicate the effective permissions of the user or group for that file or folder.
Image

Get Set…

Safe keep msdb files

Copy msdb data and log files to a neutral location

Create a safe location and make copies of files to place in Safe Location.

Image

Go!

Start MS SQL Server using trace files

Trace Flags
Trace Flags Outline
  • Trace Flags
    • -T3608

 

Trace Flag ID What does it do Applicable Effective
3608 It will not start and recover any database except the MASTER system database. So none of the system databases; msdb, tempdb, model, etc will start or recover if this trace flag is used while starting SQL Server. Yes Yes
 902  Trace Flag 902 will bypass the internal upgrade script on startup No  N/A
Trace Flags Steps
SQL Server Configuration Manager
Add(ing) Trace Flag :- -T3608

Add(ed) Trace Flag :- -T3608

 

Start MS SQL Server
SQL Server Configuration Manager

 

Detach msdb
SQL

exec sp_detach_db 'msdb'

 

Rename MSDB Data & Log Files
Image

Create msdb
Command Line

sqlcmd -S . -i instmsdb.sql

SSMS
SSMS – Output

Rollout

Outline

  1. Stop SQL Server
  2. Remove SQL Server Trace Flags
  3. Retrace Trace Flags
    • -T3608

Stop MS SQL Server

Image

Trace Flags
Trace Flags Remove

remove Trace Flags

Trace Flags Remove – removed

Summary

There is a lot of very empty spaces here.

So much was left out to make this post concise and address them during more specific subject materials.

 

References

  1. SQLServerCentral.com
    • mssqlfun
      • How to rebuild MSDB database?
        Link
  2. Microsoft
    • Microsoft \ TechNet \ Windows Server
      • Access Control > Managing Permissions > Set, View, Change, or Remove Permissions on an Object
        Link
    • msdn
      • Sql Server 2012 – Cannot recover the master database
        Link
  3. Trace Flags
    • 3608
      • Paul Randal
        • How to recreate the msdb database in SQL Server 2005?
          Link
    •  902
      • Patrick Keisler, MSFT – Premier Field Engineer
        • Use Trace Flag 902 to Recover from a Cumulative Update Failure
          Posted on :- 2017-July-7th
          Link

 

SQL Server – Database Internal Version Number

Introduction

As we prepare for a new SQL Server release, SQL Server 2017 on Windows & Linux, it is as good a time as any to publicly avail the Internal Database Version Numbers.

Internal Database Version

Internal Version Number Marketing Description
869 2017
852 2016
782 2014
706 2012
663 2008-R2 2008-R2 SP1, SP2, SP3 ( This server supports version 663 and earlier. A downgrade path is not supported )
661 2008-R2
655 2008
612 2005 When vardecimal feature is enabled ( Paul S. Randal – SQL Q&A Partitioning, Consistency Checks, and More )
611 2005
539 2000
515 7

Code

Determine Internal Database Version Number

SQL



declare @database sysname

set @database = 'master'

select

	  [database]
		= @database
			
	, [version]
		= DATABASEPROPERTYEX
		(
			  @database
			, 'Version'
		)

Output

databaseInternal_20180729_0216AM
 

References

  1. Paul S. Randal
    • SQL Q&A Partitioning, Consistency Checks, and More
      Link
  2. Dan Guzman
    • Internal SQL Server Database Version Numbers
      Link
  3. Dixin Yan
    • Attach SQL Server 2000 database to SQL Server 2014
      Link

TempDB – Allocation & Deallocation Tracking – Internal & User Objects

Background

After quite  a long lapse, took this entire weekend to dig more into tempdb hyper growth.

We noticed that tempdb was using quite a bit of memory on a couple of boxes.

 

Earlier Work

  1. SQL Server – Query Plans with DesiredMemory
    Published :- 2016-12-02
    Link
  2. SQL Server – Memory Allocated/Unallocated per Database
    Published :- 2016-12-05
    Link
  3. SQL Server – Tempdb – v2012
    Published :- 2016-12-05
    Link

 

TroubleShooting

Instrumentation

Glossary

Here are the Dynamic Management Views (DMVs) that we will employ:

DMVInfo Information Link
 sys.dm_db_file_space_usage Returns space usage information for each file in the database.  Link
sys.dm_os_buffer_descriptions  Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.  Link
sys.dm_db_session_space_usage Returns the number of pages allocated and deallocated by each session for the database.  Link
 sys.dm_db_task_space_usage  Returns page allocation and deallocation activity by task for the database.  Link

 

 

 

tempdb.sys.dm_db_file_space_usage

Code


set nocount on
go

use [tempdb]
go

select
 
          [runtime]
			= convert( varchar(30), getdate(), 100) 
 
         , [totalspaceMB]
			= ( SUM (total_page_count)*8 / 1024 ) 

         , [unallocatedSpaceMB]
			= ( SUM (unallocated_extent_page_count)*8 / 1024 ) 

        , [versionStoreMB]
			= ( SUM (version_store_reserved_page_count)*8  / 1024 )
 
        , [userObjectMB]
			= ( SUM (user_object_reserved_page_count)*8 / 1024 ) 
 
        , [internalObjectMB]
			= ( SUM (internal_object_reserved_page_count)*8 / 1024 )

        , [mixedextentMB]
			= ( SUM (mixed_extent_page_count)*8 / 1024 ) 
 
FROM [tempdb].[sys].[dm_db_file_space_usage]

 

Output

QA – 2017.01.22 9:14 PM

sys__dm_db_file_space_usage__20170122_0915pm

 

sys.dm_os_buffer_descriptions

Code


; with cte
( 
    [count]
)
as
(
  
    select [count] = count(*)
    FROM   sys.dm_os_buffer_descriptors
  
)
  
SELECT
        [Database]
            = case
                when (database_id = 32767) then 'ResourceDB'
                else DB_NAME(database_id)
              end  
  
        , [CachedSizeMB]
  
            = 
                cast
                    (
                        (
                            COUNT(*) * 8
                        ) 
                        / 
                        ( 
                            1024.0 * 1
                        )
                        as decimal (18, 2)
                    )
  
        , [CachedSizeGB]
            = 
                cast
                    (
                        (
                            COUNT(*) * 8
                        ) 
                        / 
                        ( 
                            1024.0 * 1000
                        )
                        as decimal (18, 2)
                    )
  
  
    
        , [UnusedSizeMB]
  
            = SUM 
				(
					CAST ([free_space_in_bytes] AS BIGINT)
				) 
				/ (1024 * 1024) 

        , [%]
            = cast
              (
                    (count(*) * 100.00)
                        / cte.[count] 
                    as decimal(10, 2)
              )
  
FROM sys.dm_os_buffer_descriptors
  
cross apply cte
  
GROUP BY
             case
                when (database_id = 32767) then 'ResourceDB'
                else DB_NAME(database_id)
             end   
 
            , [cte].[count]
  
ORDER BY
        count(*) desc
           
OPTION (MAXDOP 1, RECOMPILE)

Output

QA – 2017.01.22 9:56 PM

sys_dm_os_buffer_descriptors__20170122_0956pm

 

 

sys.dm_db_session_space_usage & sys.dm_db_task_space_usage

Code


use [tempdb]
go

select 

		  tblSP.[spid]

		, tblSP.[loginame]

		, tblSP.[hostname]

		, tblSP.[program_name]

		--, tblSS.memory_usage

		--, tblSS.[status]

		, tblSS.open_transaction_count

		, [sessionInternalObjectsAllocationInMB]
			= cast
				(
					(tblSBSSU.[internal_objects_alloc_page_count] *1.0 ) / ( 128)
					as decimal(30, 2)
				)

		, [sessionInternalObjectsDeAllocationInMB]
			= cast
				(
					(tblSBSSU.[internal_objects_dealloc_page_count] *1.0 ) / ( 128)
					as decimal(30, 2)
				)

/*
		, [sessionInternalObjectsAllocationInGB]
			= cast
				(
					(tblSBSSU.[internal_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
					as decimal(30, 2)
				)
*/

		, [sessionUserObjectsAllocationInMB]
			= cast
				(
					 (tblSBSSU.user_objects_alloc_page_count *1.0 ) / ( 128)
					as decimal(30, 2)
				)


		, [sessionUserObjectsDeAllocationInMB]
			= cast
				(
					 (tblSBSSU.user_objects_dealloc_page_count *1.0 ) / ( 128)
					as decimal(30, 2)
				)


		, [taskInternalObjectsAllocationInMB]
			= cast
				(
					([tblSSBTSU].[internal_objects_alloc_page_count] *1.0 ) / ( 128)
					as decimal(30, 2)
				)

		, [taskInternalObjectsAllocationInGB]
			= cast
				(
					([tblSSBTSU].[internal_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
					as decimal(30, 2)
				)

/*
		, [taskUserObjectsAllocationInMB]
			= cast
				(
					 ([tblSSBTSU].user_objects_alloc_page_count *1.0 ) / ( 128)
					as decimal(30, 2)
				)


		, [taskUserObjectsAllocationInGB]
			= cast
				(
					([tblSSBTSU].[user_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
						as decimal(30, 2)
				)

*/
		, tblSP.[cmd]

from   [tempdb].[sys].[dm_db_session_space_usage] tblSBSSU

inner join [tempdb].[sys].[dm_db_task_space_usage] tblSSBTSU

		on tblSBSSU.[session_id] = tblSSBTSU.[session_id]

inner join [sys].[sysprocesses] tblSP

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

inner join sys.dm_exec_sessions tblSS

		on tblSBSSU.[session_id] = tblSS.[session_id]

where

	(

		(
			tblSBSSU.internal_objects_alloc_page_count
			+ tblSBSSU.user_objects_alloc_page_count
		) > 0


	)

order by

		(
			tblSBSSU.internal_objects_alloc_page_count
			+ tblSBSSU.user_objects_alloc_page_count
		) desc



 

Output

QA – 2017.01.22 9:14 PM

sys__dm_db_session_space_usage__20170122_0933am

Explanation
  1. We see that sessions 61 & 63 are the highest users of tempdb
    • In this case they are using internal Objects as compared to user Objects
    • We also see that just about all the memory allocated are subsequent deallocated

 

Summary

In a nutshell, both tempdb.sys.dm_db_file_space_usage ( durable file allocation ) and  sys.dm_os_buffer_descriptors ( online memory ) have high values for tempdb uptake.

But,  sys.dm_db_session_space_usage & sys.dm_db_task_space_usage ( currently running session indicators ) agree with what we see in sys.dm_db_file_space_usage DMV that the storage is indeed unallocated.

It is just that SQL Server is reluctant to free up that space from memory and give is back to the system.

Admittedly, we got lucky here; lucky in the sense that the sessions that are using tempdb are still connected to the SQL Instance; otherwise, we will mistake them for system or phantom processes.

 

References

  1. Microsoft Developer
    • Arvind Shyamsundar
      • Tracking TEMPDB internal object space usage in SQL 2012
        Link
  2.  SqlSkills
    • Paul Randall
      • Performance issues from wasted buffer pool memory
        Link

 

SQL Server – Error – Cannot shrink log file because the logical file located at the end of the file is in use

Background

Received a trouble ticket overnight that indicates that one of the disks on our server is running low.

Removed some of the outdated backup files and now wanted to shrink a log file that was relatively big.

Log File Shrink Command

Shrink Log File

Command


use [rbpivr1]
go

dbcc shrinkfile(2)
go

 

Error Message

Textual


Cannot shrink log file 2 (xxxxx_log) because the logical log file located at the end of the file is in use.


Image

errormessage

 

TroubleShoot

Let us do a bit of a troubleshooting….

Metadata

sys.databases – log_reuse_wait_desc

Code


select 
		  tblSD.name
		, tblSD.database_id
		, tblSD.recovery_model_desc
		, tblSD.log_reuse_wait_desc 

from   sys.databases tblSD

Output

sys-databases-20161221-1013am

 

Explanation

  • There are two databases that have other than NOTHING for their log re-use
  • The two databases are
    • msdb
      • wait reason is DATABASE_SNAPSHOT_CREATION
    • rbpivr1
      • wait reason is CHECKPOINT

 

sys.dm_exec_requests

Code



select 
		 [isCheckpointSession]
			= case
				  when (tblSDER.command = 'CHECKPOINT') then 'Yes'
				  when (tblSDER.command like '%CKPT%') then 'Yes'
				  else 'No'
			  end	

		, tblSDER.[status]

		, tblSDER.session_id

		, tblSDER.command

		, tblSDER.database_id

		, [database]
			= db_name(tblSDER.database_id)

		, tblSDER.wait_type

		, tblSDER.last_wait_type

		--, tblSDER.estimated_completion_time

		, tblSSP.[last_batch]

		, tblSDER.start_time

		, [duration]
			= datediff(minute, tblSSP.last_batch, getdate())

		, tblSDER.wait_resource

		, tblSDER.wait_time

		, tblSDER.blocking_session_id

		, tblSDER.cpu_time
		, tblSDER.[reads]
		, tblSDER.[writes]

from   sys.dm_exec_requests tblSDER

inner join master.dbo.sysprocesses tblSSP

		on tblSDER.[session_id] = tblSSP.[spid]
order by 
		  1 desc
		, tblSDER.[status] asc
		, tblSDER.command asc


Output

 

sys-dm_exec_requests-20161221-0233pm

 

Explanation

  1. Because of our requested ordering, the sessions that relate to CHECKPOINT are listed  first
    • CHECKPOINT
      • The traditional CHECKPOINT session
    • XTP_CKPT_AGENT
    • XTP_OFFLINE_CKPT
      • For the databases that have in-memory tables, we have unique sessions
  2. There is no blocking going on
    • And, so not quite why it appears that our database is not being checkpoint

 

Get Transaction Log Size & Actual Usage

Performance Counters

Code
Code – Function – transactionLog.IUDF_UtilizationMetricsFromPerformanceCounters


use master
go

if schema_id('transactionLog') is null
begin

	exec('create schema [transactionLog] authorization [dbo]')

end
go

if object_id('[transactionLog].[IUDF_UtilizationMetricsFromPerformanceCounters]') is null
begin

	exec(
			'create function [transactionLog].[IUDF_UtilizationMetricsFromPerformanceCounters]
			()
			RETURNS table
			AS
			RETURN 
			(
				SELECT 
					[shell] = 1/0
		   );
	   	   '
		)

end
go


alter function [transactionLog].[IUDF_UtilizationMetricsFromPerformanceCounters]
()
returns TABLE
as
return
(

	with cte
	(
		  [dbid]
		, [database]
		, [counter]
		, [size]
	)
	as
	(
 
		SELECT
				  [dbid]
					= db_id(RTRIM(instance_name)) 
 
				, [database]
					= RTRIM(instance_name) 
 
				, tblDMOPC.counter_name
 
				, cntr_value log_size_kb
 
		FROM  sys.dm_os_performance_counters tblDMOPC
 
		WHERE tblDMOPC.object_name = 'SQLServer:Databases'
 
		AND   tblDMOPC.counter_name like 'Log File' + '%'
 
		AND   tblDMOPC.instance_name <> '_Total'
 
	)
	, ctePivot
	as
	(
		select *
 
		from   cte
		pivot 
				(
					avg ([size]) 
							for [counter] in
								(
									  [Log File(s) Used Size (KB)]                                                                                                           
									, [Log File(s) Size (KB)]                                                                                                           
								)
				) as avgSize
 
	)
 
	select 

			  [cteP].[dbid]

			, [cteP].[database]
 
			, [usedMB] = [Log File(s) Used Size (KB)]
							/ 1000
 
			, [totalMB] =  [Log File(s) Size (KB)]
							/ 1000
 
			, [%]
				= cast
					(
						( [Log File(s) Used Size (KB)] * 100.00)
						/ ( [Log File(s) Size (KB)])
						as decimal(10,2 )
					)

			, [recoveryModel]
				= tblSD.recovery_model_desc

			, [log reuse wait]
				= tblSD.log_reuse_wait_desc
			
	from   ctePivot cteP

	inner join sys.databases tblSD

		on cteP.[dbid] = tblSD.database_id
 

)
go



Invoke

select iutfPC.*

from   [transactionLog].[IUDF_UtilizationMetricsFromPerformanceCounters]() iutfPC

order by 
	iutfPC.[usedMB] desc


Output

performancecounter-20161221-0246pm

 

Explanation:
  1. The database that we are digging into, ppsivr, is the one that has the most in-use storage allocated

 

 

dbcc sqlperf(logpace)

The traditional method for getting transaction in-use is dbcc sqlperf(logspace).

 

Code


dbcc sqlperf(logspace)

Output

dbcc-sqlperf-logspace-20161221-0320pm

Explanation

  1. The database we are focused on is rbpivr1
    • It’s allocated size is 3300 MB
    • And, % utilization is 30

 

To increase our repertoire of diagnostic tools, we focused on using performance counters in this post.

 

dbcc opentran

Code


use rbpivr1;

dbcc opentran

Output

dbcc opentran came back empty.

 

Trace Flags

Does the errorlog file contain interesting clues.

To go forward we must enable certain Trace Flags.

Pertinent Trace Flags

TraceFlag File Version  Source  KB
 3502 Tracks when checkpoint is occurring Paul Randal How to monitor checkpoints
Link
 3504  More detailed information about checkpoint  Paul Randal How to monitor checkpoints
Link

 

 

Set Trace Flags


dbcc traceon (3502, 3504, 3605, -1 )

Review Trace Flags


dbcc tracestatus

Output

traceflagsreview

ErrorLog

 

Aggregate Checkpoints in the current error log file

Code



use[master]
go
 
set nocount on;
go
 
declare @p1  INT
declare @p2  INT
declare @p3  VARCHAR(255)
declare @p4  VARCHAR(255)
 
declare @tblErrorLog     TABLE
(
      [id] int not null
        identity(1,1)
 
    , [LogDate] datetime
 
    , [ProcessInfo] sysname
 
    , [Text]        varchar(600) null
 
    , primary key
        (
              [LogDate]
            , [id]
        )
 
    , INDEX INDX_ID
        (
            [id]
        )
 
)
 
set @p1 = 0 -- current
set @p2 = 1 -- error log
set @p3 = 'Ckpt'
set @p3 = 'Ckpt dbid'
set @p4  = ''
 
insert into @tblErrorLog
(
      [LogDate]
    , [ProcessInfo]
    , [Text]        
)
exec [sys].[sp_readerrorlog]
         @p1   = @p1  
       , @p2   = @p2
       , @p3   = @p3
       , @p4   = @p4
 
 
select
          tblSD.[name]
 
        , tblSD.log_reuse_wait_desc
 
        , [count] 
            = count(tblEL.LogDate)
 
        , [LogDateMin]
            = min(tblEL.LogDate)
 
        , [LogDateMax]
            = max(tblEL.LogDate)
  
from  sys.databases tblSD
 
left outer join @tblErrorLog tblEL
 
        on   tblEL.[Text]  like '%' + cast(tblSD.database_id as varchar(10)) + '%'
		 
        and  tblEL.[Text] like 'Ckpt dbid ' + '%' + 'started'

group by
 
           tblSD.[name]
         , tblSD.log_reuse_wait_desc
 
order by
 
           tblSD.log_reuse_wait_desc asc
         , count(tblEL.LogDate) desc
         , tblSD.[name]


readerrorlog-20161212-0343pm

 

Output

  1. Database
    • rbpivr1
      • We have yet to log CHECKPOINTS for the rbpivr1 database
  2. Whereas, we have logged 3 checkpoints for the MDW, ess, and master databases

Summary

Went for our corporate’s White Elephant Christmas party.

Upon returning ran the same set of scripts and we are now able to shrink the transaction logs.

Also, CHECKPOINTS are occurring on the rbpivr1 database.

Again, not so sure what the initial reason for the CHECKPOINTS not completing is.

Hopefully, will get read more on background processes and successfully troubleshooting them in the future.

 

Dedicated

It has been a long time coming.

Somehow never dedicated something to a thought leader, Paul Randal.

Late till now…

Otis Redding – A Change Is Gonna Come
Link

Hate to go there, but on Post, gotta be honest.

Had better feel for Otis Redding when I listened to Lil Wayne’s track.

Lil Wayne – Long Time Coming
Link

With rhymes like “But I can’t love ya I ain’t usher I’m far southern”.

It is all of 8 years behind us; right before your boy Hussein made it into Office, so don’t SMH.

 

Source Control

GitHub

  1. SQLServerTransactionLogMetrics
    Link

 

References

  1. Paul S. Randal
    • sqlmag
      • How to monitor checkpoints
        Link
  2.  Microsoft
    • technet.microsoft.com
      • Degremont Michel
        • Unable to shrink the transaction log
          Link
    • In Memory Tables
      • Extreme Transaction Processing (XTP, Hekaton) – the solution to everything?
        Link
    • Developer Network
      • Database Features >> In-Memory OLTP (In-Memory Optimization) >> Creating and Managing Storage for Memory-Optimized Objects >> Scalability
        Link
    • Support
      • FIX: Offline checkpoint thread shuts down without providing detailed exception information in SQL Server 2014
        Link
  3. Stack Overflow
    • How can I get the size of the Transaction Log in SQL 2005 programmatically?
  4. sqlity.net
    • Log Reuse Waits Explained: CHECKPOINT
      Link