POWERSHELL:30 easy implementation of SQL Server database capacity monitoring with line code

This article describes how to implement SQL Server database capacity monitoring with PowerShell scripts

Gossip is not much to say, straight into the subject

I. Establishment of the table

Create a table for each server that records the capacity of the server's individual databases, with the server name as the table name.

CREATE TABLE table_name ([log_date] [varchar] (null,[db_name) [varchar] (NULL,[TOTAL_SIZE_MB] [numeric] (15, 2) NULL,[USE_SIZE_MB] [numeric] (2) NULL,[FREE_SIZE_MB] [numeric] (2) NULL,[DAILY_GROWTH_MB] [numeric] (, 2) NULL DEFAULT ((0)))

Ii. data collection

Points:

1. The main use of SP_MSFOREACHDB, sp_spaceused cycle to obtain database capacity data, simplify the code

2. today_use_size = Total_szie-free_size

3. Growth_szie = Today_use_size-yesterday_use_size

4. Chkservers.txt Store Server name

5. Create the job, schedule it daily (if you need to adjust to weekly, monthly execution, modify $yesterday= $today. AddDays (-1) and operating 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,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}}}

：

650) this.width=650; "Src=" Https://s2.51cto.com/oss/201711/08/4e0068528ab369d8d5030774229a867f.jpg-wh_500x0-wm_3 -wmp_4-s_1487207650.jpg "title=" db_szie.jpg "alt=" 4e0068528ab369d8d5030774229a867f.jpg-wh_ "/>

This article is from the "Aimax" blog, make sure to keep this source http://aimax.blog.51cto.com/11610508/1980021

POWERSHELL:30 easy implementation of SQL Server database capacity monitoring with line code