SQL SERVER 监控数据文件增长情况

   在项目前期评估数据库的增长情况，然后根据数据库数据量的增长情况来规划存储的分配其实是一件比较麻烦的事情。因为项目没有上线，用什么来评估数据库的数
据增长情况呢？
如果手头没有实际的数据，我们只能从表的数量以及预计一天的数据增长情况来预估数据增长量。当然这里猜测的成分较大。这个是非常不靠谱，也是不准确的。当
然我们可以监控测试环境的数据库大小的增长情况来评估数据增长情况。我们可以监控数据库大小的变化来估计生产环境的数据增长情况。当然生产环境和测试环境
的区别还是蛮大的。但是这样比那种瞎猜式的还是要靠谱得多。

   

在项目中期，我们在管理、维护数据库当中，也是需要监控数据库的增长情况的。这样有利于我们了解系统的数据变化情况，利于长期的存储规划，也能提前发现一
些异常情况，及时调整数据库数据文件的增长设置。总之来说，监控数据文件的增长情况是有必要的。数据库管理、维护也是需要大数据和BI分析的吗。这个也是
一个趋势。

   为了监控数据库的数据文件增长情况，我写了一个存储过程用来获取数据库数据文件的一些详细信息。然后可以按天、按周、按月份这三种频率采集数据（具体可以根据需要来采集数据）存放在日表、周表、月表。需要时，即可拿来做一下分析。

基础表Maint.DataBaseSizeDtl_Day，Maint.DataBaseSizeDtl_Week，Maint.DataBaseSizeDtl_Month

USE YourSQLDba;
GO
 
IF EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND object_id=OBJECT_ID('Maint.DataBaseSizeDtl_Day'))
BEGIN
    DROP TABLE Maint.DataBaseSizeDtl_Day;
END
GO
 
 
CREATE TABLE Maint.DataBaseSizeDtl_Day
(
     DateCD                DATETIME
    ,DataBaseId            INT 
    ,FileId                INT
    ,DataBaseName        NVARCHAR(256)
    ,LogicalName        NVARCHAR(256)
    ,FileTypeDesc        NVARCHAR(120)
    ,PhysicalName        NVARCHAR(520)
    ,StateDesc            NVARCHAR(120)
    ,MaxSize            NVARCHAR(32)
    ,IsPercentGrwoth    BIT
    ,Growth                NVARCHAR(24)
    ,IsReadOnly            BIT
    ,DataBaseSize        FLOAT
    CONSTRAINT PK_DataBaseSizeDtl_Day PRIMARY KEY(DateCD, DataBaseId,FileId)
);
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database Size Detail Records every day', @level0type=N'SCHEMA', @level0name=N'Maint', @level1type=N'TABLE', @level1name=N'DataBaseSizeDtl_Day';
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采集数据库记录的时间',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'DateCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'DataBaseId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase file''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'FileId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'DataBaseName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库逻辑名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'LogicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件类型',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'FileTypeDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库物理文件',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'PhysicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库状态',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'StateDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件最大值',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'MaxSize';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否按百分比增长', 
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'IsPercentGrwoth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否自动增长',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'Growth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库是否只读',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'IsReadOnly';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件大小',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'DataBaseSize';
 
 
 
IF EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND object_id=OBJECT_ID('Maint.DataBaseSizeDtl_Week'))
BEGIN
    DROP TABLE Maint.DataBaseSizeDtl_Week;
END
GO
 
CREATE TABLE Maint.DataBaseSizeDtl_Week
(
     DateCD                DATETIME
    ,WeekCD                INT
    ,DataBaseId            INT 
    ,FileId                INT
    ,DataBaseName        NVARCHAR(256)
    ,LogicalName        NVARCHAR(256)
    ,FileTypeDesc        NVARCHAR(120)
    ,PhysicalName        NVARCHAR(520)
    ,StateDesc            NVARCHAR(120)
    ,MaxSize            NVARCHAR(32)
    ,IsPercentGrwoth    BIT
    ,Growth                NVARCHAR(24)
    ,IsReadOnly            BIT
    ,DataBaseSize        FLOAT
    CONSTRAINT PK_DataBaseSizeDtl_Week PRIMARY KEY(WeekCD,DateCD, DataBaseId,FileId)
);
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database Size Detail Records every week(Sunday)', @level0type=N'SCHEMA', @level0name=N'Maint', @level1type=N'TABLE', @level1name=N'DataBaseSizeDtl_Week';
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采集数据库记录的时间',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'DateCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第几周',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'WeekCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'DataBaseId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase file''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'FileId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'DataBaseName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库逻辑名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'LogicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件类型',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'FileTypeDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库物理文件',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'PhysicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库状态',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'StateDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件最大值',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'MaxSize';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否按百分比增长', 
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'IsPercentGrwoth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否自动增长',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'Growth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库是否只读',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'IsReadOnly';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件大小',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'DataBaseSize';
 
 
 
