MSSQL 監(jiān)控?cái)?shù)據(jù)/日志文件增長(zhǎng)實(shí)現(xiàn)方法
前幾天,在所有數(shù)據(jù)庫(kù)服務(wù)器部署了監(jiān)控磁盤空間的存儲(chǔ)過(guò)程和作業(yè)后（MS SQL 監(jiān)控磁盤空間告警），今天突然收到了兩封告警郵件，好吧，存儲(chǔ)規(guī)劃是一方面，但是，是不是要分析一下是什么原因造成磁盤空間不足的呢？會(huì)不會(huì)是因?yàn)橥蝗槐┰龅娜罩疚募?，抑或是系統(tǒng)業(yè)務(wù)猛增導(dǎo)致數(shù)據(jù)量暴增，還是歷史數(shù)據(jù)累計(jì)原因....分析總得有數(shù)據(jù)來(lái)支撐吧，但是現(xiàn)在只有那些數(shù)據(jù)文件的當(dāng)前大小信息，沒有數(shù)據(jù)文件的歷史增長(zhǎng)變化信息，所以，今天就想實(shí)現(xiàn)這么一個(gè)功能，每天（頻率可以調(diào)整）去收集一下數(shù)據(jù)文件的信息，放到一個(gè)表里面，這樣方便我們分析數(shù)據(jù)文件的增長(zhǎng)演變例程，甚至你可以將數(shù)據(jù)文件的增長(zhǎng)幅度和業(yè)務(wù)變化關(guān)聯(lián)起來(lái)分析....
那么接下來(lái)就是我的設(shè)計(jì)思路和實(shí)現(xiàn)代碼，目前只是簡(jiǎn)單實(shí)現(xiàn)，以后將繼續(xù)優(yōu)化,豐富一些功能。
首先我們創(chuàng)建一個(gè)表DiskCapacityHistory，用來(lái)保存數(shù)據(jù)庫(kù)文件的歷史增長(zhǎng)變化信息：
USE msdb;
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'') AND xtype='U')
DROP TABLE DiskCapacityHistory;
GO
CREATE TABLE dbo.DiskCapacityHistory
(
[Date_CD] INT ,
[DataBaseID] INT ,
[FileID] INT ,
[DataBaseName] sysname ,
[LogicalName] VARCHAR(32) ,
[FileTypeDesc] NVARCHAR(60) ,
[PhysicalName] NVARCHAR(260) ,
[StateDesc] NVARCHAR(60) ,
[MaxSize] NVARCHAR(32) ,
[GrowthType] NVARCHAR(8) ,
[IsReadOnly] INT ,
[IsPercentGrowth] SMALLINT ,
[Size] FLOAT ,
[Growth_MOM_RAT] FLOAT ,
[Growth_YOY_RAT] FLOAT ,
CONSTRAINT PK_DiskCapacityHistory PRIMARY KEY(Date_CD, DataBaseID, FileID)
);
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '日期編碼'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'Date_CD';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '數(shù)據(jù)庫(kù)標(biāo)識(shí)'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'DataBaseID';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '文件標(biāo)識(shí)'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'FileID';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '數(shù)據(jù)庫(kù)名稱'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'DataBaseName';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '數(shù)據(jù)庫(kù)邏輯名稱'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'LogicalName';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '文件類型描述'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'FileTypeDesc';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '物理數(shù)據(jù)庫(kù)文件'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'PhysicalName';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '文件最大大小'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'MaxSize';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '文件增長(zhǎng)類型'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'GrowthType';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '是否只讀類型'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'IsReadOnly';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '是否按百分比增長(zhǎng)'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'IsPercentGrowth';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '數(shù)據(jù)文件大小(GB)'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'Size';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '文件增長(zhǎng)環(huán)比(%)'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'Growth_MOM_RAT';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
, @value = '文件增長(zhǎng)同比(%)'
, @level0type = N'SCHEMA'
, @level0name = N'dbo'
, @level1type = N'TABLE'
, @level1name = N'DiskCapacityHistory'
, @level2type = N'COLUMN'
, @level2name = N'Growth_YOY_RAT';
GO
IF OBJECT_ID(N'sp_diskcapacity_cal') IS NOT NULL
DROP PROCEDURE sp_diskcapacity_cal;
GO
接下來(lái)，我們創(chuàng)建存儲(chǔ)過(guò)程，負(fù)責(zé)來(lái)收集、統(tǒng)計(jì)這些數(shù)據(jù)庫(kù)的文件的相關(guān)信息。關(guān)于環(huán)比/同比,正常情況一般是:
環(huán)比: (指標(biāo)當(dāng)前值 - 指標(biāo)值(上個(gè)月同一天))/ 指標(biāo)值(上個(gè)月同一天) 。
同比: (指標(biāo)當(dāng)前值 - 指標(biāo)值(去年月同一天))/ 指標(biāo)值(去年月同一天) 。
其實(shí)如果關(guān)注每天的數(shù)據(jù)文件變化情況，這個(gè)代碼里面的環(huán)比、同比其實(shí)意義不大，其實(shí)我們可以這樣定義環(huán)比、同比：
環(huán)比： （指標(biāo)當(dāng)前值 - 指標(biāo)值（昨天）)/指標(biāo)值（昨天）。
同比： （指標(biāo)當(dāng)前值 - 指標(biāo)值 （上個(gè)月））/指標(biāo)值（上個(gè)月）
當(dāng)然，你也可以把這四個(gè)指標(biāo)都加上，對(duì)比參考，側(cè)重點(diǎn)不同而已。
IF OBJECT_ID(N'sp_diskcapacity_cal')IS NOT NULL
DROP PROCEDURE sp_diskcapacity_cal;
GO
CREATE PROCEDURE dbo.sp_diskcapacity_cal
AS
BEGIN
INSERT INTO dbo.DiskCapacityHistory
(
[Date_CD] ,
[DataBaseID] ,
[FileID] ,
[DataBaseName] ,
[LogicalName] ,
[FileTypeDesc] ,
[PhysicalName] ,
[StateDesc] ,
[MaxSize] ,
[GrowthType] ,
[IsReadOnly] ,
[IsPercentGrowth] ,
[Size]
)
SELECT CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)
AS DateCD ,
database_id AS DataBaseId ,
file_id AS FileID ,
DB_NAME(database_id) AS DataBaseName ,
name AS LogicalName ,
type_desc AS FileTypeDesc ,
physical_name AS PhysicalName ,
state_desc AS StateDesc ,
CASE WHEN max_size = 0 THEN N'不允許增長(zhǎng)'
WHEN max_size = -1 THEN N'自動(dòng)增長(zhǎng)'
ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'
END AS MaxSize ,
CASE WHEN is_percent_growth = 1
THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'
ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'
END AS Growth ,
Is_Read_Only AS IsReadOnly ,
Is_Percent_Growth AS IsPercentGrowth ,
CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS Size
FROM sys.master_files;
MERGE INTO dbo.DiskCapacityHistory DM USING
(
SELECT M.Date_CD ,
M.DataBaseID ,
M.FileID ,
CASE WHEN N.SIZE IS NULL OR N.SIZE = 0 THEN 0 ELSE
(M.SIZE - N.SIZE)/N.SIZE END AS Growth_MOM_RAT
FROM dbo.DiskCapacityHistory M
LEFT JOIN dbo.DiskCapacityHistory N ON
CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1, CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
AND M.DataBaseID = N.DataBaseID AND M.FileID = N.FileID
WHERE M.Date_CD = CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)
) TMP
ON
(
DM.Date_CD = TMP.Date_CD AND
DM.DatabaseId = TMP.DataBaseId AND
DM.FileId = TMP.FileId
)
WHEN MATCHED THEN UPDATE SET
DM.Growth_MOM_RAT = TMP.Growth_MOM_RAT;
END
GO
順便吐槽一下：由于前兩年一直使用ORACLE數(shù)據(jù)庫(kù)，很少接觸SQL SERVER，在實(shí)現(xiàn)上面功能的時(shí)候，我深深的體會(huì)到了ORACLE和SQL SERVER的巨大差距，如果用PL/SQL實(shí)現(xiàn)，那非常方便快捷，但是用T-SQL讓我遇到了幾個(gè)相當(dāng)痛苦地方，下面順便記錄對(duì)比一下吧：
一：由于我采用INT來(lái)保存日期數(shù)據(jù)，那么需要在DATE類型和INT類型之間轉(zhuǎn)換，我們來(lái)對(duì)比一下兩者的差別吧：
1.1 DATE類型轉(zhuǎn)換為整型:
T-SQL：
SELECT CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT);
PL/SQL：
SELECT TO_CHAR(Date_CD, 'YYYYMMDD') FROM DUAL;
1.2 整型轉(zhuǎn)換為DATE類型（字段DATE_CD）
T-SQL：
SELECT CAST(CAST(DATE_CD AS CHAR(8)) AS DATE) FROM TEST;
PL/SQL：
SELECT TO_DATE(DATE_CD, 'YYYY-MM-DD') FROM TEST;
結(jié)論: 純屬個(gè)人感受,從上面的腳本的簡(jiǎn)單性,方便性上,感覺ORACLE完勝SQL SERVER
二：計(jì)算數(shù)據(jù)文件增長(zhǎng)同比、環(huán)比值
1：SQL SERVER 2005 沒有MERGE語(yǔ)句功能，上面的腳本得改寫成
UPDATEdbo.DiskCapacityHistory
SET GROWTH_MOM_RAT =( SELECTCASE WHEN N.SIZE IS NULL
OR N.SIZE = 0 THEN 0
ELSE ( dbo.DiskCapacityHistory.SIZE
- N.SIZE ) / N.SIZE
END AS Growth_MOM_RAT
FROM dbo.DiskCapacityHistory N
WHERE CAST(CAST(dbo.DiskCapacityHistory.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH,
1,
CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
AND dbo.DiskCapacityHistory.DataBaseID = N.DataBaseID
AND dbo.DiskCapacityHistory.FileID = N.FileID
)
WHEREdbo.DiskCapacityHistory.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),
'-', '') AS INT)
UPDATEdbo.DiskCapacityHistory
SET GROWTH_YOY_RAT =( SELECTCASE WHEN N.SIZE IS NULL
OR N.SIZE = 0 THEN 0
ELSE ( dbo.DiskCapacityHistory.SIZE
- N.SIZE ) / N.SIZE
END AS Growth_YOY_RAT
FROM dbo.DiskCapacityHistory N
WHERE CAST(CAST(dbo.DiskCapacityHistory.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH,
12,
CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
AND dbo.DiskCapacityHistory.DataBaseID = N.DataBaseID
AND dbo.DiskCapacityHistory.FileID = N.FileID
)
WHEREdbo.DiskCapacityHistory.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),
'-', '') AS INT)
或
CREATE TABLE #DiskCapacityHistory
(
DATE_CD INT ,
DataBaseID INT ,
FileID INT ,
Growth_MOM_RAT FLOAT
) ;
INSERTINTO #DiskCapacityHistory
SELECT M.DATE_CD ,
M.DataBaseID ,
M.FileID ,
CASE WHEN N.SIZE IS NULL
OR N.SIZE = 0 THEN 0
ELSE ( M.SIZE - N.SIZE ) / N.SIZE
END AS Growth_MOM_RAT
FROM dbo.DiskCapacityHistory M ,
dbo.DiskCapacityHistory N
WHERE CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1,
CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
AND M.DataBaseID = N.DataBaseID
AND M.FileID = N.FileID
AND M.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE()
- 1, 120), '-', '') AS INT)
UPDATE dbo.DiskCapacityHistory
SET Growth_MOM_RAT = M.Growth_MOM_RAT
FROM #DiskCapacityHistory M
WHERE dbo.DiskCapacityHistory.DATE_CD = M.DATE_CD
AND dbo.DiskCapacityHistory.DataBaseID = M.DataBaseID
AND dbo.DiskCapacityHistory.FileID = M.FileID ;
2: 幸好SQL 2008還把ORACLE的MERGE的功能給模仿了過(guò)來(lái)，但是T-SQL缺少ORACLE數(shù)據(jù)庫(kù)強(qiáng)大的分析函數(shù)LAG，如果有這個(gè)，我計(jì)算環(huán)比，同比就非常方便了，一個(gè)SQL就搞定了，下面是個(gè)例子，本想把ORACLE的SQL也做個(gè)例子展現(xiàn)，但是又要建表、造數(shù)，折騰起來(lái)比較麻煩。
MERGE INTO DM.TM_WGGBO_IDCTOBUSVOLDTL_DAY DM
USING (
SELECT *
FROM (
SELECT DATE_CD,
CITY_ID,
IDC_NODE,
VOL_TYPE,
LAG(IDC_VOL_RAT ) OVER(PARTITION BY CITY_ID,IDC_NODE,VOL_TYPE,SUBSTR(DATE_CD,7,2) ORDER BY SUBSTR(DATE_CD,0,6)) AS IDC_MOM_RAT ,
LAG(IDC_VOL_RAT ) OVER(PARTITION BY CITY_ID,IDC_NODE,VOL_TYPE,SUBSTR(DATE_CD,5,4) ORDER BY SUBSTR(DATE_CD,0,4)) AS IDC_YOY_RAT ,
FROM DM.TM_WGGBO_IDCTOBUSVOLDTL_DAY
) T
WHERE EXISTS(SELECT 1 FROM ETL.T_IDCVOL_DAY_${ssid} WHERE DATE_CD = T.DATE_CD)
) TEMP
ON (
DM.DATE_CD = TEMP.DATE_CD AND
DM.CITY_ID = TEMP.CITY_ID AND
DM.IDC_NODE = TEMP.IDC_NODE AND
DM.VOL_TYPE = TEMP.VOL_TYPE
)
WHEN MATCHED THEN
UPDATE
SET DM.IDC_MOM_RAT = TEMP.IDC_MOM_RAT ,
DM.IDC_YOY_RAT = TEMP.IDC_YOY_RAT
COMMIT;
作者：瀟湘隱者
出處：http://www.cnblogs.com/kerrycode/
- 清空MSSQL日志 與set recovery simple
- mssql無(wú)數(shù)據(jù)庫(kù)日志文件恢復(fù)數(shù)據(jù)庫(kù)的方法
- mssql自動(dòng)備份及自動(dòng)清除日志文件服務(wù)器設(shè)置
- sqlserver 數(shù)據(jù)庫(kù)日志備份和恢復(fù)步驟
- sqlserver 日志恢復(fù)方法(搞定drop和truncate)
- SQLServer2005 沒有日志文件(*.ldf) 只有數(shù)據(jù)文件(*.mdf) 恢復(fù)數(shù)據(jù)庫(kù)的方法
- 收縮數(shù)據(jù)庫(kù)日志文件的方法(僅適用于mssql2005)
- MSSQL 2005/2008 日志壓縮清理方法小結(jié)
- 刪除sqlserver數(shù)據(jù)庫(kù)日志和沒有日志的數(shù)據(jù)庫(kù)恢復(fù)辦法
- 拯救你的數(shù)據(jù) 通過(guò)日志恢復(fù)MSSQL數(shù)據(jù)
相關(guān)文章
本文主要介紹了淺談SQL中Partition的相關(guān)用法,使用Partition可以根據(jù)指定的列或表達(dá)式將數(shù)據(jù)分成多個(gè)分區(qū),具有一定的參考價(jià)值,感興趣的可以了解一下2023-10-10
SQL Server高級(jí)內(nèi)容之case語(yǔ)法函數(shù)概述及使用
本文將詳細(xì)介紹下Case函數(shù)的用法感興趣的你可以參考下，或許對(duì)你有所幫助2013-03-03
盤點(diǎn)SqlServer?分頁(yè)方式和拉姆達(dá)表達(dá)式分頁(yè)
這篇文章主要介紹了SqlServer?5種分頁(yè)方式和拉姆達(dá)表達(dá)式分頁(yè)，進(jìn)來(lái)看看吧,本文結(jié)合示例代碼給大家講解的非常詳細(xì)，需要的朋友可以參考下2022-12-12
SQL SERVER數(shù)據(jù)庫(kù)的作業(yè)的腳本及存儲(chǔ)過(guò)程
本站文章旨在為該問(wèn)題提供解決思路及關(guān)鍵性代碼，并不能完成應(yīng)該由網(wǎng)友自己完成的所有工作，請(qǐng)網(wǎng)友在仔細(xì)看文章并理解思路的基礎(chǔ)上舉一反三、靈活運(yùn)用2015-10-10
SQL Server創(chuàng)建數(shù)據(jù)庫(kù)圖文教程
這篇文章主要介紹了SQL Server創(chuàng)建數(shù)據(jù)庫(kù)圖文教程，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-02-02
SqlServer強(qiáng)制斷開數(shù)據(jù)庫(kù)已有連接的方法
在執(zhí)行建庫(kù)腳本時(shí)，往往會(huì)先將原有的數(shù)據(jù)庫(kù)drop掉，由于SqlServer檢測(cè)到有數(shù)據(jù)連接時(shí)禁止執(zhí)行drop database操作，所以建庫(kù)腳本經(jīng)常執(zhí)行失敗，為此我們需要一種能強(qiáng)制斷開數(shù)據(jù)庫(kù)已有連接的方法,需要的朋友可以參考下2012-12-12
Sql Server中判斷表、列不存在則創(chuàng)建的方法
這篇文章主要給大家分享了Sql Server中判斷表、列是否存在，如果不存在則創(chuàng)建的方法，文章先給大家簡(jiǎn)單介紹了Sql Server中判斷表中某列是否存在的方法，文中給出了詳細(xì)的示例代碼，需要的朋友們可以參考借鑒，下面來(lái)一起看看吧。2017-03-03
dbeaver配置SQL?server連接實(shí)現(xiàn)
本文主要介紹了dbeaver配置SQL?server連接實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-07-07
索引利用數(shù)據(jù)結(jié)構(gòu)和搜索算法，在數(shù)據(jù)庫(kù)中加快數(shù)據(jù)的查找和訪問(wèn)速度，通過(guò)創(chuàng)建映射關(guān)系、有序排列和快速定位來(lái)提供高效的數(shù)據(jù)檢索機(jī)制，通過(guò)選擇適當(dāng)?shù)乃饕蛢?yōu)化查詢，可以提高數(shù)據(jù)庫(kù)的性能和效率，本文介紹sqlserver?如何給表添加索引，一起看看吧2023-10-10
淺析SQL Server的嵌套存儲(chǔ)過(guò)程中使用同名的臨時(shí)表怪像
這篇文章主要介紹了淺析SQL Server的嵌套存儲(chǔ)過(guò)程中使用同名的臨時(shí)表怪像,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-02-02