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 – Database Size Aggregated By File Type

Background

For a performance exercise that I will be undertaken I need to figure out how much my user databases and tempdb are growing.

 

Lineage

Here are some past posts along those same lines.

  1. SQL Server – List Database Size
    Use sys.master-files
    Published On :- 2017-March-29th
    Link

 

SQL

Pasted below are some sample SQL for getting file sizes and in use portion aggregated by file types.

Database Size for current database

Code



; with cte
as
(

  select 
		    [dbName]
			= DB_NAME() 

		, [type]				

		, [fileSizeMB]
			= cast
				(
					sum(size)/128.0
					as decimal(10, 2)
				)

		, [spaceUsedMB]
			= cast
				(
					sum(
							CAST
							(
								FILEPROPERTY(name, 'SpaceUsed') AS INT
							)
					)/128.0
					as decimal(10, 2)
				)

		, [freeSpaceMB]
			= sum
				(
					tblSDF.[size]
					- CAST
					(
						FILEPROPERTY
						(
							  tblSDF.[name]
							, 'SpaceUsed'
						) AS INT
					)
				)/128.0

	from sys.database_files tblSDF

	group 
		by tblSDF.[type] 

)

select 
		  [dbName]

		, [DataAllocatedSizeInMB] = sum(isNull([alloc0], 1))

		, [DataInUseSizeInMB] = sum(isNull([inuse0], 1))

		, [dataInUse%]
			= cast
				( 
					(
						( sum(isNull([inuse0], 1)) * 100.00 )
							/ sum(isNull([alloc0], 1))
					)
					as decimal(10, 2)
				)

		, [LogAllocatedSizeInMB] = sum(isNull([alloc1], 0))
		, [LogInuseSizeInMB] = sum(isNull([inuse1], 0))	
		, [logInUse%]
			= cast
				(
					( 
						sum
						(
							isNull([inuse1], 1)
						) * 100.00 
					)
					/ sum
					  (
						isNull([alloc1], 1)
					  )

					as decimal(10, 2)
				)

		, [fileStreamAllocatedSizeInMB] 
			= sum
				(
					isNull([alloc2], 0)
				)


from   
		(
		
			select 
					  [dbName]
					, [type] = 'alloc' + cast([type] as char(1))
					, [fileSizeMB]
					, [spaceUsedMB]
					, [typeInUse] = 'inuse' + cast([type] as char(1))
			from   cte

		) cteFS

		PIVOT
		(

			  sum 
			  ( 
				cteFS.[fileSizeMB] 
			  )

			  for [type]
			  in 
			  (
				  [alloc0]
				, [alloc1]
				, [alloc2]
			  )

		) as cteDataFileSizeInMB

		PIVOT
		(

			  sum 
			  ( 
				[spaceUsedMB]
			  )

			  for [typeInUse]
			  in 
			  (
				  [inuse0]
				, [inuse1]
				, [inuse2]
			  )

		) as cteInUseFileSizeInMB


group by
		  [dbName]



Output

 

 

Database Size for all databases

Code




set nocount on;
go

use [master]
go

declare @tblDatabase  TABLE
(
	  [dbName]					sysname not null
	, [DataAllocatedSizeInMB]   int
	, [DataInUseSizeInMB]	    bigint

	, [dataInUse%] 
		as 
			convert
			(
				  decimal(10, 2)
				, (
					[DataInUseSizeInMB] * 100.00
					/ NULLIF
						(
							[DataAllocatedSizeInMB]
							, 0
						)	
					)
			)

	, [LogAllocatedSizeInMB] bigint
	, [LogInuseSizeInMB]	 bigint
	, [logInUse%] as 
		convert
		(
			decimal(10, 2)
			, ( [LogInuseSizeInMB] * 100.00 )
				/ NULLIF([LogAllocatedSizeInMB], 0)
		)

	, [fileStreamAllocatedSizeInMB]  bigint


)

insert into @tblDatabase

EXEC sp_MSforeachdb 
	'
	IF DATABASEPROPERTYEX(''?'', ''Collation'') IS NOT NULL
	begin

		USE [?];
		; with cte
		as
		(

		  select 
					[dbName]
					= DB_NAME() 

				, [type]				

				, [fileSizeMB]
					= sum(size)/128.0

				, [spaceUsedMB]
					= sum(
							CAST
								(
									FILEPROPERTY(name, ''SpaceUsed'') AS INT
								)
						)/128.0

				, [freeSpaceMB]
					= sum
						(
							tblSDF.[size]
							- CAST
							(
								FILEPROPERTY
								(
									  tblSDF.[name]
									, ''SpaceUsed''
								) AS INT
							)
						)/128.0

			from sys.database_files tblSDF

			group 
				by tblSDF.[type] 

		)

		select 
				  [dbName]

				, [DataAllocatedSizeInMB] = sum(isNull([alloc0], 1))

				, [DataInUseSizeInMB] = sum(isNull([inuse0], 1))

				, [LogAllocatedSizeInMB] = sum(isNull([alloc1], 0))
				, [LogInuseSizeInMB] = sum(isNull([inuse1], 0))	

				, [fileStreamAllocatedSizeInMB] 
					= sum
						(
							isNull([alloc2], 0)
						)

		from   
				(
		
					select 
							  [dbName]
							, [type] = ''alloc'' + cast([type] as char(1))
							, [fileSizeMB]
							, [spaceUsedMB]
							, [typeInUse] = ''inuse'' + cast([type] as char(1))
					from   cte

				) cteFS

				PIVOT
				(

					  sum 
					  ( 
						cteFS.[fileSizeMB] 
					  )

					  for [type]
					  in 
					  (
						  [alloc0]
						, [alloc1]
						, [alloc2]
					  )

				) as cteDataFileSizeInMB

				PIVOT
				(

					  sum 
					  ( 
						[spaceUsedMB]
					  )

					  for [typeInUse]
					  in 
					  (
						  [inuse0]
						, [inuse1]
					  )

				) as cteInUseFileSizeInMB


			group by
				  [dbName]

		end

	  '

select *
from   @tblDatabase
order by [dbName]