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/
相关推荐
-
sqlserver 日志恢复方法(搞定drop和truncate)
--1 use master backup database logTest to disk='D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest.bak' with format --2 use logTest create table table_1(Id int, test varchar(max)) insert table_1(Id,test) values (1,'3
-
删除sqlserver数据库日志和没有日志的数据库恢复办法
一.删除数据库日志文件的方法 你曾经有在执行SQL的时候,数据库报事务日志已满,然后执行报错.然后纠结于怎么删除数据库日志,捣鼓半天吗,现在就提供两种删除日志文件的方法,希望能够帮到你! 方法一:手工操作1.数据库->右键->属性->选项-恢复模式->由完成切换成简单2.数据库->右键->任务->收缩-文件->由完成切换成简单->文件类型->日志->将文件收缩到 方法二:存储过程代替手工操作 复制代码 代码如下: --日志文件收缩至多少M
-
清空MSSQL日志 与set recovery simple
由于服务器上的数据库使用很长时间了日志文件已经是好几个了 数据库服务器的一直采用的备份我想应该可以放弃以前的日志文件了所以清理以前的日志文件 我现在想的问题是在利用备份文件恢复的时候 要不要使用到日志文件呢 一会再查资料找下,如果不会的话,就使用简单的数据模式了,节约日志文件占的空间 1．打开查询分析器,输入命令 DUMP TRANSACTION 数据库名称 WITH NO_LOG 2.再打开企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件--选择日志文件--在收缩方式
-
mssql无数据库日志文件恢复数据库的方法第1/2页
方法一 1.新建一个同名的数据库 2.再停掉sqlserver(注意不要分离数据库) 3.用原数据库的数据文件覆盖掉这个新建的数据库 4.再重启sqlserver 5.此时打开企业管理器时会出现置疑,先不管,执行下面的语句(注意修改其中的数据库名) 6.完成后一般就可以访问数据库中的数据了,这时,数据库本身一般还要问题,解决办法是,利用 数据库的脚本创建一个新的数据库,并将数据导进去就行了. USEMASTER GO SP_CONFIGURE'ALLOWUPDATES',1RECONFIGURE
-
sqlserver 数据库日志备份和恢复步骤
--创建测试数据库 CREATE DATABASE Db GO --对数据库进行备份 BACKUP DATABASE Db TO DISK='c:\db.bak' WITH FORMAT GO --创建测试表 CREATE TABLE Db.dbo.TB_test(ID int) --延时1秒钟,再进行后面的操作(这是由于SQL Server的时间精度最大为百分之三秒,不延时的话,可能会导致还原到时间点的操作失败) WAITFOR DELAY '00:00:01' GO --假设我们现在误操作删
-
MSSQL 2005/2008 日志压缩清理方法小结
适用于SQL Server 2005的方法 --------------------------------------------- 复制代码 代码如下: USE DNName GO 1,清理日志 复制代码 代码如下: Backup Log DNName WITH no_log GO 2,截断事务日志 复制代码 代码如下: DUMP TRANSACTION DNName WITH no_log GO 3,收缩 两种方式: 1):压缩数据库 DBCC SHRINKDATABASE(库名) GO
-
收缩数据库日志文件的方法(仅适用于mssql2005)
执行以下语句,在执行前将dbname修改为需要瘦身的数据库名称,执行后数据库日志文件将变为1M大小. 复制代码 代码如下: backup log dbName with NO_LOG; backup log dbName with TRUNCATE_ONLY; DBCC SHRINKDATABASE(dbName);
-
mssql自动备份及自动清除日志文件服务器设置
1.每日自动备份 强烈建议有条件的用户进行此操作! 1.打开企业管理器,在控制台根目录中依次点开Microsoft SQL Server--SQL Server组--双击打开你的服务器 2.然后点上面菜单中的工具--选择数据库维护计划器 3.下一步选择要进行自动备份的数据--下一步更新数据优化信息,这里一般不用做选择--下一步检查数据完整性,也一般不选择 4.下一步指定数据库维护计划,默认的是1周备份一次,点击更改选择每天备份后点确定 5.下一步指定备份的磁盘目录,选择指定目录,如您可以在D盘新
-
拯救你的数据 通过日志恢复MSSQL数据
这段时间看了关于在SQL server 中通过日志和时间点来恢复数据.也看了一些网上的例子,看如何通过日志来恢复数据. 前提条件: 数据库的故障恢复改为非简单模式,去掉自动关闭和自动收缩两个选项 如果是简单模式:类似下面的语句操作数据就不会记录到日志中: select * into t from [表名] 这时为保证数据的完整要将数据库的恢复模式改成"完整" 测试环境: 1.建立数据库和测试表 create database zp create table [
-
SQLServer2005 没有日志文件(*.ldf) 只有数据文件(*.mdf) 恢复数据库的方法
复制代码 代码如下: exec sp_attach_db exun,'d:\exun2.mdf' 一句话就可以了. 网上看了那些比较繁琐的,都是sql server 2000版本的. (可能执行一次不能成功,测试了下,有时候需要执行2次以上命令才行) 执行了之后,记得刷新数据库,不然是不会显示的