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

get total disk space per instance from within SQL without cmdshell. Expand / Collapse
Author
Message
Posted Wednesday, May 11, 2011 5:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:31 AM
Points: 79, Visits: 430
For policy reasons I can't enable cmdshell.

I can get disk free space via xp_fixeddrives. But is it possible to get total disk space (ie. free space + used space).

I need this for all drives (so can't minus SQL file sizes off free space as there are non sql files).

I need to run on multiple instances & regularly.

Thanks in advance!
Post #1106767
Posted Wednesday, May 11, 2011 5:19 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 18, 2011 3:54 AM
Points: 492, Visits: 481
declare @driveName varchar(100)
declare @query varchar(1000)
declare @fsinfo varchar(1000)

set @driveName = 'C'

set @query ='fsutil volume diskfree '+@driveName+':\'
set @fsinfo = 'fsutil fsinfo volumeinfo '+@driveName+':\'

exec xp_cmdshell @query
exec xp_cmdshell @fsinfo

check this T-SQL


Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com
Post #1106771
Posted Wednesday, May 11, 2011 5:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:31 AM
Points: 79, Visits: 430
Thanks, but I can't use cmdshell - frustrating I know!
Post #1106774
Posted Wednesday, May 11, 2011 5:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:31 AM
Points: 79, Visits: 430
By which I mean I'm prevented by standard operating procs here ... not that there's a technical issue in doing so.
Post #1106778
Posted Wednesday, May 11, 2011 6:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 20,572, Visits: 9,618
Syed Jahanzaib Bin hassan (5/11/2011)
declare @driveName varchar(100)
declare @query varchar(1000)
declare @fsinfo varchar(1000)

set @driveName = 'C'

set @query ='fsutil volume diskfree '+@driveName+':\'
set @fsinfo = 'fsutil fsinfo volumeinfo '+@driveName+':\'

exec xp_cmdshell @query
exec xp_cmdshell @fsinfo

check this T-SQL



Forgot to read again???
Post #1106815
Posted Wednesday, May 11, 2011 6:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 20,572, Visits: 9,618
Is powershell an option? I can't code with it but I'm sure google would solve that one for ya.

<oi>sql?

SSIS?
Post #1106816
Posted Wednesday, May 11, 2011 6:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
You could always use SP_OACreate.

Look at this thread for a possible script:-
http://www.sqlservercentral.com/Forums/Topic113227-5-1.aspx



Post #1106824
Posted Wednesday, May 11, 2011 6:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:31 AM
Points: 79, Visits: 430
thanks guys. appreciate your time. i'll have a look @ those options now.
Post #1106834
Posted Tuesday, December 11, 2012 3:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 3:27 AM
Points: 51, Visits: 28
itws working

thanxs
Post #1394996
Posted Tuesday, December 11, 2012 3:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 3:27 AM
Points: 51, Visits: 28
thnxs
Post #1394997
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse