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

Microsoft – SQL Server – Wait Resource – Database Object and Page

Introduction

As I was debugging an unresponsive a SQL Server Instance, I ran into the need to review the sessions and determine whether they might be colliding with each other.

Review System Processes

Let us quickly review which processes are being blocked:

select *
from   master.dbo.sysprocesses
where  spid > 50
and    (
	  (blocked != 0)
	)	

Here is our output:

sysprocesses_waitresource

The column we are tracking is the waitresource column.

Wait Resource Format

Thankfully, Microsoft has done a good job documenting and publishing detailed guide as to how data in the waitresource column is arrived at.

The relevant KB Articles are:

And, here is the well presented guide:

Resource Format Example
Table TAB: DatabaseID:ObjectID:IndexID TAB:
<dbid>,
<objectID>,
<indexID>
Page PAGE:
DatabaseID:FileID:PageID
PAGE:
<dbid>,
<DBID>,
<PageID>
Key Key:
DatabaseID:HashValueForIndexKey (HOBT_ID)
KEY:
<dbid>,
<Hobt_ID>
Row RID:
DatabaseID:FileID:PageID:Slot (row)
 RID:
<dbid>,
<DBID>,
<PageID>,
<Slot (Row)>
Compile DatabaseID:ObjectID [[COMPILE]] <dbid>,
<ObjectID>[[COMPILE]]

 

From our query output pasted earlier, we see that spid = 54 is waiting on a specific resource 20:1:56444 and spid is waiting on resource PAG 20:1:168.

Interpretation:

Item Value – SPID – 54 Value – SPID – 65
Database 20 20
File ID 1 1
Page ID 56444 56444

Wait Resource – PAG* – MS SQL Server – v2008/R2 and below

To determine the specific object that is being waited on we can try a couple of things.

dbcc inputbuffer(<spid>)

We can issue dbcc inputbuffer(<spid>) and read the SQL Statement that is the session’s input buffer.

dbcc page(<database-id>, <file-id>, <page-id>)

The truth is that dbcc inputbuffer will return the entire SQL Statement in the batch and so it is often not precise enough when one will like the specific database object that is being waited on.

For example if our SQL statement is:


select tblOrder.*

from   dbo.order  tblOrder

         inner join dbo.orderDetail tblOrderDetail

              on tblOrder.orderNumber = tblOrderDetail.orderNumber

where   tblOrderNumber.orderNumber = @orderNumber            

we will know that dbo.order and dbo.orderDetail are possible culprit, but we will not know which specific database object.

So to get specific detail we should use dbcc page …

Syntax:

dbcc traceon (3604)

    dbcc pagee (<database-id>, <file-id>, <page-id>)

DBCC traceoff (3604)

 

Sample Code:

dbcc traceon (3604)

    dbcc page (12,1,6000)

DBCC traceoff (3604)

 

Command Interpretation:

Command Reason
dbcc traceon(3604) Redirect output of DBCC to the client and not to the log
dbcc page(<db>, <file>, <page>) Actual DBCC Command
dbcc traceoff(3604) Reverse DBCC Output Redirection

DBCC page – Output

DBCCPageOutput

DBCC page – Output – Explanation

DBCC Page provides a full plate of information.  Here is the data that is pertinent to our current discussion:

Item Sample What does it mean?
bdbid 14 Database ID – Use dbname(<dbid>) to get database name
m_pageId 1:145604 Page ID
Metadata: AllocUnitId 72057594044350464 Allocation Unit ID
Metadata: PartitionId 72057594039959552 Partition ID
Metadata: ObjectId 565577053 Object ID – Use object_name(<object-id>, <dbid>) to get actual table name
Metadata: IndexId 0 Index ID – 0 is for heap, 1 for clustered index, and >1 for non-clustered index
m_lsn (6856:580:56) LSN – Log Sequence Number
m_ghostRecCnt 0 Ghost Record Count – 0 means it is not a ghost record; anything other than 0 mean the record is marked for deletion

 

Wait Resource – PAG* – MS SQL Server – v2012 and above

As good and revealing as “DBCC Page” is, it is encumbered for a variety of reasons; The reasons includes:

  • You need to have sysadmin privileges to use it
  • The output is not delivered via tabulated easy to read grid display
  • And, also it is easily consumable for monitoring and other uses

To widen the audience, Microsoft is now offering “DBCC Page” data via dynamic management views (DMV) specifically sys.dm_db_database_page_allocations.

Please note that it was added as part of MS SQL Version v2012.

Sample Code:


/*
    RID: 14:1:145604:0  

    DB Name - 14
    File ID - 1
    Page ID : 145604
*/

declare @dbName sysname
declare @dbid int
declare @tableID int
declare @indexID int
declare @partionId int
declare @pageID int
declare @mode sysname

set  @dbid = db_id()
set  @dbName = db_name(@dbid)
set  @tableID = null
set  @indexID = null
set  @partionId = null

set  @pageID = 145604
/* Mode - LIMITED - DETAILED */
set  @mode = 'LIMITED'
set  @indexID = null

select
	  tblPage.database_id
	, db_name(tblPage.database_id) as databaseName
	, tblPage.object_id
	, object_name(tblPage.object_id, tblPage.database_id) as objectName
	, tblPage.index_id
	, tblPage.partition_id
	, tblPage.allocation_unit_type_desc
	, tblPage.allocated_page_file_id
	, tblPage.allocated_page_page_id
	, tblPage.allocation_unit_type_desc
	, tblPage.page_type_desc
from   sys.dm_db_database_page_allocations
		(@dbid, @tableID, @indexID, @partionId, @mode) tblPage

where (
	 (tblPage.allocated_page_page_id = @pageID)
      )

Image:

sys

Please be sure to pass in ‘LIMITED’ as the @mode option.  Passing in the other available option of ‘DETAILED’ will result in a much longer wait.

Only pass in DETAILED if there are columns that you need, and are missing when you pass in the former.

Lab Environment

It is very easy to set up a Lab environment to simulate resource wait.

Lab Environment – Create Database – DBLab


use [master]
go

if db_id('DBLab') is null
begin

	print 'Creating DB - DBLab .... '

	exec('create database [DBLab]')

	exec('ALTER DATABASE [DBLab] set recovery simple')

	print 'Creating DB - DBLab'

end
go

Lab Environment – Create Table – dbo.whatisonyourmind


use [DBLab]
go

set noexec off
go

if object_id('dbo.whatisonyourmind') is not null
begin

	set noexec on
end
go

create table dbo.whatisonyourmind
(
	  [post] nvarchar(600)

	, [addedBy] sysname not null
		constraint defaultWhatIsOnYourMindAddedBy default SYSTEM_USER

	, [addedOn] datetime not null
		constraint defaultWhatIsOnYourMindAddedOn default getutcdate()

)

go

set noexec off
go

Lab Environment – Populate Table – dbo.whatisonyourmind

Let us populate the table.

The DML statements are familiar and simple.  But, rather than to conform to normal Microsoft SQL Server default mode of auto-commit, let us use explicit transaction mode.

use [DBLab]
go

truncate table [dbo].[whatisonyourmind];

go

begin tran

    insert into [dbo].[whatisonyourmind]
    ([post])
    values ('DEV6834')

    insert into [dbo].[whatisonyourmind]
    ([post])
    values ('DEV6834.2')

    update [dbo].[whatisonyourmind]
    set    [post] = 'DEV6834.3'
    where  [addedBy] = SYSTEM_USER

    delete
    from   [dbo].[whatisonyourmind]
    where  [post] = 'DEV6834.3'

/*
while (@@trancount > 0)
begin
    print 'rolling back';
    rollback tran;
end
*/

Notice that we have commented out the “rollback tran” statement.

Lab Environment – Populate Table – dbo.whatisonyourmind (2nd Connection)

Please initiate a new SQL Server Connection and issue the statement pasted below:


while (@@trancount > 0)
begin
	print 'rolling back';
	rollback tran;
end
go

use [DBLab]
go

delete from
from   [dbo].[whatisonyourmind]

Lab Environment – Inspect Sessions and find blockers

Microsoft SQL Server (ver 2000)

select 

		  tblSysProcess.spid
		, tblSysProcess.cmd
		, tblSysProcess.blocked
		, tblSysProcess.waitresource
		, tblSysProcess.open_tran
		, tblSysProcessBlocker.spid spidBlocker

from   master.dbo.sysprocesses tblSysProcess

			left outer join master.dbo.sysprocesses tblSysProcessBlocker

				on tblSysProcess.blocked = tblSysProcessBlocker.spid

where
	(

		(tblSysProcess.spid > 50)

	)

and
	   (

		 (tblSysProcess.blocked != 0)
	      or (tblSysProcess.spid in
                        (
                            select blocked
                            from master.dbo.sysprocesses tblSysProcess_Inner
                        )
                  )

	)

Image – v2000

waitResource_output_v2000

Microsoft SQL Server (more recent versions)


SELECT
		sysprc.spid,
		sysprc.waittime,
		sysprc.lastwaittype,
		DB_NAME(sysprc.dbid) AS database_name,
--		sysprc.cpu,
--		sysprc.physical_io,
--		sysprc.login_time,
--		sysprc.last_batch,
		sysprc.status,
		sysprc.hostname,
--		sysprc.[program_name],
		sysprc.cmd,
--		sysprc.loginame,
		OBJECT_NAME(sqltxt.objectid) AS [object_name],
		sqltxt.text

FROM master.sys.sysprocesses sysprc

    left outer join master.sys.sysprocesses tblSysProcessBlocker

       on sysprc.blocked = tblSysProcessBlocker.spid

    OUTER APPLY master.sys.dm_exec_sql_text(sysprc.sql_handle) sqltxt

where  sysprc.spid >  50

and

   (

	      (sysprc.blocked != 0)
	 or (
               sysprc.spid in (
                              select blocked
                              from   master.dbo.sysprocesses tblSysProcess_Inner
                             )
            )

 )

Image – v2012

waitResource_output_v2012

Security Implications

DBCC Page came over from the old Sybase days.

You can use it to dig deep into the SQL Server Storage Internals.

This behavior is documented in http://support.microsoft.com/kb/83065.

When you invoke it with a print option greater than 1, it will in fact emit out raw page contents.

DBCCPagePrintOption2

 

So if you have data, that needs to kept away from DBA, etc, learn to play with encryption and compression.

References

References – SQL Server – DBCC Page

References – SQL Server – DMV – sys.dm_db_database_page_allocations

References – SQL Server – Storage Engine

References – SQL Server – TroubleShooting Blocking Problems

References – SQL Server – Latch Waits

 

References – SQL Server – Documentation