SQL Server自動化運維繫列：監控磁碟剩餘空間及SQL Server錯誤日誌

《SQL Server自動化運維繫列：監控效能指標指令碼》
《SQL Server自動化運維繫列：監控磁碟剩餘空間及SQL Server錯誤日誌》
《SQL Server自動化運維繫列：關於郵件通知那點事》
《SQL Server自動化運維繫列：監控跑批Job執行狀態》
《SQL Server自動化運維繫列：關於資料收集》

需求描述

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

在所有的自檢流程中最基礎的一個就是磁碟剩餘空間檢測。作為一個高效的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 ="\\"+$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檔案匯入：

（檢視大圖）

如此分析就方便多了。

結語

本篇就列舉了一下利用PowerShell實現自動化運維和檢測。算作拋磚引玉了吧，自己另有需求可以自己靈活實現。

關於SQL Server自動化運維和檢測的內容很廣泛，其中很多都是從日常的經驗中出發，一步步的從手動到自動的過程。

後面的文章，我們將會更深入關於SQL Server的自動化優化運維進行分析。有興趣的童鞋，可以提前關注。