SQL Server自動化運維系列——監控磁盤剩余空間及SQL Server錯誤日志（Power Shell）

2024-08-31 00:54:41

字體：大中小

來源：轉載

供稿：網友

需求描述

在我們的生產環境中，大部分情況下需要有自己的運維體制，包括自己健康狀態的檢測等。如果發生異常，需要提前預警的，通知形式一般為發郵件告知。

在所有的自檢流程中最基礎的一個就是磁盤剩余空間檢測。作為一個高效的DBA不可能每天都要上生產機上查看磁盤剩余或者直到磁盤無剩余空間報錯后才采取擴容措施。

當然，作為微軟的服務器有著自己的監控軟件：SCCM(System Center Configuration Manager)。但本篇就介紹如果通過Power shell實現狀態值監控，相比SCCM更輕量級和更具靈活性。

本篇實現

1、每天檢測磁盤剩余空間大小，如果剩余空間超過了閥值，則發郵件告訴管理員

2、每天檢測SQL Server運行的錯誤日志（Window平臺的錯誤日志），形成郵件附件發送給管理員

監控腳本

首先我們來解決第一個問題，關于磁盤剩余空間的問題，對于磁盤的監控的存在兩個需要解決的問題：

<1>一般監控我們需要監控很多臺服務器的磁盤，所以對于服務器的量控制我們需要生成一個配置文件。

<computernames>        <computername>                wuxuelei-pc        </computername></computernames>

配置文件名字：computername.xml，這樣就解決很多服務器的問題，只需要在配置文件中增加就可以，因為我在本地測試，所以就配置了我的本地電腦

<2>對于服務器的磁盤監控也需要定義一個閥值，用來動態改變，用來定義生成預警的閥值。簡單點就是定義我們檢測的磁盤剩余空間到了多少了就自動發郵件提醒。

<Counters>        <Counter alter = "10" Operator = "gt" >C:</Counter>        <Counter alter = "10" operator = "gt" >D:</Counter>        <Counter alter = "10" operator = "gt" >E:</Counter>        <Counter alter = "10" operator = "gt" >F:</Counter></Counters>

文件名為：alter_disk.xml，我定義了四個盤符：C: D: E: F:

當以上四個盤符那個盤符少于10G了就生成郵件預警。

實現代碼如下：

