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.
