Check Up Size Of Each Folder In a Path

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

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

    Thanks.

  • programmertripatsingh

    SSC Journeyman

    Points: 82

    Sir

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

    this error......

    Error_Message---

    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.

    Error_Procedure----

    xp_cmdshell

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    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.

    Thanks.

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    Use this:=

    IF NOT EXISTS (SELECT value_in_use

    FROM sys.configurations

    WHERE name = 'xp_cmdshell'

    AND value_in_use = 1)

    BEGIN

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

    RECONFIGURE;

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

    RECONFIGURE;

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

    RECONFIGURE;

    END

    Thanks.

  • programmertripatsingh

    SSC Journeyman

    Points: 82

    Thank you sir...

    Now its working... 🙂

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    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

    Thanks.

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    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.

  • Lee Linares

    SSCrazy

    Points: 2693

    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,

    Lee

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

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