Monitor the data/log file space usage in SQL SERVER-优快云博客

本文链接：https://blog.youkuaiyun.com/envykok/article/details/6080419

本文提供两种SQL脚本，一种用于查询指定数据库的空间使用情况，包括文件大小、已用空间及空闲空间等；另一种则针对所有数据库进行空间使用的汇总查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Script 1 :

USE [DBNAME]
GO

sp_spaceused

Script 2 :

USE [DBNAME] -- FOR SINGLE DATABASE
GO

SELECT    DB_NAME() as DatabaseName,
        CAST(sysfiles.size/128.0 AS int) AS FileSizeMB,
        sysfiles.name AS LogicalFileName,
        CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name,
       'SpaceUsed') AS int)/128.0 AS int) AS FreeSpaceMB,
        CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, 'SpaceUsed' ) AS int)/128.0)/(sysfiles.size/128.0))
        AS decimal(4,2))) AS varchar(8)) + '%' AS FreeSpacePct,
        sysfiles.filename AS PhysicalFileName,
        CONVERT(sysname,DatabasePropertyEx('?','Status')) AS Status,
        CONVERT(sysname,DatabasePropertyEx('?','Updateability')) AS Updateability,
        CONVERT(sysname,DatabasePropertyEx('?','Recovery')) AS RecoveryMode,
        GETDATE() as PollDate
FROM dbo.sysfiles

USE MASTER -- FOR ALL DATABASE

select db_name(dbid) as dbname,str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size from sys.sysaltfiles
group by dbid order by Size desc
GO

Sample Result:

For More Information:

http://www.mssqltips.com/tip.asp?tip=1426

DECLARE @DBInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100), FileSizeMB INT, LogicalFileName sysname, PhysicalFileName NVARCHAR(520), Status sysname, Updateability sysname, RecoveryMode sysname, FreeSpaceMB INT, FreeSpacePct VARCHAR(7), FreeSpacePages INT, PollDate datetime) DECLARE @command VARCHAR(5000) SELECT @command = 'Use [' + '?' + '] SELECT @@servername as ServerName, ' + '''' + '?' + '''' + ' AS DatabaseName, CAST(sysfiles.size/128.0 AS int) AS FileSize, sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct, GETDATE() as PollDate FROM dbo.sysfiles' INSERT INTO @DBInfo (ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate) EXEC sp_MSForEachDB @command SELECT ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate FROM @DBInfo ORDER BY ServerName, DatabaseName