Easy implementation of SQL Server Database capacity Monitoring with PowerShell:30 Line Code

This paper introduces how to use PowerShell script to monitor the capacity of SQL Server database.

Don't talk much about gossip and get to the point.

First, build a table

Create a table for each server to record the capacity of each database on the server, using the server name as the table name.

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)

II. Data collection

Main points:

1. Mainly use sp_msforeachdb and sp_spaceused to obtain the capacity data of each database and simplify the code.

2. Today_use_size = total_szie-free_size

3. Growth_szie = today_use_size-yesterday_use_size

4. Chkservers.txt storage server name

5. Create a job and execute it regularly every day (if you need to adjust it to weekly or monthly execution, modify $yesterday=$today.adddays (- 1) and job frequency)

$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 lt 2) begin exec [?].. sp_spaceused end' "- ServerInstance $serverFor $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 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}

Effect picture: