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

Looking script to get the available disk space and other details. Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 10:15 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 802, Visits: 1,357
hi,
I am looking for a custom script to get the details from all the servers while running the script in the registered server. The script should work fine in all versions of sql servers.

Servername, sqlservername,drive, totaldrivespace and availabledrivespace details

kindly assist.


Thanks.
Post #1361723
Posted Thursday, September 20, 2012 7:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 1,277, Visits: 2,199
Depending on the version of Windows your servers are running, you should be able to get the disk space information doing something like this:

EXEC xp_CMDShell 'wmic volume get capacity,caption,freespace'

You could insert the return data into a table variable, then add on the server name and such.
Post #1361942
Posted Friday, September 21, 2012 8:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 57, Visits: 615
There are a number of ways to do this --

Servername, sqlservername,drive, totaldrivespace and availabledrivespace details

@@servername returns the machine name and sql instance name
xp_fixeddrives returns the drives and available drive space.

total drive space is another story, because SQL Server can not directly produce the results you, to my knowledge.

You can use Powershell, which can obtain hardware information through WMI and SQL details through SMO.

Perhaps the best method might depend on how you want to format the results, and whether or not the connectivity can use a Trusted connection.

xp_cmdshell (which is turned off by default in 2008) can get you this info, but also represents a potential security risk. Also, using xp_cmdshell, some of the results will not come back in a friendly format, and so will require more manipulation.

sqlnyc

Post #1362698
Posted Friday, September 21, 2012 8:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 57, Visits: 615
One further thought - I'm not sure what the earliest version of SQL Server that Powershell work with, so it might not be a viable solution.
Post #1362700
Posted Friday, September 21, 2012 8:55 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 11:43 AM
Points: 33,059, Visits: 15,172
From here: http://technet.microsoft.com/en-US/library/ee177003.aspx

I believe XP/Win2K3 are the earliest you can use.

With SQL Server, I'd guess SQL Server 2005, but it might be 2008.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1362740
Posted Friday, September 21, 2012 8:56 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 11:43 AM
Points: 33,059, Visits: 15,172
Saw another note that it's .NET Framework 2.0, so where you can run that you can probably make Powershell work.

It should work with 2005.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1362741
Posted Friday, September 21, 2012 9:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 802, Visits: 1,357
Thanks Guys.

However, neither powershell nor xp_cmdshell, do work in SQL Server 2000 !


Thanks.
Post #1362765
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse