How to convert sys.master_files size column into GB

07 September,2018 by Jack Vamvas

Question: I need to convert the sys.master_files size column into GB. Currently when I run select name,size from sys.master_files I get back the file size as KB. I've tried doing something    - size/1024/1024 - but I'm getting incorrect results.    

How do I get the current sys.master_files size unit into the GB format?

 

Answer: The size column in the sys.master_files is an int data type and is represented as 8-KB pages. In the example you've given above - you're not taking into account the 8-KB pages.    You need to multiply the size value by 8. i.e size * 8

Find an example of converting the 8-KB pages within a SQL statement. I also like cast the size column for greater accuracy.

select name,(cast(size as float)*8)/1024/1024 as FILE_SIZE_GB
 from sys.master_files 

Read more on getting database file information

SQL Server – sys.master_files and list all database files (SQL Server ...

SQL Server - Find the Database File Size and Log File Size

SQL Server – Differential Base Information


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to convert sys.master_files size column into GB


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer