Get SQL Server free disk information without admin privileges
Following script will get the free disk space in database server. To run this script following permissions are required. "NO sysadmin" privileges are required.
Permissions Required
GRANT execute on sp_OAGetErrorInfo to UuerUser
GRANT execute on sp_OAGetProperty to UuerUser
GRANT execute on sp_OACreate to UuerUser
GRANT execute on sp_OADestroy to UuerUser
Script
declare @RunLocal bit = 1;
DECLARE @DiskSpaceStats TABLE
(
DriveLetter char(1),
TotalSize decimal(20,0),
AvailableBytes decimal(20,0),
DriveType char(10),
PercentFree decimal(18,3),
Report_Date datetime default getdate()
)
DECLARE @DiskSpace TABLE
(
RowID int Identity Primary Key,
DriveLetter char(1),
TotalSize decimal(20,0),
AvailableBytes decimal(20,0),
DriveType char(10),
PercentFree decimal(18,3)
)
DECLARE @counter int
DECLARE @FSOobject int
DECLARE @RecordCount int
DECLARE @count int
DECLARE @string char(35)
DECLARE @DriveLetter char(1)
DECLARE @TotalSize char(50)
DECLARE @AvailableSpace char(50)
DECLARE @DriveType char(10)
DECLARE @error int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @ServerName sysname
SET @ServerName = @@ServerName
SET @count=0
SET @counter=67
SET @error=0
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSOobject OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
PRINT @desc
RETURN
END
WHILE @counter < 91 OR @error <3 p="p"> BEGIN
SET @String='Drives.item("'+char(@counter)+'").DriveLetter'
EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveLetter OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
SELECT @counter=@counter+1
SELECT @error=@error +1
GOTO ErrorPoint
END
SET @String='Drives.item("'+char(@counter)+'").TotalSize'
EXEC @HR = sp_OAGetProperty @FSOobject, @string, @TotalSize OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
SELECT @counter=@counter+100
SELECT @error=1
END
SET @String='Drives.item("'+char(@counter)+'").AvailableSpace'
EXEC @HR = sp_OAGetProperty @FSOobject, @string, @AvailableSpace OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
PRINT @desc
RETURN
END
SET @String='Drives.item("'+char(@counter)+'").DriveType'
EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveType OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
PRINT @desc
RETURN
END
IF @Error < 3
BEGIN
INSERT INTO @DiskSpace (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
VALUES (@DriveLetter, @TotalSize, @AvailableSpace, @DriveType, (convert(decimal(20,0), @AvailableSpace)/convert(decimal(20,0), @TotalSize)))
END
SELECT @counter=@counter+1
END
ErrorPoint:
INSERT INTO @DiskSpaceStats (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
SELECT DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree
FROM @DiskSpace
EXEC @hr = sp_OADestroy @FSOobject
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject
RETURN
END
SELECT * FROM @DiskSpace
3>
Permissions Required
GRANT execute on sp_OAGetErrorInfo to UuerUser
GRANT execute on sp_OAGetProperty to UuerUser
GRANT execute on sp_OACreate to UuerUser
GRANT execute on sp_OADestroy to UuerUser
Script
declare @RunLocal bit = 1;
DECLARE @DiskSpaceStats TABLE
(
DriveLetter char(1),
TotalSize decimal(20,0),
AvailableBytes decimal(20,0),
DriveType char(10),
PercentFree decimal(18,3),
Report_Date datetime default getdate()
)
DECLARE @DiskSpace TABLE
(
RowID int Identity Primary Key,
DriveLetter char(1),
TotalSize decimal(20,0),
AvailableBytes decimal(20,0),
DriveType char(10),
PercentFree decimal(18,3)
)
DECLARE @counter int
DECLARE @FSOobject int
DECLARE @RecordCount int
DECLARE @count int
DECLARE @string char(35)
DECLARE @DriveLetter char(1)
DECLARE @TotalSize char(50)
DECLARE @AvailableSpace char(50)
DECLARE @DriveType char(10)
DECLARE @error int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @ServerName sysname
SET @ServerName = @@ServerName
SET @count=0
SET @counter=67
SET @error=0
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSOobject OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
PRINT @desc
RETURN
END
WHILE @counter < 91 OR @error <3 p="p"> BEGIN
SET @String='Drives.item("'+char(@counter)+'").DriveLetter'
EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveLetter OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
SELECT @counter=@counter+1
SELECT @error=@error +1
GOTO ErrorPoint
END
SET @String='Drives.item("'+char(@counter)+'").TotalSize'
EXEC @HR = sp_OAGetProperty @FSOobject, @string, @TotalSize OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
SELECT @counter=@counter+100
SELECT @error=1
END
SET @String='Drives.item("'+char(@counter)+'").AvailableSpace'
EXEC @HR = sp_OAGetProperty @FSOobject, @string, @AvailableSpace OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
PRINT @desc
RETURN
END
SET @String='Drives.item("'+char(@counter)+'").DriveType'
EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveType OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
PRINT @desc
RETURN
END
IF @Error < 3
BEGIN
INSERT INTO @DiskSpace (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
VALUES (@DriveLetter, @TotalSize, @AvailableSpace, @DriveType, (convert(decimal(20,0), @AvailableSpace)/convert(decimal(20,0), @TotalSize)))
END
SELECT @counter=@counter+1
END
ErrorPoint:
INSERT INTO @DiskSpaceStats (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
SELECT DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree
FROM @DiskSpace
EXEC @hr = sp_OADestroy @FSOobject
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject
RETURN
END
SELECT * FROM @DiskSpace
3>
Comments
Post a Comment