SQLSERVER如何查看各磁盤可用空間

這篇文章將為大家詳細講解有關SQLSERVER如何查看各磁盤可用空間，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

保存數據的表


create table dc_DiskDetaill 
(
 Drive varchar(10),
 Size  varchar(50),
 FreeSize varchar(50),
 GetTime  datetime default(getdate())
)


采集的存儲過程


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <DBA>
-- Create date: <2012-11-16>
-- Description: <get_disk_size>
-- =============================================
CREATE PROCEDURE [dbo].[Usp_Get_DiskSize]
as
begin
 create table  #freedisk (drive varchar(50),disksize varchar(100))
 insert into #freedisk
 exec master.dbo.xp_fixeddrives  
 
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE




  create table #a (DiskName varchar(50))
  insert into #a 
  exec xp_cmdshell  'wmic LOGICALDISK get name'
  select IDENTITY(int,1,1) as id ,DiskName into #a1 from #a 
  drop table #a 
  
  create table #b (freespace varchar(50))
  insert into #b
  exec xp_cmdshell 'wmic LOGICALDISK get freespace' 
  select IDENTITY(int,1,1) as id ,freespace into #b1 from #b 
  drop table #b 
  
  create table #c(size varchar(50))
  insert into #c 
  exec xp_cmdshell 'wmic LOGICALDISK get size'
  select IDENTITY(int,1,1)as id,size into #c1 from #c  
  drop table #c 
  
  delete from #a1 where id=1
  delete from #b1 where id=1
  delete from #c1 where id=1 
  
  update #a1 set DiskName=REPLACE(DiskName,':','')
  update #a1 set DiskName=REPLACE(DiskName,' ','')
  
  select id,LEFT(diskname,1) as diskname into #a2 from #a1 
  drop table #a1 
  
  select  a.diskname,b.freespace,c.size,getdate() as gettime into #all 
  from #a2 as a ,#b1 as b ,#c1 as c where a.id=b.id and a.id=c.id 
  and a.diskname in (select drive  from #freedisk)
  drop table #a2
  drop table #b1
  drop table #c1
  
  update #all set freespace=REPLACE(freespace,' ',''),size=REPLACE(size,' ','')
  select diskname,left(freespace,LEN(freespace)-1) as freespace, left(size,LEN(size)-1) as size,gettime into #all2  from #all
  drop table #all 
  
  insert into dc_DiskDetaill(Drive,Size,FreeSize,GetTime)
  select diskname,cast(size as bigint)/1024/1024 as size,cast(freespace as bigint)/1024/1024 as freesize,gettime from #all2
  drop table #all2 
 EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE


 drop table #freedisk
end

調用存儲過程并查看是否有數據
exec [Usp_Get_DiskSize]


select * from  dc_DiskDetaill

關于“SQLSERVER如何查看各磁盤可用空間”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。