3

I'm trying to read the current database size using dbcc command.
In my test I execute following commands:

DBCC Fileheader('tempdb',1) => size = 988152
DBCC Page('tempdb',1,0,3) => size = 5112

When I check tempdb initial size with the user interface I get 7.720MB (= 988152 * 8 / 1024). How come there is a difference between the size found with dbcc fileheader and dbcc page?

Update:

Output from DBCC FileHeader can be found here, the output from DBCC Page can be found here

Update:

Yesterday I restarted the server but the difference is still present. The size with dbcc page is now 0.

BindingID = 2c0a5351-ab60-11d2-896a-00c04fd9374a
FileIdProp = 1 FileGroupId = 1 Size = 0
MaxSize = 65535 Growth = 32768 Perf = 0
BackupLsn = (0:0:0) FirstUpdateLsn = (0:0:0)
OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL] MinSize = 0 Status = 0
UserShrinkSize = 65535 SectorSize = 512 MaxLsn = (0:0:0) FirstLsn = (0:0:0) CreateLsn = (0:0:0)
DifferentialBaseLsn = (0:0:0) DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000 FileOfflineLsn = (0:0:0) FileIdGuid = b0c06a0f-4ee5-43aa-92be-8f03dee24690
RestoreStatus = 0 RestoreRedoStartLsn = (0:0:0)
RestoreSourceGuid = 00000000-0000-0000-0000-000000000000 HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL]
TxfBackupLsn = [NULL] FstrContainerSize = [NULL]
MaxLsnBranchId = 00000000-0000-0000-0000-000000000000 SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL] ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0)
RestoreDifferentialBaseLsn = (0:0:0) RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000 RestorePathOrigin

DBCC Fileheader gives: minsize=131072, size=131072 and maxsize=-1

3

Restart SQL Server and see if they suddenly match.

There is a documented behaviour where if tempdb grows you can get different results depending on how you query what size it is. For example sys.master_files shows tempdb size on start; sys.database_files shows actual size (as do some of the Fileproperty() function results).

Nacho Alonso Portillo wrote on MSDN:

Well, it happens that when SQL Server autogrows a file which is part 
of TEMPDB, the size change is not reflected in sys.master_files (or
sys.sysaltfiles for that matter). As per the functional specifications of
the storage engine, for TEMPDB the change in the size of one of its files
is only reflected in those system tables for explicit grows and shrinks, 
not for those triggered by the automatic mechanisms.

https://blogs.msdn.microsoft.com/ialonso/2012/10/08/inaccurate-values-for-currently-allocated-space-and-available-free-space-in-the-shrink-file-dialog-for-tempdb-only/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.