通過Python實現對SQL Server 數據文件大小的監(jiān)控告警功能
更新時間：2021年04月23日 08:26:39 作者：東山絮柳仔
這篇文章主要介紹了通過Python實現對SQL Server 數據文件大小的監(jiān)控告警,本文給大家分享問題報錯信息及解決方案，需要的朋友可以參考下
1.需求背景
系統(tǒng)程序突然報錯，報錯信息如下：
The transaction log for database '@dbname' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
此時查看log文件，已達2T。
當時的緊急處理方案是，移除掉鏡像，修改數據庫恢復模式（由full修改為simple），收縮日志。
為了防止類似問題再次發(fā)生，需對log 文件的大小進行監(jiān)控，當到達閾值后，觸發(fā)告警。
2.主要基礎組件（類）
配置文件qqmssqltest_db_server_conf.ini
同過此配置文件獲取DB Server信息、DB信息、UID信息、郵件服務器信息等。
[sqlserver] db_user = XXXXXX db_pwd = XXXXXXX [sqlserver_qq] db_host = 110.119.120.114 db_port = 1433 [windows] user = pwd = [mail] host = zheshiceshidemail.qq.com port = 25 user = pwd = sender = zhejiushiceshidebuyaodangzhen@qq.com
獲取連接串的組件mssql_get_db_connect.py
# -*- coding: utf-8 -*-
import sys
import os
import datetime
import configparser
import pymssql
# pip3 install pymssql-2.1.4-cp37-cp37m-win_amd64.whl
# pip3 install pymssql -i https://pypi.doubanio.com/simple
# 獲取連接串信息
def mssql_get_db_connect(db_host, db_port):
db_host = db_host
db_port = db_port
db_ps_file = os.path.join(sys.path[0], "qqmssqltest_db_server_conf.ini")
config = configparser.ConfigParser()
config.read(db_ps_file, encoding="utf-8")
db_user = config.get('sqlserver', 'db_user')
db_pwd = config.get('sqlserver', 'db_pwd')
conn = pymssql.connect(host=db_host, port=db_port, user=db_user, password=db_pwd, charset="utf8", login_timeout=5, timeout=600, autocommit=True)
return conn
執(zhí)行SQL語句的組件mysql_exec_sql.py
# -*- coding: utf-8 -*-
import mysql_get_db_connect
def mysql_exec_dml_sql(db_host, db_port, exec_sql):
conn = mysql_get_db_connect.mysql_get_db_connect(db_host, db_port)
with conn.cursor() as cursor_db:
cursor_db.execute(exec_sql)
conn.commit()
def mysql_exec_select_sql(db_host, db_port, exec_sql):
conn = mysql_get_db_connect.mysql_get_db_connect(db_host, db_port)
with conn.cursor() as cursor_db:
cursor_db.execute(exec_sql)
sql_rst = cursor_db.fetchall()
return sql_rst
def mysql_exec_select_sql_include_colnames(db_host, db_port, exec_sql):
conn = mysql_get_db_connect.mysql_get_db_connect(db_host, db_port)
with conn.cursor() as cursor_db:
cursor_db.execute(exec_sql)
sql_rst = cursor_db.fetchall()
col_names = cursor_db.description
return sql_rst, col_names
發(fā)郵件的功能send_monitor_mail.py
# -*- coding: utf-8 -*-
# pip3 install PyEmail
import smtplib
from email.mime.text import MIMEText
import configparser
import os
import sys
# 發(fā)送告警郵件
def send_monitor_mail(mail_subject, mail_body, mail_receivers="testwukongbaigujing@qq.com"):
db_ps_file = os.path.join(sys.path[0], "qqmssqltest_db_server_conf.ini")
config = configparser.ConfigParser()
config.read(db_ps_file, encoding="utf-8")
mail_host = config.get('mail', 'host')
mail_port = config.get('mail', 'port')
# mail_user = config.get('mail', 'user')
# mail_pwd = config.get('mail', 'pwd')
sender = config.get('mail', 'sender')
# receivers = config.get('mail', 'receivers')
# 發(fā)送HTML格式郵件
message = MIMEText(mail_body, 'html', 'utf-8')
# message = MIMEText(mail_body, 'plain', 'utf-8')
message['subject'] = mail_subject
message['From'] = sender
message['To'] = mail_receivers
try:
smtpObj = smtplib.SMTP()
smtpObj.connect(mail_host, mail_port) # 25 為 SMTP 端口號
# SMTP AUTH extension not supported by server.
# https://github.com/miguelgrinberg/microblog/issues/76
# smtpObj.ehlo()
# smtpObj.starttls()
# smtpObj.login(mail_user, mail_pwd)
smtpObj.sendmail(sender, mail_receivers, message.as_string())
smtpObj.quit()
print("郵件發(fā)送成功")
except Exception as e:
print(e)
# except smtplib.SMTPException:
# print("Error: 無法發(fā)送郵件")
3.主要功能代碼
收集到的DB數據文件的信息保存到表mssql_dblogsize中，其建表的腳本如下：
CREATE TABLE [dbo].[mssql_dblogsize](
[id] [int] IDENTITY(1,1) NOT NULL,
[createtime] [datetime] NULL,
[vip] [nvarchar](100) NULL,
[port] [nvarchar](100) NULL,
[Environment] [nvarchar](200) NULL,
[Dbname] [varchar](200) NULL,
[Logical_Name] [varchar](200) NULL,
[Physical_Name] [varchar](1500) NULL,
[Size] [bigint] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[mssql_dblogsize] ADD DEFAULT (getdate()) FOR [createtime]
GO
為了方便對表mssql_dblogsize的數據進行管理和展示，在其基礎上抽象加工出了一個視圖v_mssql_dblogsize，注意Size大小的轉換（Size/128/1024 as SizeGB）
創(chuàng)建視圖的腳本如下：
CREATE view [dbo].[v_mssql_dblogsize]
as
SELECT [id]
,[createtime]
,[vip]
,[port]
,[Environment]
,[Dbname]
,[Logical_Name]
,[Physical_Name]
,Size/128/1024 as SizeGB
FROM [dbo].[mssql_dblogsize]
where size >50*128*1024
and Physical_Name like '%ldf%'
GO
本測試實例使用的數據庫為qqDB，監(jiān)控的各個DB Server保存在了表QQDBServer中，注意Port 不一定為標準端口1433.
collect_mssql_dblogsize_info.py
# -*- coding: utf-8 -*-
import sys
import os
import configparser
import pymssql
import mssql_get_db_connect
import mssql_exec_sql
from datetime import datetime
def collect_mssql_dblogsize_info():
db_ps_file = os.path.join(sys.path[0], "qqmssqltest_db_server_conf.ini")
config = configparser.ConfigParser()
config.read(db_ps_file, encoding="utf-8")
m_db_host = config.get('sqlserver_qq', 'db_host')
m_db_port = config.getint('sqlserver_qq', 'db_port')
# 獲取需要遍歷的DB列表
exec_sql_1 = """
SELECT IP, case Port when '1444,1433' then '1433' else Port end as Port, Environment
FROM qqDB.dbo.QQDBServer
where InUse =1 AND ServerType IN ('SQL')
and IP=VIP ;
"""
sql_rst_1 = mssql_exec_sql.mssql_exec_select_sql(m_db_host, m_db_port, exec_sql_1)
for j in sql_rst_1:
db_host_2 = j[0]
db_port_2 = j[1]
db_Environment = j[2]
exec_sql_2 = """
select '""" + db_host_2 + """' as vip, '""" + db_port_2 + """' as port, '""" + db_Environment + """' as Environment,DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, size
FROM master.sys.master_files;
"""
try:
sql_rst_2 = mssql_exec_sql.mssql_exec_select_sql(db_host_2, db_port_2, exec_sql_2)
except Exception as e:
print(e)
for k in sql_rst_2:
exec_sql_3 = """
insert into qqDB..mssql_dblogsize([vip], [port], [Environment], [Dbname], [Logical_Name], [Physical_Name], [Size])
values('%s', '%s', '%s', '%s', '%s', '%s', '%s');
"""
conn = mssql_get_db_connect.mssql_get_db_connect(m_db_host, m_db_port)
with conn.cursor() as cursor_db:
cursor_db.execute(exec_sql_3 % (k[0], k[1], k[2], k[3], k[4], k[5], k[6] ))
conn.commit()
collect_mssql_dblogsize_info()
告警郵件的功能實現為mssql_alert_dblogsize.py,此份代碼的告警閾值設置的為50G，數據來自于視圖v_mssql_dblogsize。
# -*- coding: utf-8 -*-
import sys
import os
import configparser
import pymssql
import mssql_get_db_connect
import mssql_exec_sql
import datetime
import send_monitor_mail
import pandas as pd
def mssql_alert_dblogsize():
mail_subject = "SQL Server DB Log Size Greater than 50G, please check!!! "
mail_receivers = "testDBAgrp@qtiantianq.com"
db_ps_file = os.path.join(sys.path[0], "qqmssqltest_db_server_conf.ini")
config = configparser.ConfigParser()
config.read(db_ps_file, encoding="utf-8")
m_db_host = config.get('sqlserver_qq', 'db_host')
m_db_port = config.getint('sqlserver_qq', 'db_port')
# 獲取需要遍歷的DB列表
exec_sql_4 = """
SELECT [vip] as IP,[port],[Environment],[Dbname]
,[Logical_Name],[Physical_Name],[SizeGB],[createtime]
FROM qqDB.[dbo].[v_mssql_dblogsize]
order by VIP,Dbname;
"""
sql_rst_4, col_name = mssql_exec_sql.mssql_exec_select_sql_include_colnames(m_db_host, m_db_port, exec_sql_4)
# print(sql_rst_4)
if len(sql_rst_4):
mail_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
columns = []
for i in range(len(col_name)):
columns.append(col_name[i][0])
df = pd.DataFrame(columns=columns)
for i in range(len(sql_rst_4)):
df.loc[i] = list(sql_rst_4[i])
mail_body = df.to_html(index=False, justify="left").replace('<th>', '<th style = "color:red; text-align:left; background-color: yellow">')
mail_html = "<html><body><h4>" + "Deal All : " + "<br><h4>" + "以下數據庫的db log文件，已大于50G.請及時檢查，謝謝！ " + "<br><h4>" + mail_body + "</body></html>"
send_monitor_mail.send_monitor_mail(mail_subject=mail_subject, mail_body=mail_html, mail_receivers=mail_receivers)
mssql_alert_dblogsize()
4.實現
定時任務是通過windows的計劃任務來實現的,在此不做過多的敘述。告警郵件的部分截圖如下:
5.附錄
1.報錯定位，判斷是不是log文件過大
https://blog.csdn.net/weixin_30785593/article/details/99912405
2.關于為什么數據庫log文件過大，我們可以參考以下分享的文章
https://blog.csdn.net/chinadm123/article/details/44941275
到此這篇關于通過Python實現對SQL Server 數據文件大小的監(jiān)控告警的文章就介紹到這了,更多相關PythonSQL Server 數據監(jiān)控告警內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- 查找sqlserver查詢死鎖源頭的方法 sqlserver死鎖監(jiān)控
- SQL Server 監(jiān)控磁盤IO錯誤,msdb.dbo.suspect_pages
- Windows下使用性能監(jiān)視器監(jiān)控SqlServer的常見指標
- SQL Server中使用Trigger監(jiān)控存儲過程更改腳本實例
- 利用SQL Server數據庫郵件服務實現監(jiān)控和預警
- Sql Server 死鎖的監(jiān)控分析解決思路
- Zabbix監(jiān)控SQL Server服務狀態(tài)的方法詳解
- zabbix監(jiān)控sqlserver的過程詳解
- SQL Server服務器監(jiān)控
相關文章
這篇文章主要介紹了pycharm如何設置官方中文(如何漢化)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-12-12
今天小編就為大家分享一篇對python中的控制條件、循環(huán)和跳出詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2019-06-06
這篇文章主要介紹了python 字典修改鍵(key)的幾種方法，小編覺得挺不錯的，現在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-08-08
這篇文章主要介紹了Python代碼調試的幾種方法總結,本文來自于IBM官方網站技術文檔,需要的朋友可以參考下2015-04-04
這篇文章主要為大家詳細介紹了python實現雙向鏈表原理，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-05-05
enumerate()是python的內置函數,適用于python2.x和python3.x,這篇文章主要給大家介紹了關于Python中的enumerate函數使用方法的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-06-06
這篇文章主要介紹了python tkinter組件擺放方式詳解,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2019-09-09
今天小編就為大家分享一篇python使用epoll實現服務端的方法，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-10-10
這篇文章主要全面剖析了Python的Django框架的部署技巧,包括Fabric等自動化部署和建立單元測試等方面,強烈推薦！需要的朋友可以參考下2015-04-04
這篇文章主要介紹了python小程序實現刷票功能詳解,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2019-07-07