请问如何获取SQLServer实例所占的硬盘空间大小?
要能用T-SQL或提供其他的程序获取方式.
------解决方案--------------------use master
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE usp_diskspace
@Percentagefree int,
@error2 varchar(8000) OUTPUT
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint
SET @MB = 1048576
DECLARE @COUNT int
DECLARE @Maxcount int
DECLARE @error varchar(700)
DECLARE @errordrive char(1)
DECLARE @errortotalspace varchar(20)
DECLARE @errorfreespace varchar(20)
DECLARE @free int
DECLARE @date varchar(100)
declare @query varchar(1300)
set @date = convert(varchar(100), getdate(),109)
set @error2= ' '
select @query= 'master.dbo.xp_fixeddrives '
CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject ',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso, 'GetDrive ', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive, 'TotalSize ', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB,
ServerName = replace( @query ,
'master.dbo.xp_fixeddrives ', ' '),
FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
set @maxcount =(select max(id) from #drives)
set @count=1
while @count <=@maxcount
begin
select @errortotalspace =
convert(varchar(20),
Totalsize),
@errorfreespace =freespace,
@free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),
@errordrive=Drive from #drives where id = @count
if @free <@percentagefree
begin
set @error = 'Server =
'+@@servername+ ':
Drive= ' + @errordrive+ ':
Percentage free= ' +convert(varchar(2),
@free)+ '% TotalSpace = '+ @errortotalspace + 'MB :
FreeSpace = '+ @errorfreespace + 'MB :Date = ' +@date
set @error2=@error2+@error+char(13)
end
else
begin
set @error = 'Server =
'+@@servername+ ':
Drive= ' + @errordrive+ ':
Percentage free= ' +convert(varchar(2),
@free)+ '% TotalSpace = '+ @errortotalspace + 'MB :
FreeSpace = '+ @errorfreespace + 'MB :Date = ' +@date
end
set @count=@count+1
end
DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO