Query to find list of SSAS databases and its Sizes

_{Thursday, June 7, 2012 2:25 PM}

Does anyone have a solution to this ?? If its a DMV its better as I need to send this query to others and probably will be run on tens of machines

Pavan Keerthi

_{Thursday, June 7, 2012 6:02 PM ✅Answered}

If you right click on an SSAS database in Management Studio and get the Properties for the database, there is an estimated size. It's not always accurate, but if it does seem accurate for your cubes, then the following query may help.

I think the only way to retrieve that estimated size property is through the XMLA command DISCOVER_XML_METADATA. Since it returns XML, it's not that user friendly to consume. But the ASSP project did provide a very nice wrapper for it which will flatten the results into a rowset that is easy to consume in an SSIS package, for instance. The following command will return the EstimatedSize column (which is in bytes):

CALL ASSP.DiscoverXmlMetadataFull("\Databases\Database")

More about that particular sproc is documented here:
http://asstoredprocedures.codeplex.com/wikipage?title=DiscoverXmlMetadata&referringTitle=XmlaDiscover

The only downside of that approach is that you need to deploy the ASSP assembly to your 10 SSAS servers.

http://artisconsulting.com/Blogs/GregGalloway

_{Friday, June 8, 2012 9:57 AM}

Hi Pavan,

This link might help you:

http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

Regards,
Manoj
*Happy to help
http://experiencingmsbi.blogspot.com/

_{Friday, June 8, 2012 12:31 PM}

Thanks. We have ASSP on server and the query returned what we needed

Pavan Keerthi