$server = "(local)"$uid = "sa"$db="master"$pwd="passWord"$mailPRfname = "TestMail"$recipients = "787449667@QQ.com"$subject = "老大，快去看看這個服務器的磁盤空間馬上就不夠了！"$computernamexml = "F:/PowerShell/發送郵件/computername.xml"$alter_xml = "F:/PowerShell/發送郵件/alter_disk.xml"function GetServerName($xmlpath){    $xml = [xml] (Get-Content $xmlpath)    $return = New-Object Collections.Generic.List[string]    for($i = 0;$i -lt $xml.computernames.ChildNodes.Count;$i++)    {        if ( $xml.computernames.ChildNodes.Count -eq 1)        {            $cp = [string]$xml.computernames.computername        }        else        {            $cp = [string]$xml.computernames.computername[$i]        }        $return.Add($cp.Trim())    }    $return}function GetAlterCounter($xmlpath){    $xml = [xml] (Get-Content $xmlpath)    $return = New-Object Collections.Generic.List[string]    $list = $xml.counters.Counter    $list}function CreateAlter($message){    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection     $CnnString ="Server = $server; Database = $db;User Id = $uid; Password = $pwd"     $SqlConnection.ConnectionString = $CnnString     $CC = $SqlConnection.CreateCommand();     if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }         $cc.CommandText=            " EXEC msdb..sp_send_dbmail              @profile_name  = '$mailprfname'            ,@recipients = '$recipients'            ,@body = '$message'            ,@subject = '$subject'            "    $cc.ExecuteNonQuery()|out-null     $SqlConnection.Close();}$names = GetServerName($computernamexml)$pfcounters = GetAlterCounter($alter_xml)foreach($cp in $names){    $p = New-Object Collections.Generic.List[string]    $report = ""    foreach ($pfc in $pfcounters)    {        $b = ""        $counter ="http://"+$cp+"/LogicalDisk("+$pfc.get_InnerText().Trim()+")/% Free Space"        $p.Add($counter)            }        $count = Get-Counter $p    #Write-Host $count.CounterSamples.Count;    for ($i = 0; $i -lt $count.CounterSamples.Count; $i++)    {        $v = $count.CounterSamples.Get($i).CookedValue        $pfc = $pfcounters[$i]        #$pfc.get_InnerText()        $b = ""        $lg = ""       if ($pfc.operator -eq "gt")        {            if( $v -le [double]$pfc.alter)                {                    $b = "alter"                    $lg = "Less Than"                }                if($b -eq "alter")                {                    $path = "機器名："+$cp+"; 盤符："+$pfc.get_InnerText()                    $diskFree="; 當前剩余空間為："+[math]::truncate($v).ToString()+"G;"                    $aler=" 超過了你預定的閥值："+$pfc.alter.Trim()+"G;速度去處理吧！"                                $item = "{0} {1} {2}" -f $path,$diskFree,$aler                    $report += $item + "`n"                }        }               }    if($report -ne "")    {        #生產警告 參數 計數器，閥值，當前值        CreateAlter $report    }}

通過如上腳本，生成跑批任務，就可以自動的檢測磁盤剩余空間了，而不需要我們去手動檢測了。

上述代碼中，有兩個技術點：1、需要自己配置SQL Server郵件代理；2、需要自己配置跑批計劃，方法自己網上搜，很簡單。

效果圖如下

嘿...看到上面的郵件，就是需要聯系硬件架構師，讓其擴容或者清除冗余數據的時候了。

在上面的腳本中，的確只有我的F盤符超了閥值，所以警報了！

其次，我們來解決第二個問題，關于SQL Server錯誤日志的問題，作為日常的DBA管理系統中，查看SQL Server錯誤日志是一種常用的方式。當然，如果系統運行正常，不會產生錯誤日志，一單出現問題，就會生成錯誤日志，但是對于SQL Server錯誤日志會產生很多條，為了方便查找，我們會讓其生成一個文本文件。

以郵件附件的形式，告知管理員，然后管理員就可以通過日志文件快速的定位問題的源頭。

同樣，本篇也是通過computername.xml文件，對多臺服務器進行錯誤日志進行篩選

腳本很簡單，如下

$server = "(local)"$uid = "sa"$db="master"$pwd="password"$mailprfname = "TestMail"$recipients = "787449667@qq.com"$subject = "老大，快去看看這個服務器的SQL Server出問題了！"$Info="附件為SQL Server錯誤日志....."$computernamexml = "F:/PowerShell/發送郵件/computername.xml"function GetServerName($xmlpath){    $xml = [xml] (Get-Content $xmlpath)    $return = New-Object Collections.Generic.List[string]    for($i = 0;$i -lt $xml.computernames.ChildNodes.Count;$i++)    {        if ( $xml.computernames.ChildNodes.Count -eq 1)        {            $cp = [string]$xml.computernames.computername        }        else        {            $cp = [string]$xml.computernames.computername[$i]        }        $return.Add($cp.Trim())    }    $return}function GetAlterCounter($xmlpath){    $xml = [xml] (Get-Content $xmlpath)    $return = New-Object Collections.Generic.List[string]    $list = $xml.counters.Counter    $list}function CreateAlter($message){    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection     $CnnString ="Server = $server; Database = $db;User Id = $uid; Password = $pwd"     $SqlConnection.ConnectionString = $CnnString     $CC = $SqlConnection.CreateCommand();     if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }         $cc.CommandText=            " EXEC msdb..sp_send_dbmail              @profile_name  = '$mailprfname'            ,@recipients = '$recipients'            ,@body = '$Info'            ,@subject = '$subject'            ,@file_attachments='$message'            "    $cc.ExecuteNonQuery()|out-null     $SqlConnection.Close();}$names = GetServerName($computernamexml)foreach($cp in $names){        #輸出系統日志中某個特定程序的日志到文件，比如SQL Server,然后選擇錯誤類型為Error        Get-EventLog -ComputerName  $cp application | where-object{$_.source-like "MSSQLSERVER"} | where-object{$_.entryType -eq "error"} >F:/PowerShell/$cp+"SQLErrLog.txt"                  #生產警告 參數 計數器，閥值，當前值        CreateAlter F:/PowerShell/$cp+"SQLErrLog.txt"}

效果圖如下

直接下載附件，然后查看錯誤日志就可以了。

該錯誤日志還是挺詳細的，發生時間，錯誤明細等

當然，上述腳本還欠缺一點邏輯：

比如：篩選一段時間周期的錯誤日志、或者只查看上次啟動之后的錯誤日志等。

這些邏輯加上也不麻煩，本身PowerShell實現起來就很簡單。這里就不展開了，自己靈活實現。

但是，在我們日常的分析中，在日志記錄多的時候，用文本分析的方式還是比較慢，通常用Excel查看效果會好很多。

這個實現也不麻煩，只需要更改腳本如下：

        #輸出系統日志中某個特定程序的日志到文件，比如SQL Server,然后選擇錯誤類型為Error        #Get-EventLog -ComputerName  $cp application | where-object{$_.source-like "MSSQLSERVER"} | where-object{$_.entryType -eq "error"} >F:/PowerShell/$cp+"SQLErrLog.txt"        #輸出系統日志中某個特定程序的日志到文件，比如SQL Server,然后選擇錯誤類型為Error,支持Excel打開        Get-EventLog -ComputerName  $cp application | where-object{$_.source-like "MSSQLSERVER"} | where-object{$_.entryType -eq "error"} | Export-Clixml –Path F:/PowerShell/$cp+"SQLErrLog.xml" –Depth 2                   #生產警告 參數 計數器，閥值，當前值        CreateAlter F:/PowerShell/$cp+"SQLErrLog.xml"

我們下載生成的日志文件，然后打開Excel，然后選擇：數據——>從Xml文件導入：