Query same as GUI - current size vs. compressed

epetro

I have been asked to provide data for a server move which should include the size of each database both native and compressed. I would really like to match the data available in the GUI on the 'Database Sizes' tab.

If anyone knows how/where to pull that data, please post.

Thanks.

(If I get this info from support I will provide it in an update)

epetro

I received notice from support that they use C# and TSQL to gather this data and, as a user, I will not have access to this information.

I wrote a script that would grab similar information and mark my compressed databases with a '1'. I then copied that to excel and filled in another column of %savings by hand.

use [Master]
SELECT [db]= db_name(sys.master_files.[database_id])
,CASE type_desc 
WHEN 'ROWS' THEN 'Data'
WHEN 'LOG'  THEN 'Log'  END AS [File Type]
,sum( [size]*8.0/1024 ) AS [File Size_MB]
,sum( [size]*8.0/1024/1024 ) AS [File Size_GB]
,case when t1.database_id is NULL then 0 else 1 end [is compressed]
FROM sys.master_files
left join (select distinct [database_id] from sys.master_files where physical_name like '%x')t1 on sys.master_files.database_id=t1.database_id
group by type_desc,t1.[database_id],sys.master_files.database_id

malikadil

Thank you very, very much for posting this- I'll investigate getting some formal documentation up on our site about this.