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 on 28. January 2009 at 11:57 and is filed under T-SQL, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.

| Print