Thursday, November 15, 2012

0 Why the database file display the wrong size and has the negative number ?

Sometimes, we can see the database file size show different size than it is actual on the host. The available free space is even negative.

In this example, my tempdb shows only have 0.5 MB and the available free space is –1.00MB. How could it be?

image

After some research and particular thanks for this post  which make things much clear.

Story in short, the current allocate space number in SSMS is from sys.master_files which could be stale if the database has the auto grow turn on. The available free space is calculate base on below formula :

(current allocate space from sys.master_files – fileproperty from sys.database_files ) / 1024

  • The fileproerty function would scan GAM page which would always give the most recent used size.
  • The sys.database_files is system table specific for each database.

Here is the sample query which you can prove this

declare @filesize decimal(18,4)
declare @usedsize decimal(18,4)
declare @free decimal(18,4)
 
select @filesize = size * 8
from master.sys.master_files
where database_id = db_id('tempdb');
 
use tempdb;
select  @usedsize=fileproperty(name,'spaceused') * 8
from tempdb.sys.database_files where file_id =2;
 
select @filesize as "File size KB"
select @usedsize as "File used KB"
select @free =  ( @filesize - @usedsize) /1024
select @free as "Available Free MB"

 

image

 

Reference

Inaccurate values for “Currently allocated space” and “Available free space” in the Shrink File dialog for TEMPDB on: http://blogs.msdn.com/b/ialonso/archive/2012/10/08/inaccurate-values-for-currently-allocated-space-and-available-free-space-in-the-shrink-file-dialog-for-tempdb-only.aspx

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates