数据库空间能撑多久? 容量规划

绝大多数数据库都在增长，而不是减小。作为DBA必须准备好空间耗尽的对应策略。

那么我们都需要监控哪些信息,用以支持我们对应空间的使用状况评估呢?

• 单个文件大小
• 文件中可使用空间量
• 文件的增长方式(百分比或固定增量)
• 磁盘可用空间
• 每个文件组的增速

如果你有监控工具,上述这些指标通常都在监控工具中.如果没有,下面的代码可以提供一些帮助.

 

先建立一张存储记录的表

CREATE TABLE [dbo].[DBGrowthHistory] 
    ( 
      [TimeCollected] [DATETIME] NOT NULL , 
      [Monitored_object] [NVARCHAR](100) NOT NULL , 
      [DBName] [NVARCHAR](100) NOT NULL , 
      [FGName] [NVARCHAR](100) NOT NULL , 
      [FileLogicalName] [NVARCHAR](100) NOT NULL , 
      [totalMB] [FLOAT] NOT NULL , 
      [freeMB] [FLOAT] NOT NULL , 
      [growthsremaining] [INT] NOT NULL , 
      [DBGrowthHistory_ID] [BIGINT] IDENTITY(1, 1) 
                                    NOT NULL , 
      CONSTRAINT [PK_DBGrowthHistory] PRIMARY KEY CLUSTERED 
        ( [DBGrowthHistory_ID] ASC ) 
       
    ) 
ON  [PRIMARY]; 

 

用下面的语句,定时将采集信息(请在要监控的数据库中运行,如何从每个数据库运行,请参考sp_msforeachdb)加入到表中

 

SELECT  CONVERT(DATETIME, CONVERT(DATE, GETDATE())) AS [TimeCollected] , 
        @@Servername AS [monitored_object] , 
        DB_NAME() AS [DBName] , 
        b.groupname AS [fgName] , 
        name AS [FileLogicalName] , 
        [filename] AS [OSFileName] , 
        CONVERT (DECIMAL(15, 2), ROUND(a.size / 128.000, 2)) AS [totalMB] , 
        CONVERT (DECIMAL(15, 2), ROUND(( a.size - FILEPROPERTY(a.name, 
                                                              'SpaceUsed') ) 
                                       / 128.000, 2)) AS [freeMB] 
FROM    dbo.sysfiles a ( NOLOCK ) 
        JOIN sysfilegroups b ( NOLOCK ) ON a.groupid = b.groupid 
ORDER BY b.groupname;

用上面的代码可以获取,当前数据库的情况.接下来还需要查找可用的增长量信息. 

sp_configure 'xp_cmdshell', 1 
GO
RECONFIGURE
GO
DECLARE @svrName VARCHAR(255)
DECLARE @sql VARCHAR(400)
--获取服务器名称
SET @svrName = @@SERVERNAME
SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName '
    + QUOTENAME(@svrName, '''')
    + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--创建临时表
CREATE TABLE #output ( line VARCHAR(255) )
--插入信息
INSERT  #output
        EXEC xp_cmdshell @sql
--截取PowerShell提供的信息
SELECT  @svrName AS [ServerName] ,
        CONVERT(DATETIME, CONVERT(DATE, GETDATE())) AS TimeCollected ,
        RTRIM(LTRIM(SUBSTRING(line, 1, CHARINDEX('|', line) - 1))) AS drivename ,
        ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('|', line) + 1,
                                         ( CHARINDEX('%', line) - 1 )
                                         - CHARINDEX('|', line)))) AS FLOAT),
              0) AS 'capacity(MB)' ,
        ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('%', line) + 1,
                                         ( CHARINDEX('*', line) - 1 )
                                         - CHARINDEX('%', line)))) AS FLOAT),
              0) AS 'freespace(MB)'
FROM    #output
WHERE   line LIKE '[A-Z][:]%'
ORDER BY drivename

--drop table #output
--go
--sp_configure 'xp_cmdshell',0
--go
--reconfigure
--go

用此代码将获取,服务器的名称, 创建日期, 驱动器名,以及总的大小(MB),剩余的可用空间大小(MB).

这个地方有一个小hint在里面, 还记得  xp_fixeddrives吗? 用这个存储过程不是也能获取磁盘空间信息吗?

注意:

 xp_fixeddrives  不能获取挂载点( mount point ) 的信息; powershell却可以.

最后就是计算了:

剩余天数 = 文件中的可用空间 + (增长量 * 增长数) / 每日平均增长量

所有文件的计算出来的最小剩余天数,就是我们要监控的值了

PS:索引重建，归档/清除或底层应用程序中的非正常活动将会产生异常的文件增长