SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete files older than n-days via T-SQL


Delete files older than n-days via T-SQL

Author
Message
tsduke
tsduke
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 474
What 'added' characters?
Gordon-265412
Gordon-265412
Right there with Babe
Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)

Group: General Forum Members
Points: 768 Visits: 1188
The scriptbox appears to add characters (or uses a diff charset) for tabs.

What errors are you getting?



Edit:

If you are indeed only parsing it and not compiling it, it will generate errors as the temptables are not created if you only parse it (a very annoyying feature).

Your friendly High-Tech Janitor... Smile
tsduke
tsduke
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 474
I get the same errors either way.

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 41
Line 41: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 59
Line 59: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 65
Line 65: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 73
Line 73: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 77
Line 77: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 80
Line 80: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 89
Line 89: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 101
Line 101: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 117
Line 117: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 126
Line 126: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 127
Line 127: Incorrect syntax near ' '.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 133
Line 133: Incorrect syntax near ' '.
Gordon-265412
Gordon-265412
Right there with Babe
Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)

Group: General Forum Members
Points: 768 Visits: 1188
As I suspected...the tabs are being converted in the scriptbox when submitted.

I'm not sure what to tell you except to do a search & replace on the tab character. It fails for me with the same errors and it's defenitly the tabs being incorrectly converted when scripts are submitted.

Your friendly High-Tech Janitor... Smile
DBA-640728
DBA-640728
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2989 Visits: 2000
ForFiles does'nt support UNC paths, that is why i am trying to use this code :-)
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39046 Visits: 14411
Gordon-265412 (6/5/2008)
Forfiles is not included in every os by default. I needed to make sure that the code was supportable with no additional external software requirements (resource kit executables and such).

The Forfiles command would make life quite a bit simpler and it could be easily added to it and the FOR command removed...


To be fair xp_cmdshell is not enabled on SQL Server instances by default, and is barred from many environments for a long list of good reasons surrounding misuse and security.

PowerShell ships with SQL Server. It is disabled by default, mostly to force you choose how you want to allow it to run in your environment, from wide-open to unrestricted. Open a PowerShell prompt and run this command to set the level to a happy medium so you can get started:

Set-ExecutionPolicy RemoteSigned


Reference: http://technet.microsoft.com/en-us/library/dd347628.aspx

Once PowerShell is allowed here is a one-liner to do the same. Adjust the constants to suit...remove the -WhatIf to have it really do the deletion:

ls -Path "\\FooServer\BarShare\" -Filter "FooFile_*" |? {$_.LastWriteTime -lt (get-date).AddDays(-3)} | Remove-Item -WhatIf


To also look in all sub-folders:

ls -Path "E:\Backups\" -Filter "*.bak" -Recurse |? {$_.LastWriteTime -lt (get-date).AddDays(-3)} | Remove-Item -WhatIf


* You can also change .AddDays to .AddHours if needed

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Mike Dougherty-384281
Mike Dougherty-384281
SSC Eights!
SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)

Group: General Forum Members
Points: 838 Visits: 944
While this code does the job I have to ask, "Why would you want your SQL Server deleting files?"

There isn't even any data-driven relationship to the files. We induct call recordings (.mp3) and store the files on disk for different duration based on the call result (sales, refusals, retry) - in that case it is the data that determines the files to delete. Rather than making thousands of cmdshell calls in a loop (or some other tedious operations) We have a [WSH] script that contacts a webservice to get a work-batch, it tracks the status of each operation in the batch and reports the results back to the database through another webservice call. There are two low-cost calls to the database and the heavy lifting on filesystem is done by a machine other than the SQL Server.

Well, environments vary. I guess we use whatever tools we have available. Maybe when we're hammering ever-harder on that screwdriver we should stop to consider if a chisel would be the right tool for the job.
anandkreddy
anandkreddy
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 86
Please can any one send the t-sql script to delete the files from folder older than x days for sql server 2008 on windows 2008 r2
micber
micber
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 193
all you have to do is change the version check from

IF @OSVersion = '5.2'

to IF @OSVersion >= '5.2'

umm yeah thats easy.

also I know this is an old thread but I am using this due to an issue I am having using the maint cleanup task not working. this is kinda slow if you call it repetavily and with many file.. I use a cursor to call it and it takes abt 20 min. Not really in a hurry though


DECLARE @databasename as varchar(200)
declare @strSQL as nvarchar (4000)

DECLARE Curse CURSOR local fast_forward
FOR
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name not in ('tempdb','AdventureWorks','AdventureWorksDW')
order by name desc
OPEN Curse

FETCH next FROM Curse INTO @databasename

WHILE @@fetch_status = 0
BEGIN

set @strSQL = 'EXEC DBA.dbo.usp_Admin_Delete_Files_By_Date @SourceDir = ''\\resnetapp01\prodsqlbackups$\NIGHTLY\RESMSSQL2008\' + @databasename + '\'', @SourceFile = ''*'', @DaysToKeep = 5'

EXEC dbo.sp_executesql @strSQL

fetch next from Curse into @databasename

END

close Curse
deallocate Curse



Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Gordon-265412
Gordon-265412
Right there with Babe
Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)

Group: General Forum Members
Points: 768 Visits: 1188
How well does it run for you without the cursor? How many files are you deleting when using the cursor per database?

I've not used this inside a cursor, I usually just strip the stored procedure extras and run it ad-hoc if I want to clean up something manually. But even then it has not run that long (20min) and I've got a slow network share drive...

Could the slowness possibly be coming from trying to delete a lot of files over the network?

I'm glad that it's working for you. Hopefully we/I can help figure out why it's slow on occasion...

Gordon

Your friendly High-Tech Janitor... Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search