MSSQL 監控數據/日記文件增加完成辦法。本站提示廣大學習愛好者：（MSSQL 監控數據/日記文件增加完成辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是MSSQL 監控數據/日記文件增加完成辦法正文
頭幾天,在一切數據庫辦事器安排了監控磁盤空間的存儲進程和功課後（MS SQL 監控磁盤空間告警），明天忽然收到了兩封告警郵件，好吧，存儲計劃是一方面，然則，是否是要剖析一下是甚麼緣由形成磁盤空間缺乏的呢？會不會是由於忽然暴增的日記文件，抑或是體系營業猛增招致數據量暴增，照樣汗青數據累計緣由....剖析總得稀有據來支持吧，然則如今只要那些數據文件確當前年夜小信息，沒稀有據文件的汗青增加變更信息，所以，明天就想完成這麼一個功效，天天（頻率可以調劑）去搜集一下數據文件的信息，放到一個內外面，如許便利我們剖析數據文件的增加演化例程，乃至你可以將數據文件的增加幅度和營業變更聯系關系起來剖析....
那末接上去就是我的設計思緒和完成代碼，今朝只是簡略完成，今後將持續優化,豐碩一些功效。
起首我們創立一個表DiskCapacityHistory，用來保留數據庫文件的汗青增加變更信息：
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 = '數據庫標識'
, @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 = '文件標識'
, @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 = '數據庫稱號'
, @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 = '數據庫邏輯稱號'
, @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 = '物理數據庫文件'
, @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 = '文件增加類型'
, @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 = '能否按百分比增加'
, @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 = '數據文件年夜小(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 = '文件增加環比(%)'
, @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 = '文件增加同比(%)'
, @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
接上去，我們創立存儲進程，擔任來搜集、統計這些數據庫的文件的相干信息。關於環比/同比,正常情形普通是:
環比: (目標以後值 - 目標值(上個月統一天))/ 目標值(上個月統一天) 。
同比: (目標以後值 - 目標值(客歲月統一天))/ 目標值(客歲月統一天) 。
其實假如存眷天天的數據文件變更情形，這個代碼外面的環比、同比其實意義不年夜，其實我們可以如許界說環比、同比：
環比： （目標以後值 - 目標值（昨天）)/目標值（昨天）。
同比： （目標以後值 - 目標值 （上個月））/目標值（上個月）
固然，你也能夠把這四個目標都加上，比較參考，著重點分歧罷了。
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'不許可增加'
WHEN max_size = -1 THEN N'主動增加'
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數據庫，很少接觸SQL SERVER，在完成下面功效的時刻，我深深的領會到了ORACLE和SQL SERVER的偉大差距，假如用PL/SQL完成，那異常便利快捷，然則用T-SQL讓我碰到了幾個相當苦楚處所，上面趁便記載比較一下吧：
一：因為我采取INT來保留日期數據，那末須要在DATE類型和INT類型之間轉換，我們來比較一下二者的差異吧：
1.1 DATE類型轉換為整型:
T-SQL：
SELECT CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT);
PL/SQL：
SELECT TO_CHAR(Date_CD, 'YYYYMMDD') FROM DUAL;
1.2 整型轉換為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;
結論: 純屬小我感觸感染,從下面的劇本的簡略性,便利性上,感到ORACLE完勝SQL SERVER
二：盤算數據文件增加同比、環比值
1：SQL SERVER 2005 沒有MERGE語句功效，下面的劇本得改寫成
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的功效給模擬了過去，然則T-SQL缺乏ORACLE數據庫壯大的剖析函數LAG，假如有這個，我盤算環比，同比就異常便利了，一個SQL就弄定了，上面是個例子，本想把ORACLE的SQL也做個例子展示，然則又要建表、造數，折騰起來比擬費事。
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/
