Red Gate forums :: View topic - Query same as GUI - current size vs. compressed
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Storage Compress 6
SQL Storage Compress 6 forum

Query same as GUI - current size vs. compressed

Search in SQL Storage Compress 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Wed Apr 17, 2013 11:38 pm    Post subject: Query same as GUI - current size vs. compressed Reply with quote

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)
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Thu Apr 18, 2013 8:25 pm    Post subject: Reply from support - information unavailable Reply with quote

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:
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
Back to top
View user's profile Send private message
malikadil



Joined: 06 Sep 2014
Posts: 1

PostPosted: Sat Sep 06, 2014 11:53 am    Post subject: Reply with quote

Thank you very, very much for posting this- I'll investigate getting some formal documentation up on our site about this.
_________________
adil
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group