September 13, 2011 at 6:57 am
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
September 13, 2011 at 7:15 am
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.
September 27, 2011 at 9:56 am
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
May 30, 2012 at 6:54 am
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
May 30, 2012 at 7:57 pm
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... 🙂
May 31, 2012 at 7:24 am
somehow I think its on my end in my cursor. Not sure why. If I run the proc manually on a lot of files it is very quick. I think I could avoid the cursor by doing it set based to execute but i am unsure if this will make it faster...
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply