Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

delete files before 8PM Expand / Collapse
Author
Message
Posted Monday, October 7, 2013 4:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:52 PM
Points: 208, Visits: 1,020
hello experts,
i have some error in using "sp_delete_file" Stored procedure. so looking some other option or script to delete files before 8 PM. daily. if some one can design such script please help.
Post #1502072
Posted Monday, October 7, 2013 4:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,836, Visits: 3,952
Zeal-DBA (10/7/2013)
hello experts,
i have some error in using "sp_delete_file" Stored procedure. so looking some other option or script to delete files before 8 PM. daily. if some one can design such script please help.


YOu can use maintenance plan wizard
OR
By command shell too see link http://www.sevenforums.com/tutorials/55728-file-delete-command-prompt.html


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1502076
Posted Monday, October 7, 2013 5:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:52 PM
Points: 208, Visits: 1,020
hi,
maintainenace plan itself uses internally same SP "XP_delete_file", i tried using XP_cmdshell 'forefile...

but it does not facilitate to provide time or hours... only days can be defined.
how to define time or 5 hour before in DEL\Forfiles commands
Post #1502083
Posted Monday, October 7, 2013 5:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,836, Visits: 3,952
Zeal-DBA (10/7/2013)
but it does not facilitate to provide time or hours... only days can be defined.
how to define time or 5 hour before in DEL\Forfiles commands
i am not comfotable in command shell on your requirement but yes this can be achieve easilt in Tsql by using " DATEPART function or waitfor delay


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1502085
Posted Tuesday, October 8, 2013 5:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 428, Visits: 928
Look up the powershell commands get-childitem and remove-item, and you should be well on your way.

You may even find a script to do what you want.

Get back to us if you need more help once you've tried it yourself.




Post #1502551
Posted Tuesday, October 8, 2013 5:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:52 PM
Points: 208, Visits: 1,020
actually i am quite new on this powershell scripting, it seems quite complex on this stage. so i have got following script and modified in my ways replaced hours in place of days. when i executed the same it gave me some } error.

DECLARE @pscmd varchar(1000)
DECLARE @targetpath varchar(8000)
DECLARE @olddays int
DECLARE @extension varchar(5)
DECLARE @cmdstring varchar(1000)
--assigning value to parameters, you can customize as per your need
SET @targetpath = 'D:\Dtest\'
SET @olddays = -6 --pass the Hours with negative values
SET @extension = 'txt'
SET @pscmd = '"& '+
'Get-ChildItem ' + Quotename(@targetpath,'''') + ' | '+
'where {$_.lastWriteTime -lt ((Get-Date).AddHours('+ CAST(@olddays as varchar) +'))
-and ($_.Extension -match ' + QUOTENAME(@extension,'''')+ ') } | ' +
'Remove-Item -force " '
SET @cmdstring = ' ""powershell.exe" '+ @pscmd
exec master..xp_cmdshell @cmdstring
Post #1502558
Posted Tuesday, October 8, 2013 6:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 428, Visits: 928
What's the error you're getting?





Post #1502566
Posted Tuesday, October 8, 2013 6:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:52 PM
Points: 208, Visits: 1,020
i printed the Tsql fron @cmddtring variable which is actually getting executed at last, kindly find the printed or actual executed text of @cmdstring below, i have not added any double quote just pasting same what i am getting.

""powershell.exe" "& Get-ChildItem 'D:\Dtest\' | where {$_.lastWriteTime -lt ((Get-Date).AddHours(-6))
-and ($_.Extension -match 'txt') } | Remove-Item -force "



below is the error :

Missing closing '}' in statement block.
At line:1 char:83
+ & Get-ChildItem 'D:\Dtest\' | where {$_.lastWriteTime -lt ((Get-Date).AddHour
s(-6)) <<<<
NULL


Post #1502570
Posted Tuesday, October 8, 2013 6:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 428, Visits: 928
You were very close: this needs to be in brackets:

($_.lastWriteTime -lt ((Get-Date).AddHours(-6)))



Post #1502577
Posted Tuesday, October 8, 2013 7:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
cls
$Path = 'c:\temp\'
$RefTs = ([datetime](Get-Date -Format 'yyyy-MM-dd')).AddHours(20) # 8PM
$RefTs
# select and delete files having last write time before RefTs
get-childitem -Path $Path -Filter '*.txt' -Recurse | Where-Object { $_.PSIsContainer -eq $false -and $_.lastwritetimeutc -lt $RefTs } | Remove-Item -Force


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1502588
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse