Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

SSAS – Find Cube Database Name, Size,Status and Total Size of SSAS Server

We are planning to take SSAS cube database backup since our backup tool does full file system backup in case of recovery, restoration is taking more time and hence as a first step we are started gathering the number of cubes,status and total size of cube databases .

SQLAS provider used for navigating the Analysis Management Object (AMO) hierarchy. You must import the SQLPS module before you can use the SQLAS provider and cmdlets. The SQLAS provider is an extension of the SQLServer provider. There are several ways to import the SQLPS module.

Please refer by earlier post to load SMO’s, if it’s not loaded.

http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2013/05/01/powersql-list-sql-server-instance-configuration-details-and-sending-an-automated-email/

Create a file SSAS1.PS1 and paste the below content

********************************************************
Param($ServerName=”localhost”)
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
Write-Output (“Server ‘{0}’ not found” -f $ServerName)
break
}

$sum=0
foreach ($d in $server.Databases )
{
Write-Output ( “Database: {0}; Status: {1}; Size: {2}MB” -f $d.Name, $d.State, ($d.EstimatedSize/1024/1024).ToString(“#,##0″) )
$sum=$sum+$d.EstimatedSize/1024/1024
}

$SizeGB=$Sum/1024

write-host ‘Sum of Database = ‘$sum ‘ MB’
Write-host ‘Total Size of Cube Databases =’ $SizeGB ‘ GB’

***************************************************************

Open PowerShell – Type PowerShell in command prompt

PS C:\> .\SSAS1.PS1 <serverName>

Or Command Prompt
Powershell.exe c:\ssas1.ps1 <ServerName>

PS E:\> .\ssas.ps1 TEST
Database: Daily; Status: Processed; Size: 2,180MB
Database: Test; Status: Processed; Size: 49MB
Database: PP04; Status: Processed; Size: 57MB
Database: Trans; Status: Processed; Size: 1,613MB
Database: Complaint; Status: Processed; Size: 313MB
Database: PI; Status: Processed; Size: 8,687MB
Database: PI_Jey; Status: Processed; Size: 8,807MB
Database: PI_Trng; Status: Processed; Size: 8,474MB
Database: LG; Status: Processed; Size: 56MB
Database: VPP; Status: PartiallyProcessed; Size: 7MB
Database: Assign; Status: Processed; Size: 1,551MB
Database: Cubes; Status: Processed; Size: 3,044MB
Database: Cube; Status: Processed; Size: 3,048MB
Database: Tele; Status: Processed; Size: 2MB
Database: Pricing; Status: Processed; Size: 2,140MB
Database: DMDB; Status: Unprocessed; Size: 0MB
Database: LG cube; Status: Processed; Size: 62MB
Database: Manual; Status: PartiallyProcessed; Size: 2MB
Database: Tutorial; Status: Processed; Size: 10MB
Database: DW ; Status: Processed; Size: 32MB
Database: OLAP; Status: Processed; Size: 86MB
Database: SSAS; Status: Unprocessed; Size: 0MB
Sum of Database = 40220.1397724152 MB
Total Size of Cube Databases = 39.2774802464992 GB


Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...