Query same as GUI - current size vs. compressed

epetroepetro Posts: 69
edited September 6, 2014 6:53AM in SQL Storage Compress 6
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)

Comments

  • 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
    
  • Thank you very, very much for posting this- I'll investigate getting some formal documentation up on our site about this.
    adil
Sign In or Register to comment.