确定能够释放空间的SQL Server数据库文件的脚本

问题

作为一个负责几个服务器的数据库管理员，我接到许多电话是关于磁盘空间的。我所做的第一件事是找到可以缩小的数据文件来释放一些磁盘上的空间。我所负责的一些服务器上具有几个数据库，要指出哪个数据文件能够立即释放空间需要花费些时间，专家能否介绍一种捷径解决此问题?

专家解答

你需要的第一个东西是驱动盘信息。你可以从扩展的存储过程xp_fixeddrives获得这个信息，它返回驱动盘字母和这个驱动盘上有多少空闲空间。接下来你需要知道在每个数据文件中有多少空间是可用的。当你在SSMS中使用这个信息的时候，Microsoft使用两个不同的DBCC调用;一个用于数据文件而另一个用于日志文件。要替代使用单独的DBCC调用，你可以调用fileproperty函数并传递数据文件的名称和SpaceUsed参数。这是从数据文件所在数据库的上下文环境中运行的，所以你可以使用sp_msforeachdb存储过程来返回所有数据库的结果。

既然你已经有了驱动盘信息和所用的空间，那么你就可以将它连接到sys.databases和sys.master_files表来返回对所有数据的视图。现在它可以快速地确定具有最多空闲空间的文件和它们的所在位置。

USEMASTER
　　GO
　　CREATETABLE#TMPFIXEDDRIVES(
　　DRIVECHAR(1),
　　MBFREEINT)
　　INSERTINTO#TMPFIXEDDRIVES
　　EXECxp_FIXEDDRIVES
　　CREATETABLE#TMPSPACEUSED(
　　DBNAMEVARCHAR(50),
　　FILENMEVARCHAR(50),
　　SPACEUSEDFLOAT)
　　INSERTINTO#TMPSPACEUSED
　　EXEC('sp_msforeachdb''use?;Select''''?''''DBName,NameFileNme,fileproperty(Name,''''SpaceUsed'''')SpaceUsedfromsysfiles''')
　　SELECTC.DRIVE,
　　CASE
　　WHEN(C.MBFREE)>1000THENCAST(CAST(((C.MBFREE)/1024.0)ASDECIMAL(18,2))ASVARCHAR(20))+'GB'
　　ELSECAST(CAST((C.MBFREE)ASDECIMAL(18,2))ASVARCHAR(20))+'MB'
　　ENDASDISKSPACEFREE,
　　A.NAMEASDATABASENAME,
　　B.NAMEASFILENAME,
　　CASEB.TYPE
　　WHEN0THEN'DATA'
　　ELSETYPE_DESC
　　ENDASFILETYPE,
　　CASE
　　WHEN(B.SIZE*8/1024.0)>1000THENCAST(CAST(((B.SIZE*8/1024)/1024.0)ASDECIMAL(18,2))ASVARCHAR(20))+'GB'
　　ELSECAST(CAST((B.SIZE*8/1024.0)ASDECIMAL(18,2))ASVARCHAR(20))+'MB'
　　ENDASFILESIZE,
　　CAST((B.SIZE*8/1024.0)-(D.SPACEUSED/128.0)ASDECIMAL(15,2))SPACEFREE,
　　B.PHYSICAL_NAME
　　FROMSYS.DATABASESA
　　JOINSYS.MASTER_FILESB
　　ONA.DATABASE_ID=B.DATABASE_ID
　　JOIN#TMPFIXEDDRIVESC
　　ONLEFT(B.PHYSICAL_NAME,1)=C.DRIVE
　　JOIN#TMPSPACEUSEDD
　　ONA.NAME=D.DBNAME
　　ANDB.NAME=D.FILENME
　　ORDERBYDISKSPACEFREE,
　　SPACEFREEDESC
　　DROPTABLE#TMPFIXEDDRIVES
　　DROPTABLE#TMPSPACEUSED

在将上面的内容拷贝和黏贴到一个SQL Server 2005查询窗口中并运行所有的代码之后，得到的结果集将如下所示：