Looking script to get the available disk space and other details.

Question

Looking script to get the available disk space and other details.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
September 19, 2012 at 10:15 pm

#262706

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.

Viewing 7 posts - 1 through 6 (of 6 total)

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

sestell1 SSChampion Points: 10230 More actions · Answer 1

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.

sqlnyc SSCommitted Points: 1726 More actions · Answer 2

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

sqlnyc SSCommitted Points: 1726 More actions · Answer 3

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.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

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.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

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.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

Thanks Guys.

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

Thanks.