查询 磁盘 磁盘上的数据和日志文件空间使用信息
需求背景:
有的时候磁盘空间吃紧,需要及时了解该磁盘的使用情况,以及该磁盘上的数据库数据文件和日志文件的使用情况
可以使用以下脚本获取相关信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/* editor:lynn function: get disk, datafile and logfile used space info 查看当前实例实际使用空间情况 */ USE master GO SET NOCOUNT ON --===================public variables declare @drive sysname select @drive = N'D' --input (null: all disks) ; --begin get disk info ############################## if OBJECT_ID('tempdb.dbo.#Disk','u') is not null drop table #Disk CREATE TABLE #Disk ( driver char(1) ,freespace nvarchar(255) ,totalspace nvarchar(255) ,freeprecent nvarchar(5) ) DECLARE @TotalDisk TABLE ( t nvarchar(100) ) DECLARE @driver nvarchar(5) ,@totalsize nvarchar(50) ; INSERT INTO #Disk ( driver ,freespace ) EXEC master.sys.xp_fixeddrives DECLARE disk_cur CURSOR FOR SELECT driver FROM #Disk OPEN disk_cur FETCH NEXT FROM disk_cur INTO @driver WHILE @@FETCH_STATUS=0 BEGIN INSERT INTO @TotalDisk EXEC('xp_cmdshell ''wmic LogicalDisk WHERE "Caption='''''+@driver+':''''" GET FreeSpace ,SIZE /VALUE''' ) SELECT @totalsize=t FROM @TotalDisk WHERE t LIKE 'Size%' if(@totalsize is not null and LEN(@totalsize)>4) BEGIN SET @totalsize=REPLACE(SUBSTRING(@totalsize,CHARINDEX('=',@totalsize)+1,LEN(@totalsize)-CHARINDEX('=',@totalsize)-1),' ','') SET @totalsize=cast(cast(RTRIM(@totalsize) as bigint)/1024/1024 as nvarchar(50)) UPDATE #Disk SET totalspace=@totalsize ,freeprecent=CAST(CAST(freespace AS bigint)*1.0/CAST(@totalsize AS bigint)*100 AS decimal(5,2)) WHERE driver=@driver END FETCH NEXT FROM disk_cur INTO @driver END close disk_cur deallocate disk_cur SELECT GETDATE() datetime ,driver ,[freespace(GB)] = cast(freespace as decimal(10,2))/1024. ,[totalspace(GB)] = cast(totalspace as decimal(10,2))/1024. ,[freeprecent(%)]= freeprecent FROM #Disk WHERE driver = ISNULL(@drive,driver); if OBJECT_ID('tempdb.dbo.#Disk','u') is not null drop table #Disk --end get disk info ############################## --begin get dbfile info ################# if OBJECT_ID('tempdb.dbo.#dbfileinfo','U') is not null drop table #dbfileinfo CREATE TABLE DBO.#dbfileinfo ( dbname VARCHAR(100), files_name VARCHAR(100), drive CHAR(1), type INT, AllocateSize_MB DECIMAL(10,2), UsedSize_MB DECIMAL(10,2), UnUsedSize_MB DECIMAL(10,2), Used_Percent DECIMAL(10,2), UnUsed_Percent DECIMAL(10,2), physical_name varchar(500) ) declare @sql nvarchar(max), @exec_sql nvarchar(max), @database_name sysname; if @drive is not null begin select @sql=N' INSERT INTO #dbfileinfo(dbname, files_name, drive, type, AllocateSize_MB, UsedSize_MB, UnUsedSize_MB, Used_Percent, UnUsed_Percent,physical_name) SELECT DB_NAME() AS dbname, name AS files_name, LEFT(physical_name,1) AS drive, type , CAST( size/128.0 AS DECIMAL(10,2) )AS AllocateSize_MB, CAST( FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS DECIMAL(10,2) )AS UsedSize_MB, CAST( (size - FILEPROPERTY(name, ''SpaceUsed''))/128.0 AS DECIMAL(10,2) ) AS UnUsedSize_MB, CAST( FILEPROPERTY(name, ''SpaceUsed'')*100.0/size AS DECIMAL(10,2)) AS Used_Percent, CAST((size - FILEPROPERTY(name, ''SpaceUsed''))*100.0/size AS DECIMAL(10,2)) UnUsed_Percent, physical_name FROM sys.database_files WITH(NOLOCK) where left(physical_name,1) = '''+@drive+''' ' end else select @sql=N' INSERT INTO #dbfileinfo(dbname, files_name, drive, type, AllocateSize_MB, UsedSize_MB, UnUsedSize_MB, Used_Percent, UnUsed_Percent,physical_name) SELECT DB_NAME() AS dbname, name AS files_name, LEFT(physical_name,1) AS drive, type , CAST( size/128.0 AS DECIMAL(10,2) )AS AllocateSize_MB, CAST( FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS DECIMAL(10,2) )AS UsedSize_MB, CAST( (size - FILEPROPERTY(name, ''SpaceUsed''))/128.0 AS DECIMAL(10,2) ) AS UnUsedSize_MB, CAST( FILEPROPERTY(name, ''SpaceUsed'')*100.0/size AS DECIMAL(10,2)) AS Used_Percent, CAST((size - FILEPROPERTY(name, ''SpaceUsed''))*100.0/size AS DECIMAL(10,2)) UnUsed_Percent, physical_name FROM sys.database_files WITH(NOLOCK) ' declare cur_database cursor local static forward_only read_only for select name from sys.databases with(nolock) WHERE state<>6 --OFFLINE --==============you can filter the system database or not --where name not in( --'master' --,'tempdb' --,'model' --,'msdb' --,'distribution' --); open cur_database fetch next from cur_database into @database_name while(@@FETCH_STATUS = 0) begin SET @exec_sql = N'USE ' + QUOTENAME(@database_name) + ';' + @sql; exec sys.sp_executesql @exec_sql fetch next from cur_database into @database_name end; close cur_database deallocate cur_database --select * from #dbfileinfo SELECT N'包含日志文件' '描述', drive, SUM(AllocateSize_MB) as 'AllocateSize_MB数据库文件分配空间', SUM(UsedSize_MB) as 'UsedSize_MB数据库文件使用空间', SUM(UnUsedSize_MB) as 'UnUsedSize_MB数据库文件可用空间' FROM #dbfileinfo group by drive --##start datafile############################# ;with datassum as ( select drive,dbname, SUM(AllocateSize_MB) Data_AllocateSize_MB, SUM(UsedSize_MB) Data_UsedSize_MB, SUM(UnUsedSize_MB) Data_UnUsedSize_MB from #dbfileinfo where physical_name not like '%.ldf' group by drive,dbname ) select 'datafile info'= 'Sum_Data_AllocateSize_MB: '+CAST(SUM(Data_AllocateSize_MB) AS VARCHAR(100)) +' Sum_Data_UsedSize_MB: '+CAST(SUM(Data_UsedSize_MB) AS VARCHAR(100)) +' Sum_Data_UnUsedSize_MB: ' +CAST(SUM(Data_UnUsedSize_MB) AS VARCHAR(100)) from datassum ;with datas as ( select drive,dbname, SUM(AllocateSize_MB) Data_AllocateSize_MB, SUM(UsedSize_MB) Data_UsedSize_MB, SUM(UnUsedSize_MB) Data_UnUsedSize_MB from #dbfileinfo where physical_name not like '%.ldf' group by drive,dbname ) select * from datas order by Data_AllocateSize_MB desc,Data_UnUsedSize_MB desc --##end datafile############################# --##start logfile############################# ;with logsSum as( select drive,dbname, SUM(AllocateSize_MB) logs_AllocateSize_MB, SUM(UsedSize_MB) logs_UsedSize_MB, SUM(UnUsedSize_MB) logs_UnUsedSize_MB from #dbfileinfo where physical_name like '%.ldf' group by drive,dbname )select 'logfile info'= 'Sum_logs_AllocateSize_MB: '+CAST(SUM(logs_AllocateSize_MB) AS VARCHAR(100)) +' Sum_logs_UsedSize_MB: '+ CAST(SUM(logs_UsedSize_MB) AS VARCHAR(100)) +' Sum_logs_UnUsedSize_MB: '+CAST(SUM(logs_UnUsedSize_MB) AS VARCHAR(100)) from logsSum ;with logs as( select drive,dbname, SUM(AllocateSize_MB) logs_AllocateSize_MB, SUM(UsedSize_MB) logs_UsedSize_MB, SUM(UnUsedSize_MB) logs_UnUsedSize_MB from #dbfileinfo where physical_name like '%.ldf' group by drive,dbname )select * --,SUM(logs_AllocateSize_MB),SUM(logs_UsedSize_MB),SUM(logs_UnUsedSize_MB) from logs order by logs_UnUsedSize_MB desc --##end logfile############################# --end begin get dbfile info ################# if OBJECT_ID('tempdb.dbo.#dbfileinfo','U') is not null drop table #dbfileinfo
结果如下图: