Check Up Size Of Each Folder In a Path

  • Comments posted to this topic are about the item Check Up Size Of Each Folder In a Path


  • Sir

    I am trying to use your script but i found.....

    this error......


    Error %d, Level %d, State %d, Procedure %s, Line %d, Message: SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.



  • Turn on xp_cmdshell thru sp_configure.

    By default the feature is turned off and u need to enable it before you start your work.


  • Use this:=

    IF NOT EXISTS (SELECT value_in_use

    FROM sys.configurations

    WHERE name = 'xp_cmdshell'

    AND value_in_use = 1)


    EXEC ('sp_configure ''show advanced options'', 1')


    EXEC ('sp_configure ''xp_cmdshell'', 1')


    EXEC ('sp_configure ''show advanced options'', 0')




  • Thank you sir...

    Now its working... 🙂

  • For backup size of a folder , you may use this script:=

    drop table #tfoldsizeinMB

    create table #tfoldsizeinMB(size nvarchar(1000))

    declare @cmd varchar(3000)='powershell "(Get-ChildItem ''D:\backups\MSSQLSERVER\'' -recurse | Measure-Object -property length -sum).sum/1MB"'

    select @cmd

    insert into #tfoldsizeinMB

    EXEC xp_cmdshell @cmd

    select * from #tfoldsizeinMB where size is not null


  • drop table #tfoldsizeinMB

    create table #tfoldsizeinMB(size nvarchar(100))

    declare @cmd varchar(3000)='powershell "(Get-ChildItem ''D:\backups\MSSQLSERVER\'' -recurse | Measure-Object -property length -sum).sum/1MB"'

    --select @cmd

    insert into #tfoldsizeinMB

    EXEC xp_cmdshell @cmd

    select CEILING(size) as 'Size(MB)' from #tfoldsizeinMB where size is not null


  • Thanks for sharing this procedure. Very useful. However I ran into a problem when executing against a folder with several levels of nested folders. The problem is caused by declaring @command as SYSNAME. The sysname datatype is limited to 128 characters. So when the code looped through the sub-folders and modified the @command variable (set @command = 'dir "' + @curdir +'"') eventually it exceeded the limit of 128 characters.

    All I did was change @command from sysname to VARCHAR(1000) and that fixed the issue.

    Thanks again,


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

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