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

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

Selenium With .Net

Recently I had a chance to work with Selenium framwork. It is pretty easy to intrgrate automated testing into .net with Selenium. Also for project like web site scrapping and automated posting, can be easily developped with few lines of code. Following code lines will automate a web site login with use of selenium framework. ISelenium selenium = new DefaultSelenium(seleniumServer, port, browser, website); selenium.Start(); selenium.Open(ConfigLoader.Configurations.LoginPageUrl); selenium.WaitForPageToLoad((ConfigLoader.Configurations.WaitingTime * 1000).ToString()); selenium.Type("ctl00_PageContent_UserName", ConfigLoader.Configurations.UserName); selenium.Type("ctl00_PageContent_Password", ConfigLoader.Configurations.Password); selenium.Click("ctl00_PageContent_OKButton__Button"); selenium.WaitForPageToLoad((ConfigLoader.Configurations.WaitingTime * 1000).ToString());
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