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.

  • 2
    is your question limited to t-sql? how about creating a powershell script to get the disk free space? – user37701 Jan 3 '18 at 11:58
  • yes it is. do you have a snippet for that? of using xp_cmdshell is sufficient? – Edgar Allan Bayron Jan 3 '18 at 12:19
  • @EdgarAllanBayron, see stackoverflow.com/questions/12159341/…. – Dan Guzman Jan 3 '18 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 Jan 3 '18 at 15:52
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

  • Thanks @scott. This snippet can be a good start for what I am looking for. – Edgar Allan Bayron Jan 4 '18 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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