when i run xp_fixeddrives command to the drive that contains my tempdb, I obtain 10mb of free space, but the following query return other thing (45000 mb free).

other query :

USE tempdb;
SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

this drive only contain my tempdb db.

what is my error?

  • Why are you only counting unallocated pages? Are you trying to find free space in the tempdb data file(s), or free space on the drive itself? These are not the same thing. – Aaron Bertrand Mar 31 '15 at 15:37
  • Aaron, thanks. I will find the free space on the drive – Diego Flores Mar 31 '15 at 15:55

You're querying two different things here.

xp_fixeddrives will show you the amount of free space on the disk.

Using sys.dm views will tell you how much free space there is in the tempdb data file. If the data file is 50GB, then you're only using 5GB of it and the rest is empty. Even though it's empty, the file exists and is taking up 50GB of space on your drive.

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