SQL 语句查询SQL server数据库服务器磁盘容量信息

最新推荐文章于 2024-07-05 14:36:55 发布

SmartSoftHelp开发辅助优化

最新推荐文章于 2024-07-05 14:36:55 发布

阅读量881

点赞数 1

分类专栏：数据库优化文章标签：性能优化数据库 Powered by 金山文档

本文链接：https://blog.csdn.net/qq512929249/article/details/129232368

版权

数据库优化专栏收录该内容

36 篇文章 0 订阅

订阅专栏

SET NOCOUNT ON DECLARE @Result INT; DECLARE @objectInfo INT; DECLARE @DriveInfo CHAR(1); DECLARE @TotalSize VARCHAR(20); DECLARE @OutDrive INT; DECLARE @UnitMB BIGINT; DECLARE @FreeRat FLOAT; SET @UnitMB = 1048576; CREATE TABLE #DiskCapacity ( [DiskCD] CHAR(1) , FreeSize INT , TotalSize INT ); INSERT #DiskCapacity([DiskCD], FreeSize ) EXEC master.dbo.xp_fixeddrives; EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; EXEC @Result = master.sys.sp_OACreate 'Scripting.FileSystemObject',@objectInfo OUT; DECLARE CR_DiskInfo CURSOR LOCAL FAST_FORWARD FOR SELECT DiskCD FROM #DiskCapacity ORDER by DiskCD OPEN CR_DiskInfo; FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo WHILE @@FETCH_STATUS=0 BEGIN EXEC @Result = sp_OAMethod @objectInfo,'GetDrive', @OutDrive OUT, @DriveInfo EXEC @Result = sp_OAGetProperty @OutDrive,'TotalSize', @TotalSize OUT UPDATE #DiskCapacity SET TotalSize=@TotalSize/@UnitMB WHERE DiskCD=@DriveInfo FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo END CLOSE CR_DiskInfo DEALLOCATE CR_DiskInfo; EXEC @Result=sp_OADestroy @objectInfo EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE; SELECT DiskCD AS [Drive CD_驱动器CD] , STR(TotalSize*1.0/1024,6,2) AS [Total Size(GB)_总大小] , STR((TotalSize - FreeSize)*1.0/1024,6,2) AS [Used Space(GB)_已用空间] , STR(FreeSize*1.0/1024,6,2) AS [Free Space(GB)] , STR(( TotalSize - FreeSize)*1.0/(TotalSize)* 100.0,6,2) AS [Used Rate(%)_已使用率] , STR(( FreeSize * 1.0/ ( TotalSize ) ) * 100.0,6,2) AS [Free Rate(%)_未使用自由率] FROM #DiskCapacity; DROP TABLE #DiskCapacity

SQL执行历史记录：

SQL操作返回历史记录：

2023-02-26 21:47:16:287 输入：SET NOCOUNT ON DECLARE @Result INT; DECLARE @objectInfo INT; DECLARE @DriveInfo CHAR(1); DECLARE @TotalSize VARCHAR(20); DECLARE @OutDrive INT; DECLARE @UnitMB BIGINT; DECLARE @FreeRat FLOAT; SET @UnitMB = 1048576; CREATE TABLE #DiskCapacity ( [DiskCD] CHAR(1) , FreeSize INT , TotalSize INT ); INSERT #DiskCapacity([DiskCD], FreeSize ) EXEC master.dbo.xp_fixeddrives; EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; EXEC @Result = master.sys.sp_OACreate 'Scripting.FileSystemObject',@objectInfo OUT; DECLARE CR_DiskInfo CURSOR LOCAL FAST_FORWARD FOR SELECT DiskCD FROM #DiskCapacity ORDER by DiskCD OPEN CR_DiskInfo; FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo WHILE @@FETCH_STATUS=0 BEGIN EXEC @Result = sp_OAMethod @objectInfo,'GetDrive', @OutDrive OUT, @DriveInfo EXEC @Result = sp_OAGetProperty @OutDrive,'TotalSize', @TotalSize OUT UPDATE #DiskCapacity SET TotalSize=@TotalSize/@UnitMB WHERE DiskCD=@DriveInfo FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo END CLOSE CR_DiskInfo DEALLOCATE CR_DiskInfo; EXEC @Result=sp_OADestroy @objectInfo EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE; SELECT DiskCD AS [Drive CD_驱动器CD] , STR(TotalSize*1.0/1024,6,2) AS [Total Size(GB)_总大小] , STR((TotalSize - FreeSize)*1.0/1024,6,2) AS [Used Space(GB)_已用空间] , STR(FreeSize*1.0/1024,6,2) AS [Free Space(GB)] , STR(( TotalSize - FreeSize)*1.0/(TotalSize)* 100.0,6,2) AS [Used Rate(%)_已使用率] , STR(( FreeSize * 1.0/ ( TotalSize ) ) * 100.0,6,2) AS [Free Rate(%)_未使用自由率] FROM #DiskCapacity; DROP TABLE #DiskCapacity

2023-02-26 21:47:16:297 输出：查看数据库服务器磁盘容量信息： 3