April 1, 2009 at 1:02 pm
I followed article: =http://www.sqlservercentral.com/scripts/archive/65209
I created a job to run the stored proc:
exec deleteOldFiles @basedir = 'c:\sql', @days_to_age = 0, @extension = 'bak', @checkarchives = 1
When the job runs the history returns:
Message
Executed as user: NT AUTHORITY\SYSTEM. deleting c:\sql\e1.bak ... [SQLSTATE 01000] (Message 0) deleting c:\sql\e2.bak ... [SQLSTATE 01000] (Message 0) deleting c:\sql\e3.bak ... [SQLSTATE 01000] (Message 0) deleting c:\sql\e4.bak ... [SQLSTATE 01000] (Message 0) deleting c:\sql\e5.bak ... [SQLSTATE 01000] (Message 0). The step succeeded.
However the files remain?
Any of you guys got a script working to delete .bak files?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
April 1, 2009 at 1:38 pm
Where that proc runs xp_cmdshell, remove the no_output clause. That might give you some data on what the problem is.
In SQL 2005, you'll be better of using a CLR proc to do this kind of thing than using xp_cmdshell.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2009 at 1:55 pm
Thanks for posting. No joy after removing the clause.
I will google for a script using CLR.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
April 1, 2009 at 4:34 pm
Try:
Create procedure USP_DelOldFiles @path varchar(25),@duration int
as
--Objective: To delete files older than certain period from a folder
--Usage example:
--Exec USP_DelOldFiles 'c:\test',30 -- which deletes files older than todaydate-30
--Created by :MAK
--Created date: Jan 7,2003
--OS: windows 2000
declare @myquery varchar(1000)
declare @query varchar(1000)
declare @name varchar(100)
set @myquery = "exec master.dbo.xp_cmdshell 'dir "+ ltrim(rtrim(@path)) + "\*.* /a/od'"
print @query
create table #Filenames (id int identity(1,1) ,name varchar(100))
insert #Filenames(name)
exec (@Myquery)
delete from #Filenames where substring(name,3,1) <> '/' or name is null or
substring(name,25,1) ='<'
Declare mycursor cursor for
select name from #Filenames where
convert(datetime,left(name,10)) <= getdate()-@duration
open mycursor
fetch next from mycursor into @name
while (@@fetch_status =0)
begin
set @query = 'exec master.dbo.xp_cmdshell "del '+@path+'\'+ ltrim(rtrim(substring(@name,40,59)))+'"'
--print @query
exec (@query)
fetch next from mycursor into @name
end
close mycursor
deallocate mycursor
drop table #Filenames
MJ
April 4, 2009 at 3:07 am
Hi, I ran that but received:
Msg 207, Level 16, State 1, Procedure USP_DelOldFiles, Line 12
Invalid column name 'exec master.dbo.xp_cmdshell 'dir '.
Msg 207, Level 16, State 1, Procedure USP_DelOldFiles, Line 12
Invalid column name '\*.* /a/od''.
I came across a vb script that deletes files. I was going to run as a scheduled task from the server on which the files are copied to. Script as follows:
Option Explicit
Const strSrcPath = "C:\test" ' subdirectory to clean
Const intMaxDate = 30 ' change days to keep here
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
TraverseFolder strSrcPath, intMaxDate
Sub TraverseFolder(strSrcPath, intMaxDate)
Dim objCurrentFolder
Set objCurrentFolder = objFSO.GetFolder(strSrcPath)
On Error Resume Next
Dim objFile, objFolder
For Each objFile In objCurrentFolder.Files
If DateDiff("d", CDate(objFile.DateLastModified), Now) > intMaxDate Then
objFSO.DeleteFile objFile
End If
Next
For Each objFolder In objCurrentFolder.subFolders
TraverseFolder objFolder, intMaxDate
Next
End Sub
Although the above script deletes as required it would be better if it logged what had been deleted (for audit purposes).
Can any of you guys advise?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply