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
Code
Syntax
dbcc page ( {'dbname' | dbid} , filenum , pagenum [, printopt={0|1|2|3} ] )
Explanation
- 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
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.