IF EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND object_id=OBJECT_ID('Maint.DataBaseSizeDtl_Month'))
BEGIN
    DROP TABLE Maint.DataBaseSizeDtl_Month;
END
GO
 
CREATE TABLE Maint.DataBaseSizeDtl_Month
(
     DateCD                DATETIME
    ,MonthCD            INT
    ,DataBaseId            INT 
    ,FileId                INT
    ,DataBaseName        NVARCHAR(256)
    ,LogicalName        NVARCHAR(256)
    ,FileTypeDesc        NVARCHAR(120)
    ,PhysicalName        NVARCHAR(520)
    ,StateDesc            NVARCHAR(120)
    ,MaxSize            NVARCHAR(32)
    ,IsPercentGrwoth    BIT
    ,Growth                NVARCHAR(24)
    ,IsReadOnly            BIT
    ,DataBaseSize        FLOAT
    CONSTRAINT PK_DataBaseSizeDtl_Month PRIMARY KEY(MonthCD,DateCD, DataBaseId,FileId)
);
 
 
 
USE YourSQLDba;
GO
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database Size Detail Records every month(the first day)', @level0type=N'SCHEMA', @level0name=N'Maint', @level1type=N'TABLE', @level1name=N'DataBaseSizeDtl_Month';
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采集数据库记录的时间',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'DateCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采集数据的月份',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'MonthCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'DataBaseId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase file''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'FileId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'DataBaseName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库逻辑名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'LogicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件类型',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'FileTypeDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库物理文件',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'PhysicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库状态',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'StateDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件最大值',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'MaxSize';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否按百分比增长', 
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'IsPercentGrwoth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否自动增长',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'Growth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库是否只读',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'IsReadOnly';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件大小',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'DataBaseSize';

 

存储过程[Maint].[Usp_Monitor_Database_Size]



 


作业YourSQLDba_Monitor_Database_Daily_Growth




作业YourSQLDba_Monitor_Database_Week_Growth




