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

Comments

Post a Comment

Popular posts from this blog

Embedding PowerBI with ASP.NET Core 2 and Angular(2,4,5)

Image
Background  Integration of PowerBI into ASP.Net is straightforward as far as you follow the Microsoft documentation. When it comes to ASP.NET Core 2, it is not, at the time of writing this article. Let me guide you through the steps I followed to successfully embed PowerBI reports to our ASP.NET Core2 / Angular5 enterprise website. Two Scenarios 1. User Owns Data - where users will be directly registered in PowerBi portal https://docs.microsoft.com/en-us/power-bi/developer/integrate-report 2. App Owns Data - you have an application which owns the data and you have users to view these reports but they are not registered in PowerBI as direct users - ISV situation https://docs.microsoft.com/en-us/power-bi/developer/embed-sample-for-customers Following steps are focused on scenario 2 which is app own data, but it is not limited to use this information for scenario 1 as well. [Flow of authentication and authorization (from Microsoft documentation)] Step 1: Regis
Read more

CSS text-overflow ellipsis in the beginning of the the text - Left Ellipsis using CSS in a Flex box

Image
Problem: If you want to have css text overflow ellipsis in the left (beginning of the text), there is no straight forward solution to get it working in all browsers (especially in Safari). There are several CSS tricks that you will end up only working few browsers. It will be further difficult, if you are trying to achieve this in flex container. Solution: Combining few ideas found in the stack-overflow, manged to get it working in most of the browsers including safari. ".cont" is flex box and either you can set a width to it, or can inherit the width from parent container element. .ellipsis:after { content: ""; background-color: white; color: transparent; position: relative; z-index: 2; min-width: 100%; display: inline-block; min-height: 1rem; } .ellipsis { direction: rtl; display: inline-block; width: calc(100% - .1em); white-space: nowrap; overflow: h
Read more

Handling Exit Event of Console Application in C#

Do you wan to do something on the exit event of Console Application. .Net framework does not provide such direct event to do that. You can use following workaround to have that kind of event in your application.         [STAThread]         static void Main(string[] args)         {             HandlerRoutine hr = new HandlerRoutine(Handler);             SetConsoleCtrlHandler(hr, true);             GC.KeepAlive(hr);          }         public static void DoOnAppExit()         {                   //Do something......         }         #region Handle App Exit         static Boolean Handler(CtrlTypes CtrlType)         {             // A switch to handle the event type.             switch (CtrlType)             {                 case CtrlTypes.CTRL_C_EVENT:                 case CtrlTypes.CTRL_BREAK_EVENT:                 case CtrlTypes.CTRL_CLOSE_EVENT:                 case CtrlTypes.CTRL_LOGOFF_EVENT:                 case CtrlTypes.CTRL_SHUTDOWN_EVENT:           
Read more