May 14, 2010 at 7:53 am
I set up a maintenance plan to delete .csv files that are generated by SQL Server Agent for another purpose. I set up the plan to delete all files with the extension csv that are older than 4 weeks of age. I've done this before with backup related files and it works fine, but it doesn't seem to be working for my csv files. I do not get an error when running the job. In fact is says it executed properly, but when I open the folder, there are all the files. Can I not do this in Mainentance Plan?
May 14, 2010 at 8:16 am
I have nerver been able to get it to work like that.
I have this script that I just posted for Lucas in another thread. I use this to delete Audit Files. I tweaked it for him to delete backup files.
you need to point this to the file folder you are storing your files in
so edit
set @filepath = '"D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"'
And if you have a common file name enter the common portion of that under here or you could take out the common filename part of the code and just go with .csv
delete from
@myTable2
where FileNames not like '[insertcommonfilename]%' or FileNames is null
or lower(RIGHT(FileNames, 4)) <> '.csv'
Here is where you set the date of how old the files need to be, in order to be deleted, I set it to 28 days for your 4 weeks. you can change the format from days to weeks to hours....whatever you like
--*****************************************************
--Set the @filecount variable that we will use for our
--loop
--*****************************************************
set @filecount = (select COUNT (*) from @myTable3 where FileCreationDate < DATEADD(dd, -28,getdate()))
And here is the whole thing put this in a T-SQL Task, schedule it and it will run like clock work.
--**********************************************************
--Enable the ability to set advance settings and xp_cmdshell
--**********************************************************
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'xp_cmdshell', 1
go
reconfigure
go
--******************************************************
--Declare variable's to use in the code and set their values
--******************************************************
declare @myTable as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileInfo varchar(max))
declare @myTable2 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max))
declare @myTable3 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max),
FileCreationDate datetime)
declare @myTable4 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max),
FileCreationDate datetime)
declare @filecount int
declare @i int
declare @filepath varchar(100)
declare @sqlcmd varchar(max)
declare @sqlcmd2 varchar(max)
declare @sqlcmd3 varchar(max)
declare @filename1 varchar(100)
set @filepath = '"D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"'
set @sqlcmd= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ''''
set @sqlcmd2= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ' /b' + ''''
set @i = 1
--*********************************************************
--insert the contents of the direcotry path into our table
--variables. @myTable will hold the file names, @myTable2
--will hold all of the file names and the size and date of
--creation
--********************************************************
insert @myTable exec(@sqlcmd)
insert @myTable2 exec(@sqlcmd2)
--******************************************************
--we only want to delete the cvs files you are looking for
--******************************************************
delete from
@myTable2
where FileNames not like '[insertcommonfilename]%' or FileNames is null
or lower(RIGHT(FileNames, 4)) <> '.csv'
--******************************************************
--Insert into @myTable3 the FileName and Date info from
--the file directory, convert the char date text into a
--real datetime field
--******************************************************
insert into @myTable3
select
mt2.filenames,
Case (select count(*) from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%'))
when 1 then
(
convert(datetime,left((select mt1.fileinfo from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%')), 20))
)
else
(
convert(datetime,left((select top 1 mt1.fileinfo from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%') and mt1.FileInfo like ('%' + mt2.FileNames + '_%')), 20))
)
end as FileCreationDate
from @myTable2 mt2
--*****************************************************
--Reinsert all data into @myTable4 so the myID column
--will incriment at the same rate as the counter we will
--use to delete these files from the directory
--*****************************************************
insert into @myTable4
select FileNames, FileCreationDate
from @myTable3
order by FileCreationDate
--*****************************************************
--Set the @filecount variable that we will use for our
--loop
--*****************************************************
set @filecount = (select COUNT (*) from @myTable3 where FileCreationDate < DATEADD(dd, -28,getdate()))
--*******************************************************
--Begin loop, @i should match @myTable4.myID, so that way
--we can select a filename from @myTable4 to delete from
--the file structure.
--*******************************************************
while @i <= @filecount
Begin
set @filename1 = (select filenames from @myTable4 where myID= @i)
set @sqlCmd3 = 'master..xp_cmdshell' + ''''+ 'del /Q ' + @filepath + @filename1 + ''''
exec(@sqlCmd3)
set @filename1=''
set @sqlcmd3=''
set @i = @i + 1
end
--*******************************************************
--Re-Configure the server not to display advanced options
--Re-Configure the server not to allow xp_cmdshell commands
--to be run
--*******************************************************
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'xp_cmdshell', 0
go
reconfigure
go
exec sp_configure 'show advanced options', 0
go
reconfigure
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 14, 2010 at 11:30 am
I believe you would have given file extension as '.csv' while configuring maintenance plan. Just remove that '.' and it should be fine.
Thanks
May 14, 2010 at 11:57 am
I didn't use .csv. Just plain old csv. I don't know why the Maintenance Plan does not work, but it doesn't. I'd like to get SQL to do it using the Agent, but I can do above or I can just delete all of last years files. I chose the latter.
May 14, 2010 at 12:29 pm
I believe that is because SQL stores the physical location for backups, and it does not store the physical location of other file types.
I don't know the full architecture behind the maintenance cleanup task, but I have tried to get it to work with non SQL backup files and it did not.
I originially tried to use it to manage audit logs and certificate backups for TDE certificate backups but found that even though the job would run just fine, the files would not delete.
I wrote the above script to handle the audit logs, and then it was a simple change to get it to work for the certs as well.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 14, 2010 at 2:27 pm
thanks Bradley. It's not that I do not want to use your script. It's just that I've already wasted enough time on it and it since it is not that big of a deal and the size of the files are minimal (0 to 50kb) it's just easier to delete them once a year. I was mainly just curious as to why it didn't work. Seems like it would be a nice functionality since you can specify the extensions.
May 30, 2013 at 9:51 am
Please correct above script as below. I just added desc
insert into @myTable4
select FileNames, FileCreationDate
from @myTable3
order by FileCreationDate desc
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply