PowerShell：30行代码轻松实现SQL Server数据库容量监控

本文介绍如何用PowerShell脚本实现SQL Server数据库容量监控

闲话就不多说，直入主题

一、建表

为每台服务器创建一个表，用于记录服务器各个数据库的容量，以服务器名作为表名。

CREATE TABLE table_name([LOG_DATE] [varchar](20) NULL,[DB_NAME] [varchar](50) NULL,[TOTAL_SIZE_MB] [numeric](15, 2) NULL,[USE_SIZE_MB] [numeric](15, 2) NULL,[FREE_SIZE_MB] [numeric](15, 2) NULL,[DAILY_GROWTH_MB] [numeric](15, 2) NULL DEFAULT ((0)))

二、数据收集

要点：

1. 主要利用sp_msforeachdb、sp_spaceused循环获取各数据库容量数据，简化代码

2. today_use_size = total_szie - free_size

3. growth_szie = today_use_size - yesterday_use_size

4. chkservers.txt存放服务器名

5. 创建作业，每天定时执行(如需调整为每周、月执行，修改$yesterday=$today.adddays(-1)及作业频率即可)

$MonitorServer='' $servers=gc F:\DBA\chkservers.txt$today=Get-Date$log_date=$today.toString('yyyyMMdd')$yesterday=$today.adddays(-1)$compare_date=$yesterday.toString('yyyyMMdd')Foreach($server in $servers){if($server.length -gt 0){$results=invoke-sqlcmd "exec sp_msforeachdb 'if(db_id(''?'') not in(1,2,3,4)) begin exec [?]..sp_spaceused end'" -ServerInstance $serverFor($n=0;$n -lt $results.length;$n=$n+2){$db_name=$results[$n].database_name$db_total_size=$results[$n].database_size$db_free_size=$results[$n].'unallocated space'$total_size=$db_total_size.substring(0,$db_total_size.length-3)$free_size=$db_free_size.substring(0,$db_free_size.length-3)$today_use_size=$total_size-$free_size$count=invoke-sqlcmd "select count(1) as count from DBMonitor..$server where LOG_DATE='$compare_date' and DB_NAME='$db_name'" -ServerInstance $MonitorServerif($count.count -gt 0){$comp_results=invoke-sqlcmd "select MAX(USE_SIZE_MB) as USE_SIZE_MB from DBMonitor..$server where LOG_DATE='$compare_date' and DB_NAME='$db_name'" -ServerInstance $MonitorServer$yesterday_use_size=$comp_results.USE_SIZE_MB$growth_size=$today_use_size-$yesterday_use_size}else{$growth_size=0}invoke-sqlcmd "insert into DBMonitor..$server select '$log_date','$db_name',$total_size,$today_use_size,$free_size,$growth_size" -ServerInstance $MonitorServer}}}

效果图：