Joined: 31 May 2011
Location: Zotec Partners
|Posted: Thu Apr 18, 2013 8:25 pm Post subject: Reply from support - information unavailable
|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.
SELECT [db]= db_name(sys.master_files.[database_id])
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]
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