作业YourSQLDba_Monitor_Database_Month_Grwoth


				


				
时间： 2024-03-17 11:25:52

		
		


		


	

	
	

		


		
SQL SERVER 监控数据文件增长情况的相关文章


								

		

			

                SQL Server监控系列之调优排错
			

		

		

									

				使用SQL Server Profile GUI工具还是很多优势,首先是减少了我们监控的复杂性,可以款速的建立监控,在跟踪属性中,可以可以选择MSSQL为我们提供的模版,包括常用的T-SQL.T-SQL Duration.T-SQL Locks模版分别监控当前DB运行的所有查询,所有查询的耗时.所有的锁定状态. 在跟踪属性 –> 选择事件选择 我们可以选择自己需要的事件,所有的事件在MSDN 都有定义->单击列筛选器 可以自定义过滤,排序噪点干扰因素 (我随便选择了一个耗时 = 500 微妙的			

		

	

				

		

			

                利用typeperf工具收集SQL Server性能数据
			

		

		

									

				通常DBA在监控和优化SQL Server数据库DBA,都想利用命令行工具.其中经常要做的一件事情,就是收集服务器性能数据,包括CPU ,内存和磁盘利用率以及SQL Server特定数据.这时你就可以利用TypePerf.exe这个命令行工具来帮你捕获这些性能数据. TypePerf.exe是一个命令行工具,包括把Windows操作系统的性能计数器数据输出到命令窗口或写入到支持该功能的日志文件格式中. Windows操作系统以对象及其相关联的计数器的形式,提供了非常多的性能数据.例如, SQL			

		

	

				

		

			

                SQL Server 2005 数据维护实务
			

		

		

									

				1.关于SQL Server 2005数据维护计划 为了使SQL Server数据库的性能保持在最佳的状态,数据库管理员应该对每一个数据库进行定期的常规维护.这些常规任务包括重建数据库索引.检查数据库完整性,更新索引统计信息,数据库内部一致性检查和备份等.这些常规的数据库维护任务需要经常重复,而且繁琐耗时,所以往往被管理员忽略.而且,现在的数据库管理员一天到晚都被很多其他的任务压得喘不过气来,根本没有时间去进行日常维护工作.认识到这些问题的存在,SQL Server通过制定维护计划,提供了一个可			

		

	

				

		

			

                SQL Server中数据库文件的存放方式
			

		

		

									

				SQL Server中数据库文件的存放方式 在SQL SERVER中,通过文件组这个逻辑对象对存放数据的文件进行管理. 先来看一张图: 我们看到的逻辑数据库由一个或者多个文件组构成 而文件组管理着磁盘上的文件.而文件中存放着SQL SERVER的实际数据. 为什么通过文件组来管理文件 对于用户角度来说,需对创建的对象指定存储的文件组只有三种数据对象:表,索引和大对象(LOB) 使用文件组可以隔离用户和文件,使得用户针对文件组来建立表和索引,而不是实际磁盘中的文件.当文件移动或修改时,由于用户建立			

		

	

				

		

			

                为什么你SQL Server的数据库文件的Date modified没有变化呢？
			

		

		

									

				在SQL Server数据库中,数据文件与事务日志文件的修改日期(Date Modified)是会变化的,但是有时候你会发现你的数据文件或日志文件的修改日期(Date Modified)几个月甚至是半年以上都没有变化了,如下截图所示:     为什么呢?不会是什么bug吧? 相信很多人都会有这样的反应.下面我们通过实验来看看数据库的数据文件与事务日志文件在什么情况或条件下, 修改日期(Date Modified)才会变化.首先创建一个TEST数据库,查看其数据文件或事务日志文件的修改日期如下:			

		

	

				

		

			

                《SQL Server企业级平台管理实践》读书笔记——SQL Server如何设置自动增长和自动收缩项
			

		

		

									

				原文:<SQL Server企业级平台管理实践>读书笔记--SQL Server如何设置自动增长和自动收缩项 SQL Server允许用户设置数据库初始值和最大值,可以通过自动增长或者自动收缩进行配置.通过这些配置,我们可以防止数据库空间问题而导致的应用程序修改失败或者SQL Server磁盘空间耗尽的事情发生.一般来讲,如果数据库不是很忙,默认的设置为自动增长,这种方式能够满足大部分的需求.但是在大量并发的情况下,申请数据文件和日志文件增长本身是一件非常消耗系统资源和影响性能的工作.所以如果			

		

	

				

		

			

                0. SQL Server监控清单
			

		

		

									

				原文:0. SQL Server监控清单 数据库服务器的监控可大致分为两类: (1) 状态监控:数据库服务器有没有在健康地运行? (2) 性能监控:健康运行的同时,有没有性能问题?可不可以更快些?   一. 服务器 1. 状态监控 (1) 服务器是否可访问? (2) 数据库服务是否启用? (3) 操作系统事件日志中的错误或告警 (4) 磁盘可用空间   2. 性能监控 (1) IO压力 (2) 内存使用 (3) CPU使用 (4) 网络带宽占用 这1,2,3,4是按照容易出现瓶颈的顺序排列的,由			

		

	

				

		

			

                在SQL Server中将数据导出为XML和Json的方法
			

		

		

									

				 有时候需要一次性将SQL Server中的数据导出给其他部门的也许进行关联或分析,这种需求对于SSIS来说当然是非常简单,但很多时候仅仅需要一次性导出这些数据而建立一个SSIS包就显得小题大做,而SQL Server的导入导出工具其中BUG还是蛮多的,最简单的办法是BCP.   数据导出为XML     在SQL Server 2005之后提供了一个for xml子句在关系数据库中原生支持XML.通过该命令可以将二维关系结果集转换为XML,通过BCP就可以将数据存为XML了.     例如下面			

		

	

				

		

			

                SQL Server 2005和SQL Server 2000数据的相互导入
			

		

		

									

				  1) SQL Server 2000数据导入SQL Server 2005 在SQL Server 2000中,把其数据进行备份,在数据库中点击右键,选择"所有任务"下的"备份数据库",弹出一个对话框,然后点击"添加"按钮,输入对应的数据库备份名称,就可以备份数据了.然后在SQL Server 2005中,在数据库中点击右键,点击"还原数据库",在弹出的对话框中,"常规"的选项卡中,"目标数据