Check Up Size Of Each Folder In a Path

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  May 20, 2015 at 10:25 am

  #298516

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

  Thanks.

• programmertripatsingh

  SSC Journeyman

  Points: 82

  More actions

  May 28, 2015 at 2:05 am

  #1801167

  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: 33518

  More actions

  May 28, 2015 at 5:37 am

  #1801213

  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: 33518

  More actions

  May 28, 2015 at 2:22 pm

  #1801403

  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

  More actions

  May 28, 2015 at 9:01 pm

  #1801455

  Thank you sir...

  Now its working...

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  July 16, 2015 at 10:49 am

  #1813073

  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: 33518

  More actions

  July 16, 2015 at 10:53 am

  #1813076

  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: 2730

  More actions

  August 18, 2016 at 9:06 am

  #1896181

  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