disk space free/total disk space

  • Wesley Brown

    SSChampion

    Points: 12035

    I'm trying to find the percent of free disk space from a t-sql script so I don't run some operations and blow up the server due to out of disk space errors. I'm stumped.

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Andy Warren

    SSC Guru

    Points: 119676

    You could use the FileSystemObject, it will return disk space info. Or maybe cmdshell Dir *.* > diskinfo.txt, then parse out the bytes? Hackaroo! I think you can also get this via WMI, though I have no experience with it.

    Andy

  • SQLZ

    SSChampion

    Points: 12872

    If you have the NT Resource kit installed you can run srvinfo (I'm pretty sure you need the NT R Kit) from the command prompt, using xp_cmdshell as Andy explains above.

    The syntax for srvinfo is:

    srvinfo -ns \\<servername>

    The output is shown below and can be put into a temp table and cleaned up.

    Drive: [FileSys] [ Size ] [ Free ] [ Used ]

    C$ NTFS 8192 3057 5135

    D$ NTFS 8192 3581 4611

    F$ NTFS 104141 67558 36583

    G$ NTFS 9169 9120 49

    H$ NTFS 104164 40172 63992

    I$ NTFS 104164 51026 53138

    O$ NTFS 104164 88030 16134

    T$ NTFS 52078 40512 11566

    Or...

    xp_fixeddrives gives you the amount of free space on each physical drive on the server.

    Obviously xp_fixeddrives is easier but it only tells you the free space, whereas srvinfo gives more detailed info on the size, free space and used space.

    Regards,

    Karl

    Karl Grambow

  • Wesley Brown

    SSChampion

    Points: 12035

    I could use xp_fixeddrives then do a compare against the size of the object being written to disk. I think that will work. Where did you find the sproc? I looked through the SQL2K resource kit and BOL.

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • SQLZ

    SSChampion

    Points: 12872

    I haven't been able to find any documentation on the xp_fixeddrives sproc. I needed similar functionality to what you're trying to do and I just happened to stumble across it when I was browsing through the extended procedures list in the master database one day (I didn't have anything much better to do ).

    Regards,

    Karl Grambow

  • David Benoit

    SSC-Dedicated

    Points: 34562

    That proc is undocumented but, if you just run it as is i.e. xp_fixeddrives it will return something like as follows;

    drive MB free

    C 410

    D 1271

    E 10356.

    Don't know if there are any switches accepted which will cause variation in the output, but would be interested to find out.

    I looked in both the B. Knight Admin911 book and "Guru Guide to Transact SQL" and that is the basic coverage they have as well so, that could be the full functionality of the proc.

    David

    David

    @SQLTentmaker

    β€œHe is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Brian Knight

    One Orange Chip

    Points: 27249

    The xp_fixeddrives will only show you the free space in MB. I'm afraid it can't help output the percentage. I like the FileSystemObject idea or especially the srvinfo via xp_cmdshell idea. Anyone care to write a new extended proc to do it?

    Brian Knight

    bknight@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bknight

  • Wesley Brown

    SSChampion

    Points: 12035

    The xp_fixeddrives lets me do what I need to do. I already have two calls to xp_cmdshell in my script and really didn't want another. I just dump the output from xp_fixeddrives compare it to the db size before I write it down to disk. Its just an insurance policy to keep me from writing a 10 gb database to a disk that only has 5 mb of space :). I'm prety much done writing all the backup/restore/maint/schedule/security scripts now. I got really lazy about not writing scripts to do admin task but now with the environment I'm in its almost the only option I have. But, to answer your question I would be more than happy to write a sample using the output from the xp_cmdshell dump to figure percentage free. πŸ˜›

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Andy Warren

    SSC Guru

    Points: 119676

    Was curious about alternatives, havent found anything really exciting. One is this bit of code from the MSDN site that uses WMI (which you may need to install, look in x86\other\wmi):

    Dim DiskSet As Variant

    Dim Disk As Variant

    Set DiskSet = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery("select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3")

    For Each Disk In DiskSet

    If (Disk.FreeSpace / Disk.Size) > 0.2 Then

    Debug.Print "Drive " + Disk.Name + " is low on space."

    End If

    Next

    DMO sorta lets you get the answer, if anyone knows how to translate the results:

    Dim oserver As SQLDMO.SQLServer

    Dim oResult As SQLDMO.QueryResults

    Dim J As Integer

    Dim K As Integer

    Set oserver = New SQLDMO.SQLServer

    oserver.LoginSecure = True

    oserver.Connect

    Set oResult = oserver.EnumAvailableMedia(SQLDMOMedia_FixedDisk)

    For J = 1 To oResult.Rows

    For K = 1 To oResult.Columns

    Debug.Print oResult.ColumnName(K), oResult.GetColumnString(J, K)

    Next

    Next

    oserver.DisConnect

    Set oserver = Nothing

    On my machine this returns:

    name C:low free 877740032

    high free 11

    media type 2

    There is 61g free on the machine. Says this in BOL:

    low free integer Interpreted as an unsigned value. Low-order double word of available media resource.

    high free integer Interpreted as an unsigned value. High order double word of available media resource.

    File scripting object is looking pretty good. Maybe a job that runs it and updates a table periodically - you could always run the job just before you run other scripts to get most current data.

    Amazing - you'd think this would be readily available - missing something somewhere?

    Andy

  • dimadd

    SSC-Addicted

    Points: 420

    Why i have a problem runnig

    EXECUTE master.dbo.xp_fixeddrives 2

    When i run it as

    EXECUTE master.dbo.xp_fixeddrives

    the result is

    drive MB free

    ----- -----------

    C 5497

    E 12926

    F 20185

    (3 row(s) affected)

    But when i run as

    EXECUTE master.dbo.xp_fixeddrives 2

    there is long timeout for 1 hour and after

    it i jast close the connection

    πŸ™

  • Wesley Brown

    SSChampion

    Points: 12035

    I get the fixed drives data and dump it into a temp table I dont' select just one drive. I'll see if I can replicate your error.

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716331

    I think your parameter is looking for an a: drive. There is probably a dialog asking for you to insert a disk.

    Steve Jones

    steve@dkranch.net

  • dimadd

    SSC-Addicted

    Points: 420

    No the SQL Server enterpise manager executing

    EXECUTE master.dbo.xp_fixeddrives 2

    when i do restore from backup and whant to select the backup

    file it jast don't open the dialog of choose file.

    the program not responding

    and i see in dbcc inputbuffer tha the connection of enterprise execute the master.dbo.xp_fixeddrives 2.

    and when i tryed to do this in query analyser there was also problem.

    All things in the sqlserver worked properly

    and also restore from backup without choose file dialog.

    But when i tried restart MSSql there was a problem

    it was down in the service manager and not down in the task

    manager. and the master.mdf was in use.

    Only after restart of computer all thing was good

    and alse EXECUTE master.dbo.xp_fixeddrives 2

    was good

    What was a problemm ??

    πŸ™‚

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716331

    Perhaps it had a security validation issue for a mapped drive? Sometimes MyComputer hangs when searching for a mapped drive before it times out and displays the "x" for not connected. Perhaps this was the issue?

    If it hangs again, I'd log into the server console using the SQL Server account and check if you can get My Computer to come up.

    Steve Jones

    steve@dkranch.net

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply