Sid Ramesh wrote a fantastic script that provides great LDM statistics amongst which how many extents a data source uses. This helps to determine which volume to migrate first to reduce extents if there is a need to. Sample output is shown below followed by the actual script.
A companion post on LDM information can be found here.
#begin script
#---------------------------------
#LdmStats.Ps1
param ()
#region queries
#-- Count only extents from dynamic disks
$ExtentCountCmd = "select count(*) as TotalNumberOfDynamicExtents from tbl_SPM_Extent extent where extent.DiskID in (select DiskID from tbl_SPM_Disk where DiskType = 1) "
#-- Count the number of unique PhysicalReplicas
$PhysReplCountCmd = "select physicalreplicaid as PhysicalReplicaId, COUNT(*) as Count from tbl_PRM_LogicalReplica where physicalreplicaid is not null and datasourceid is not null group by PhysicalReplicaId"
#-- Count the number of datasources
$DScountCmd = "select COUNT(*) as TotalNumberOfDatasources from tbl_IM_Datasource where ProtectedGroupId is not null"
#-- Count the number of volumes
$VolCountCmd = "select COUNT(*) as NumberOfVolumes from tbl_SPM_Volume where VolumeSetID is not null"
#-- Get the LDM alerts
$LDMAlertsCmd = "select AlertId, Type, OccuredSince, Resolution, ResolvedTime from tbl_AHP_Alerts where Type = 63 or Type = 64 order by OccuredSince desc "
#-- Get the datasources with the maximum extents (useful for migration)
$DSwithExtentsCmd = "SELECT (select DataSourceName from tbl_IM_Datasource ds2 where DataSourceId = DS.DataSourceId) as DatasourceName,
(select pg.FriendlyName from tbl_IM_Datasource ds2 join tbl_IM_ProtectedGroup pg on ds2.ProtectedGroupId = pg.ProtectedGroupId
where DataSourceId = DS.DataSourceId) as ProtectionGroupName,
COUNT(Extent.DiskID) as NumberOfExtents, replica.PhysicalReplicaId as PhysicalReplicaId, DS.DatasourceId as DatasourceId
FROM tbl_SPM_Extent Extent WITH (NOLOCK)
JOIN dbo.tbl_SPM_Volume Volume WITH (NOLOCK)
ON Extent.GuidName = Volume.GuidName
JOIN dbo.tbl_PRM_LogicalReplica Replica WITH (NOLOCK)
ON Replica.PhysicalReplicaId = Volume.VolumeSetID
JOIN dbo.tbl_IM_Datasource DS WITH (NOLOCK)
ON Replica.DataSourceId = DS.DataSourceId
GROUP BY DS.DataSourceId, Replica.PhysicalReplicaId
Order by NumberOfExtents desc"
#-- Get all the Replica and SC disk threshold exceeded alerts along with the corresponding datasources
$GetDiskAlertsCmd = "select aa.AlertId, ra.DatasourceId, Type as AlertType, OccuredSince, Resolution, ResolvedTime from tbl_AHP_Alerts aa
JOIN tbl_PRM_ReplicaAlerts ra WITH (NOLOCK)
ON aa.AlertId = ra.AlertId
where Type = 31 or Type = 36"
#endregion
function DisplayAndSelect {
param ($list, $item)
# unified forced select from list by index number
Write-Host ""
Write-Host "Select $($item.toupper()) from list below:"
for ($i = 0; $i -lt $list.count; $i++) {
write-host "`t -> [$i] $($list[$i])"
}
Write-Host ""
$i = – 1
while (($i -lt 0) -or ($i -ge $list.count)) {
$now = (Get-Date).ToString($format)
$i = [int](Read-Host "[$now] Enter index number")
}
write-host "Selected $($list[$i])`n"
return $list[$i]
}
function GetSqlServers {
# return SQL instances that the SQL browser service can find
write-host "Searching for SQL service instances, this may take a while..."
return [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
}
#START
$version = "1.6"
write-host ""
Write-Host ("=" * 30) -ForegroundColor cyan
Write-Host "LdmStats version $version" -ForegroundColor yellow
Write-Host ("=" * 30) -ForegroundColor cyan
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.RegisteredServers")
#ensure browser runs
$sb = Get-Service | ? {$_.name -match "sqlbrowser"}
if (!$sb) {Throw "No SQL browser service found!"}
if ($sb.status -ne "running") {$sb.start; sleep 500}
# get sql server list
$SQLinstances = @(GetSqlServers)
$tmplist = @()
# build SERVER\INSTANCE format
$SQLinstances | foreach {$tmplist += "$($_.servername)\$($_.instancename)"}
$SQLinstances = @($tmplist)
$sqlserver = DisplayAndSelect $SQLinstances "SQLserver"
$dpmservername = (&hostname)
Write-Host "Connecting [$sqlserver]..."
$srvr = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlserver
$db = $srvr.Databases["DPMDB"]
$ExtentCount = $db.ExecuteWithResults($ExtentcountCmd).Tables[0].rows[0].TotalNumberOfDynamicExtents
$DSCount = $db.ExecuteWithResults($DScountCmd).Tables[0].rows[0].TotalNumberOfDatasources
$volcount = $db.ExecuteWithResults($VolcountCmd).Tables[0].rows[0].NumberOfVolumes
$dpmRemaining = [math]::truncate(600 - $volcount)
$diskcount = (Get-DPMDisk $dpmservername).count
Write-Host "Total disks".PadRight(20) ": $diskcount"
Write-Host "Total volumes".PadRight(20) ": $volcount"
Write-Host "Total extents".PadRight(20) ": $ExtentCount"
Write-Host "Total data sources".PadRight(20) ": $DSCount"
$usedslots = 1 + $diskcount + (2*$volcount) + $ExtentCount
$slotsRemaining = 2960-$usedslots
$volRemaining = [math]::Truncate($slotsRemaining/3)
if ($dpmRemaining -lt $volRemaining) {$dsRemaining = [math]::Truncate($dpmRemaining/2)}
else {$dsRemaining = [math]::Truncate($volRemaining/2)}
Write-Host "Number of non-colocated data sources that can still be added: $dsRemaining"
Write-Host "`nData source extent list..."
$DSwithExtentsTable = $db.ExecuteWithResults($DSwithExtentsCmd).Tables[0]
$DSwithExtentsRows = $DSwithExtentsTable.rows
$physicalReplicasPrinted = @{}
foreach ($row in $DSwithExtentsRows)
{
if ($physicalReplicasPrinted.Contains($row["PhysicalReplicaId"])) {
$row.Delete()
} else {
$physicalReplicasPrinted[$row["PhysicalReplicaId"]] = 1;
}
}
$DSwithExtentsTable.AcceptChanges()
$DSwithExtentsRows | ft –AutoSize
Write-Host "`nReplica colocation counts..."
$PhysReplCount = $db.ExecuteWithResults($PhysReplCountCmd).Tables[0].rows
$PhysReplCount | ft –AutoSize
Write-Host "`nLDM alerts list..."
$LDMAlerts = $db.ExecuteWithResults($LDMAlertsCmd).Tables[0].rows
if ($LDMAlerts.Count -gt 0 ) {
$LDMAlerts | ft –AutoSize
}
else {write-host "None found!" -f white}
Write-Host "`nDone!"
#---------------------------------