您好,登錄后才能下訂單哦!
本文介紹如何用PowerShell腳本實(shí)現(xiàn)SQL Server數(shù)據(jù)庫(kù)容量監(jiān)控
閑話就不多說(shuō),直入主題
一、建表
為每臺(tái)服務(wù)器創(chuàng)建一個(gè)表,用于記錄服務(wù)器各個(gè)數(shù)據(jù)庫(kù)的容量,以服務(wù)器名作為表名。
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)) )
二、數(shù)據(jù)收集
要點(diǎn):
1. 主要利用sp_msforeachdb、sp_spaceused循環(huán)獲取各數(shù)據(jù)庫(kù)容量數(shù)據(jù),簡(jiǎn)化代碼
2. today_use_size = total_szie - free_size
3. growth_szie = today_use_size - yesterday_use_size
4. chkservers.txt存放服務(wù)器名
5. 創(chuàng)建作業(yè),每天定時(shí)執(zhí)行(如需調(diào)整為每周、月執(zhí)行,修改$yesterday=$today.adddays(-1)及作業(yè)頻率即可)
$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 $server For($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 $MonitorServer if($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 } } }
效果圖:
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。