SQL Server :- DBCC Buffer

Background

Wanted to cover a DBCC Command that is not well documented.

It is “DBCC Buffer“.

I am not covering it cause it stands out so much, but to show a couple of troubleshooting tips that I was not previously aware of till someone else covered it.

DBCC

DBCC means Database Consistency Checker and it has been around for a while now.

Microsoft continues to add more functionality to it.

DBCC HELP

To get a list of available DBCC Commands, please issue “DBCC Help”.

Default

Outline

To have dbcc help show hidden options, please issue dbcc traceon(2588) beforehand.

And, be sure to issue dbcc traceoff(2588) afterwards.

Syntax

dbcc traceon(2588)
with no_infomsgs
go

DBCC HELP ('?')
with no_infomsgs
GO

dbcc traceoff(2588)
with no_infomsgs
go

Output

v2017

Full Set

Syntax


DBCC HELP ('?')
    with no_infomsgs

Output

v2017

Explanation

When we precede dbcc help with dbcc traceon(2588), we get a more complete list.

DBCC BUFFER

To review actually loaded in memory for a particular table and index, we can issue “DBCC Buffer“.

Outline

Here are the steps that we will follow :-

  1. dbcc help(‘buffer’)
  2. dbcc traceon(3604)
    • Allows result of certain dbcc commands to be showed on screen
  3. Prime Memory
    • Read data from targeted table
  4. dbcc buffer
  5. dbcc traceoff(3604)

dbcc help(‘buffer’)

Syntax

dbcc traceon(2588)
with no_infomsgs
go

DBCC HELP ('buffer')
with no_infomsgs
GO

dbcc traceoff(2588)
with no_infomsgs
go

Output

Output – v2017

dbcc traceon(3604)

Syntax


dbcc traceon(3604)
with no_infomsgs

Prime Memory

Sample


select top 1 * from [bible].[dbo].[kjv]

dbcc buffer

Syntax


dbcc buffer
(
      database
    , table
    , numberofbuffers
    , printoption
)
with
      all_errormsgs
    , no_infomsgs
    , maxdop=1

Sample


dbcc buffer
(
      'bible' -- database
    , '[dbo].[kjv]'   -- table
    , 1 -- number of buffers
    , 1 -- printopt
)
with
      all_errormsgs
    , no_infomsgs
    , maxdop=1

Output

Output – v2017

Explanation – v2017

  1. Allocation Status
    • GAM ( Global Allocation Map )
    • SGAM ( Shared Global Allocation Map )
    • PFS ( Page Free Space )
  2. Record Type
    • Primary Record
  3. Record Size
    • We see the size of each record
      • 81 in this case
  4. Memory Dump
    • Actual Memory Dump of database record’s content

dbcc traceoff(3604)

Syntax


dbcc traceoff(3604)
with no_infomsgs

Summary

As always, nada.

Hopefully, you will need to issue “dbcc buffer“.

I wanted to familiarize myself with dbcc help as a gateway to reviewing other dbcc commands.

 

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 – 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

SQL Server – Determining Size of Your Procedure Cache

Background

For some work I am doing in terms of TempDB growing size, I wanted to see what role the Procedure Cache plays.

We will look at the Procedure Cache in terms of number of entries and actual size.

But, we will not divvy things up in terms of type nor characteristics of the contents.

 

Instrumentation

Here is a bit of tooling on how to get the size of your Procedure Cache.

SQL Server

dbcc memorystatus

Script


declare @tblMemoryStatus TABLE
(
	  [id]     int not null 
					identity(1,1) 

	, [item]   varchar(255)

	, [value]  bigint

	, [memoryMB] 
			as
				case 
						when [item] like '%Pages' then [value] /128
						else null
				end

	, [memoryGB] 
			as
				cast
				(
					case 

						when [item] like '%Pages' then 
						(
							( [value] * 1.00 / 128)
							/ 1024 
						)

						else null

					end
					as decimal(10, 2)
				)


)

insert into @tblMemoryStatus
(
	  [item]
	, [value]
)
exec ('dbcc memorystatus')

select 

		  [item]

		, [value]

		, [memoryMB] 

		, [memoryGB] 

from   @tblMemoryStatus

where  [item] in
			(
				  'TotalProcs'
				, 'TotalPages'
				, 'InUsePages'
			)

 

Output

dbccmemorystatus__20170124__1149am

 

dbcc proccache

Script


DBCC PROCCACHE WITH NO_INFOMSGS 

Output

dbccproccache-hpalm-20170124-1206pm

Performance Counters

Script


SELECT
		  [object_name]
		, [counter_name]
		, [instance_name]
		, [cntr_value]
		, [cntr_type]  

		, [memoryMB] 
		=
			case 
					when [counter_name] like '%Pages%' then cntr_value /128
					else null
			end

		, [memoryGB] 
			=
					cast
					(
						case 

							when [counter_name] like '%Pages%' then 
							(
								( cntr_value * 1.00 / 128)
								/ 1024 
							)

							else null

						end
						as decimal(10, 2)
					)


FROM sys.dm_os_performance_counters tblDOPC

where  tblDOPC.object_name like 'SQLServer:Plan Cache%'

