SQL Server 2005中获取数据库文件大小信息

核心提示：捕捉SQL Server 2005数据库大小的趋势，这是非常重要的，SQL Server 2005中获取数据库文件大小信息，因为DBA们可以利用这些信息来规划未来的空间需求，问题的通知类型，为了确保正确捕获到所有的数据，我们来看看DatabaseFiles数据表中的内容，并规划繁重负载的时间段，本文将介绍一种捕捉这一信

捕捉SQL Server 2005数据库大小的趋势，这是非常重要的，因为DBA们可以利用这些信息来规划未来的空间需求，问题的通知类型，并规划繁重负载的时间段。本文将介绍一种捕捉这一信息的简单的方法。

SQL Server上的每个数据库都包含了关于数据库文件的大小的信息，连同其他一些相关信息。为了得到这个信息，我们需要一个方法来从单个数库中一次性检索出相关的数据。

有两个方法可以实现这个功能：

sp_spaceused：这个系统存储过程将返回当前数据库中的表、索引视图或 SQL Server 2005 Service Broker 队列所使用的磁盘空间。这是存储过程的返回数据库或者数据库中表的大小，但是报告的输出内容不太友好，不好理解。该存储过程通过脚本来捕获每个数据库的信息，但它可能需要用到用户自定义的光标。

sp_msforeachdb：这是一个非常有用的系统存储过程，将任何SQL脚本传递给你在每个数据库上的SQL Server实例上来执行。存储过程遍历每个数据库，这样就省却了您一项项指定数据库名称了。本文我们将利用这个存储过程来捕获各个数据库文件大小的信息。

这些资料我想收集和储存可以在sys.database_files系统视图中获得，包括数据库文件的大小，以及其他一些相关的信息，如数据库状态，数据库文件的增长方式(按绝对大小递增还是按百分比递增)，是否是只读的等等。我需要捕获每个数据库中的这些信息。

下面的脚本以系统视图sys.database_files的结构为基础，创建一个名为DatabaseFiles表(如果它不存在的话)，还增加了一个新的字段，用来记录捕获的记录什么时候插入到数据表中。

IFOBJECT_ID('DatabaseFiles')ISNULL
BEGIN
SELECTTOP0*INTODatabaseFiles
FROMsys.database_files
ALTERTABLEDatabaseFiles
ADDCreationDateDATETIMEDEFAULT(GETDATE())
END

现在需要填充DatabaseFiles数据表。下面这个脚本使用sp_msforeachdb存储过程，并通过一个SQL脚本从sys.database_files读取数据，并插入到DatabaseFiles数据表中。

EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, GETDATE() FROM [?].sys.database_files'

从脚本中我们可以发现，每个数据库的数据库名称，有点特别：使用[ ?]作为视图sys.database_files的前缀。

此代码执行时，实际上是遍历每个数据库实例，将数据库名称作为参数代替[ ?]标记。每个数据库的信息，将插入到DatabaseFiles表。这比自己写一个自定义游标实现起来容易很多。我还增加了一个GETDATE( )函数，用来记录记录插入到表中的时间。

注意：这个例子有点违背编码标准，使用SELECT *插入到一个表时，没有明确写明字段名称不列清单。如果用于实际项目中，建议大家作下修改，注明字段名称。

为了确保正确捕获到所有的数据，我们来看看DatabaseFiles数据表中的内容。

SELECT * FROM DatabaseFiles

执行上面的命令后，可以得到类似以下的信息：

图1：查看捕获到的数据库大小的数据