get total disk space per instance from within SQL without cmdshell.

  • 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!

  • 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

  • Thanks, but I can't use cmdshell - frustrating I know!

  • By which I mean I'm prevented by standard operating procs here ... not that there's a technical issue in doing so.

  • 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???

  • Is powershell an option? I can't code with it but I'm sure google would solve that one for ya.

    <oi>sql?

    SSIS?

  • You could always use SP_OACreate.

    Look at this thread for a possible script:-

    http://www.sqlservercentral.com/Forums/Topic113227-5-1.aspx

  • thanks guys. appreciate your time. i'll have a look @ those options now.

  • itws working

    thanxs

  • thnxs

Viewing 10 posts - 1 through 9 (of 9 total)

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