Useful Multipliers

28. January 2009 by Bennett.

Multiply SQL database pages by 0.0078125 to get space in Megabytes
Multiply SQL database pages by 0.00000762939453125 to get space in Gigabytes

Here are a couple of examples of where this is useful:

This query uses a DMV to return TempDB utilization by object category such as user, internal, and version store:

SELECT
SUM(user_object_reserved_page_count)*0.0078125 as usr_obj_mb,
SUM(internal_object_reserved_page_count)*0.0078125 as internal_obj_mb,
SUM(version_store_reserved_page_count)*0.0078125  as version_store_mb,
SUM (unallocated_extent_page_count)*0.0078125 as freespace_mb,
SUM (mixed_extent_page_count)*0.0078125 as mixedextent_mb
FROM sys.dm_db_file_space_usage 

The next query returns space utilization , in Megabytes, by filegroup:

select name, filename, cast(size * 0.0078125 as int)as size_mb
from sysfiles
ORDER BY FILENAME
compute sum(cast(size * 0.0078125 as int))

Posted in T-SQL, SQL Server | Print | No Comments »