and    tblDOPC.[instance_name] = '_Total'

and    tblDOPC.[counter_name] 
			in (
					  'Cache Pages'
					, 'Cache Object Counts'                                                                                                             
					, 'Cache Objects in use'                                                                                                            
			   )
				
;  


Output

peformancecounters_hpalm_prod_20170124_1154am

 

Performance Monitor

Using perfmon, add metrics for SQLServer:PlanCache

performancemonitor-hpalm-20170124-1158am

 

Summary

Within SQL Server someone with sysadmin privileges can use a variety of dbcc commands such as “dbcc memorystatus” and “dbcc proccache” to get the size of the Procedure Size.

Performance Counters are also exposed from within SQL Server via sys.dm_os_performance_counters or through the OS via Performance Counters ( perfmon ).

Microsoft – SQL Server – Database Repair

Microsoft – SQL Server – Database Repair

Many years ago, when your hard-drive crashes you want to look for \ to “Norton Disk Doctor”.

Another excellent tool is Gibson’s SpinRite.

These days, if you are running Microsoft SQL Server you want do the following:

  1. Take regular full and incremental backups
  2. Set your database mode to PAGE_VERIFY CHECKSUM / TURN_PAGE_DETECTION ( Sunil Agarwal – Enabling CheckSum in SQL Server 2005 http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/29/enabling-checksum-in-sql2005.aspx )
  3. Pay attention to your SQL Server Error Logs and OS Event Viewer Log
  4. Review your HBA Cards (QLogic / Brocade) Logs
  5. Understand everything in your IO Path (Placement of your data & log files, configuration of your HBA cards, Anti-Virus Tools certification for your OS, File System Filters [compression, Security \ encryption])
  6. And, most importantly friend Paul Randle who wrote most of the original DBCC CheckDBs) in SQL Server

Please per-use the references listed below.

References:

  1. Repair vs Restore Dileama http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/07/620319.aspx
  2. Paul Randle – Repair
    http://www.sqlskills.com/blogs/paul/category/Repair.aspx
  3. Paul Randle – Unexpected consistency checks
    http://technet.microsoft.com/en-us/magazine/2009.10.sqlqa.aspx
  4. How to troubleshoot Msg 824
    http://support.microsoft.com/kb/2015756
  5. How to trouble Database Consistency errors
    http://support.microsoft.com/kb/2015748
  6. Understanding and managing the suspect pages table
    http://msdn.microsoft.com/en-us/library/ms191301(v=sql.105).aspx
  7. SQL Server Page Restore
    http://beyondrelational.com/modules/24/syndicated/500/posts/12128/sql-server-page-restore.aspx
  8. Single User Mode in Mirrored Database
    http://www.sqlservercentral.com/Forums/Topic1206207-1549-1.aspx

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

MS SQL Server – Determine last date DBCC CheckDB was ran

MS SQL Server – Determining last date DBCC CheckDB was ran

A very good article around determine the last time DBCC CheckDB date was ran against a Database is available @ http://sqlserverpedia.com/wiki/Last_clean_DBCC_CHECKDB_date

   SET NOCOUNT ON;

   If object_id('tempdb.dbo.#temp') is not null
   begin

      drop table #temp

   end

   CREATE TABLE #temp
   (
         ParentObject     VARCHAR(255)
       , [Object]       VARCHAR(255)
       , Field          VARCHAR(255)
       , [Value]        VARCHAR(255)
   )  

   CREATE TABLE #DBCCResults
   (
          ServerName         VARCHAR(255)
        , DBName             VARCHAR(255)
        , LastCleanDBCCDate  DATETIME
    )  

    EXEC master.dbo.sp_MSforeachdb
             @command1 = 'USE [?]; INSERT INTO #temp EXECUTE (''DBCC DBINFO WITH TABLERESULTS, NO_INFOMSGS'')'
           , @command2 = 'INSERT INTO #DBCCResults SELECT @@SERVERNAME, ''?'', [Value] FROM #temp WHERE [Field] = ''dbi_dbccLastKnownGood'''
           , @command3 = 'TRUNCATE TABLE #temp'  

   --Delete duplicates due to a bug in SQL Server 2008

/*
   ;WITH DBCC_CTE AS
   (
       SELECT ROW_NUMBER() OVER (PARTITION BY ServerName, DBName, LastCleanDBCCDate ORDER BY LastCleanDBCCDate) RowID
       FROM #DBCCResults
   )
   DELETE FROM DBCC_CTE WHERE RowID > 1;
*/

    SELECT
             ServerName
           , DBName
           , Max(LastCleanDBCCDate) as LastCleanDBCCDate
           , CASE Max(LastCleanDBCCDate)
                   WHEN '1900-01-01 00:00:00.000' THEN 'Never ran DBCC CHECKDB'
                   ELSE Convert(varchar(255), max(LastCleanDBCCDate))
             END AS LastCleanDBCCDateInfo
   FROM #DBCCResults
   Group by
             ServerName
           , DBName
   Order by
             ServerName
           , DBName   

   DROP TABLE #temp
   
   DROP TABLE #DBCCResults;