SSAS Free drive space command
Today i was reviewing some traces and saw 2 commands i had never noticed before which had been issued by SSMS, these are shown below.
SystemGetLogicalDrives
SystemGetAllowedFolders
The commands can be executed in a MDX query session.
SystemGetLogicalDrives returns the free space in MB for the disk drives that are contained within the server property “AllowedBrowsingFolders”, see below for an example.
I have checked and the commands work in all versions of SSAS back to 2005 and anyone can run them.
Personally i think this is really useful and here a few reasons why.
- Cut sysadmins out of the loop when maintaining space (this is a good thing
).
- If your managing a remote dedicated SSAS server and have no access to the OS its a quick way to check space just like SQL Servers xp_fixeddrives & fn_fixeddrives()
- You might use it when collecting space stats or alerting.
- SystemGetAllowedFolders gives you the directories configured in “AllowedBrowsingFolders”.
- I hope you find this useful! I passed the info onto one of the Dev teams i work with and they certainly will make good use of it.
Categories: Analysis Services, Tips, Utilities
nice tip. thanks
Very handy command. Do you know of a way to use this to regularly report on free space on SSAS? I’m thinking like using it in an agent job that query the SSAS server for free space and logs it or emails it out. I need to be able to monitor the space I’m using on my server – I’m having trouble finding any canned reports out there or anything. Any thoughts?
Hi
Yes you could execute it using SQL Agent. 2 other ways that i also use are a vbscript that sends an alert when drive space reaches X and i also have a SSIS package using a WMI query that captures the storage stats and stores them in a cube for analysis.