Check Up Size Of Each Folder In a Path

Question

Post reply

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.

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

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

programmertripatsingh SSC Journeyman Points: 82 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

programmertripatsingh

SSC Journeyman

Points: 82

May 28, 2015 at 9:01 pm

#1801455

Thank you sir...

Now its working... 🙂

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

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 · Answer 6

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: 2727 More actions · Answer 7

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