Useful Multipliers

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))

This entry was posted in SQL Server, T-SQL. Bookmark the permalink.