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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Looking script to get the available disk...

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

Rate Topic

Display Mode

Topic Options

Author

Message

SQL-DBA-01

Posted Wednesday, September 19, 2012 10:15 PM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 1:33 AM
Points: 738, Visits: 1,126

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

sestell1

Posted Thursday, September 20, 2012 7:09 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:36 AM
Points: 837, Visits: 1,219

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

sqlnyc

Posted Friday, September 21, 2012 8:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:16 PM
Points: 49, Visits: 438

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

sqlnyc

Posted Friday, September 21, 2012 8:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:16 PM
Points: 49, Visits: 438

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

Steve Jones - SSC Editor

Posted Friday, September 21, 2012 8:55 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:20 AM
Points: 31,437, Visits: 13,752

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

Steve Jones - SSC Editor

Posted Friday, September 21, 2012 8:56 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:20 AM
Points: 31,437, Visits: 13,752

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

SQL-DBA-01

Posted Friday, September 21, 2012 9:08 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 1:33 AM
Points: 738, Visits: 1,126

Thanks Guys.

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

Thanks.

Post #1362765

« Prev Topic | Next Topic »

Permissions