What volume to migrate first?

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



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"
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()
$version = "1.6"
write-host ""
Write-Host ("=" * 30) -ForegroundColor cyan
Write-Host "LdmStats version $version" -ForegroundColor yellow
Write-Host  ("=" * 30) -ForegroundColor cyan
#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"])) { 
    } else { 
        $physicalReplicasPrinted[$row["PhysicalReplicaId"]] = 1; 
$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!"