Query same as GUI - current size vs. compressed

Silent data compression to optimize SQL Server Storage

Moderators: eddie davis, Colin Millerchip, fionag

Query same as GUI - current size vs. compressed

Postby epetro » Wed Apr 17, 2013 11:38 pm

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
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Reply from support - information unavailable

Postby epetro » Thu Apr 18, 2013 8:25 pm

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.

Code: Select all
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
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby malikadil » Sat Sep 06, 2014 11:53 am

Thank you very, very much for posting this- I'll investigate getting some formal documentation up on our site about this.
adil
malikadil
 
Posts: 1
Joined: Sat Sep 06, 2014 11:37 am


Return to SQL Storage Compress 6

Who is online

Users browsing this forum: No registered users and 0 guests