Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Finding out Total Disk Space in TSQL Expand / Collapse
Author
Message
Posted Monday, February 3, 2014 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 3:14 PM
Points: 7, Visits: 10
I have the same challenge and I have to query thousands of servers many of which do not allow the use of OA_ and also do not allow the use of Powershell for security reasons. Any work-arounds for that? TIA


Kindest Regards,

JimAtWork
Post #1537374
Posted Wednesday, September 3, 2014 1:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 20, 2015 9:19 AM
Points: 31, Visits: 188
JimAtWork (2/3/2014)
I have the same challenge and I have to query thousands of servers many of which do not allow the use of OA_ and also do not allow the use of Powershell for security reasons. Any work-arounds for that? TIA


Not the most elegant solution, and only works for 2008R2+. but it gets the job done.

SELECT distinct(volume_mount_point), total_bytes/1048576 as Size_in_MB, available_bytes/1048576 as Free_in_MB
FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, available_bytes/1048576 order by 1

from: http://msdn.microsoft.com/en-us/library/hh223223.aspx and some Pinal Dave advices.

read more at: http://thelonelydba.wordpress.com/2014/09/03/ms-sql-find-total-server-disk-space-in-t-sql

hope this helps!
Post #1610251
Posted Wednesday, September 3, 2014 3:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:08 PM
Points: 36,646, Visits: 33,405
JimAtWork (2/3/2014)
I have the same challenge and I have to query thousands of servers many of which do not allow the use of OA_ and also do not allow the use of Powershell for security reasons. Any work-arounds for that? TIA


Sorry for the late reply but, Yes... DOS batch code with calls to WMI.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1610302
Posted Thursday, January 8, 2015 8:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 27, 2015 8:14 AM
Points: 36, Visits: 77
Can someone help to get the total space, free space and used space with the servername of the disk.
Post #1649517
Posted Thursday, January 8, 2015 10:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:42 AM
Points: 31,667, Visits: 16,128
Does the query posted above by mauriciorpp not help?

What is the servername of the disk?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1649568
Posted Thursday, January 15, 2015 7:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 27, 2015 8:14 AM
Points: 36, Visits: 77
Hi Steve,

Its giving me the below error:

Invalid object name 'sys.dm_os_volume_stats
Post #1651563
Posted Thursday, January 15, 2015 8:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 1,192, Visits: 3,816
Jeff Moden (9/3/2014)
JimAtWork (2/3/2014)
I have the same challenge and I have to query thousands of servers many of which do not allow the use of OA_ and also do not allow the use of Powershell for security reasons. Any work-arounds for that? TIA


Sorry for the late reply but, Yes... DOS batch code with calls to WMI.


Attached is an old vb script that checks free space percentage. It uses Win32_PerfFormattedData_PerfDisk_LogicalDisk WMI class. You probably want to use the Win32_LogicalDisk class.

This was used to send back free space to a centralized monitoring system. The comments should point you in the right direction.

You will need to change the extension to .vbs

Hope this helps.


Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/


  Post Attachments 
MSSQL_Check_Disks .txt (2 views, 7.52 KB)
Post #1651579
Posted Thursday, January 15, 2015 8:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:42 AM
Points: 31,667, Visits: 16,128
Ah, sorry, didn't realize this was a SQL 2000 instance.

You could use something like prasenjit221's code. I don't have a SQL 2000 instance handy to test, but that looks compatible. Depending on OS, I might use Powershell or VBscript instead and have those languages insert the data into a table that you can query. That's because I've found I don't usually need this in real time. I might have a job that updates this once a day or once an hour.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1651580
Posted Thursday, January 15, 2015 8:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:42 AM
Points: 31,667, Visits: 16,128
JimAtWork (2/3/2014)
I have the same challenge and I have to query thousands of servers many of which do not allow the use of OA_ and also do not allow the use of Powershell for security reasons. Any work-arounds for that? TIA


Not to hijack, but Powershell runs all over the place in MS products, so it can't be stopped. Is this just for user scripts?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1651593
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse