Simple local check - t-sql / microsoft sql tablesize of a database size logger

How-to Articles
1

Same as before: Hope it is the right place to let this here…
If you like to draw size summary graphs for the tablesizes of all tables bigger than 100MB
Powershell to place in C:\ProgramData\checkmk\agent\local - and change it accordingly :wink:


#Invoke-sqlcmd Connection string parameters
$params = @{'server'='LOCALHOST,50014';'Database'='----database----'}
#Server to query WMI class win32_logicalDisks
$query1 = "Use ----database---- SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as ReservedSize FROM sys.dm_db_partition_stats, sys.objects  WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id GROUP BY sys.objects.name ORDER BY ReservedSize DESC"
$query2 = @"
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name
"@
$sqlresult = Invoke-Sqlcmd @params -Query $query2


foreach ($file in $sqlresult)
{
  #Write-Host $file.TableName
  #Write-Host $file.UnusedSpaceMB
  if ($file.UsedSpaceMB -gt 100)
  {
    #Write-Host $file.ItemArray
    $tname = $file.TableName
    $tsize =  $file.UsedSpaceMB
    #Write-Host $file.TableName
    #Write-Host $file.UsedSpaceMB
    $outconcat += "$tname=$tsize|"
    
  }
}

#$outconcat
Write-Host "0" """PS_----database----_TableSize""" $outconcat.Substring(0,$outconcat.Length-1) "Sizes of Tables"