disk space free/total disk space
-
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
-
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
-
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
-
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
-
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
-
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
C410
D1271
E10356.
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
-
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
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars -
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
-
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 freeintegerInterpreted as an unsigned value. Low-order double word of available media resource.
high freeintegerInterpreted 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
-
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
🙁
-
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
-
I think your parameter is looking for an a: drive. There is probably a dialog asking for you to insert a disk.
Steve Jones
-
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 ??
🙂
-
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
).
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply