0

Is there a way to know the free disk space available on the drive that the back up file will be created?

For example, SQL Server is installed in Server 1 and the back up file is scheduled to be created in network \server2.

Is there a way to know the free space of \server2?

If I execute xp_fixeddrives, it will only give drive space where the current instance is.

While sys.dm_os_volume_stats will only give drives where database_files are located.

Improve this question
4
  • 2
    is your question limited to t-sql? how about creating a powershell script to get the disk free space?
    – user37701
    Commented Jan 3, 2018 at 11:58
  • yes it is. do you have a snippet for that? of using xp_cmdshell is sufficient?
    – Edgar Allan Bayron
    Commented Jan 3, 2018 at 12:19
  • @EdgarAllanBayron, see stackoverflow.com/questions/12159341/….
    – Dan Guzman
    Commented Jan 3, 2018 at 12:44
  • You could use an external process (maybe one that already monitors disk space on all servers/drives) and have it log that information to a table whenever it runs. The nice thing there is that the process has access to SQL Server instead of all the hoops you'll need to go through to give SQL Server access to the remote file system.
    – Aaron Bertrand
    Commented Jan 3, 2018 at 15:52

1 Answer 1

Reset to default
3

One option would be to use XP_CMDSHELL and issue a simple DIR command and load the information into a table variable. Then, delete the rows that don't have the word free and you should get the line that indicates the amount of free space.

DECLARE @DirectoryInfo TABLE (TextLine VARCHAR(max))

INSERT INTO @DirectoryInfo
EXEC xp_cmdshell 'dir <YourRemoteDirectory>'

DELETE
FROM @DirectoryInfo
WHERE isnull(TextLine, '') NOT LIKE '%free%'

SELECT *
FROM @DirectoryInfo

16 Dir(s) 6,247,769,628,672 bytes free

Improve this answer
1
  • Thanks @scott. This snippet can be a good start for what I am looking for.
    – Edgar Allan Bayron
    Commented Jan 4, 2018 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.