delete all old translog every day

Question

Post reply

delete all old translog every day

sqlstar2011

SSCertifiable

Points: 6000
More actions
May 24, 2007 at 9:58 am

#69817

I would like to delete all old trans log on regular basis. Right now I am putting each of them in diff directories. I used following solution earlier:
http://www.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp
which is useful to delete files from a directory and not sub folders. I would like it to delete everything older than a specific time period including parent folder and sub folders.
any thoughts how to accomplish that?
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Yep... do a backup.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EdVassie SSC Guru Points: 60472 More actions · Answer 2

If you are using vbscript to generate your file deletes, then Google for information about the FilesystemObject. This has properties that can give you the creation date of each file, and from this you can decide if you want to delete it.

If you are using T-SQL to generate file deletes, then you can use INSERT INTO #table EXEC xp_cmdshell "DIR ..." to get details of the files in your backup folder into a table. You can then look at each file with a cursor and decide if the creation date meets your criteria for deletion.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

http://www.sqlservercentral.com/columnists/sjones/pushftp.asp

loach Right there with Babe Points: 796 More actions · Answer 4

Here's one I've used for a while. Don't remember where I got it.

' Objective: To delete old files from a given folder and all subfolders below

' Created by: MAK

' Created Date: June 21, 2005

' Usage: cscript deloldfiles.vbs c:\dba\log 3 BAK

' : It deletes files older than 3 days with a BAK extension

Set objArgs = WScript.Arguments

FolderName =objArgs(0)

Days=objArgs(1)

Extension=ObjArgs(2)

set fso = createobject("scripting.filesystemobject")

set folders = fso.getfolder(FolderName)

datetoday = now()

newdate = dateadd("d", Days*-1, datetoday)

wscript.echo "Today:" & now()

wscript.echo "Started deleting files older than :" & newdate

wscript.echo "________________________________________________"

wscript.echo ""

recurse folders

wscript.echo ""

wscript.echo "Completed deleting files older than :" & newdate

wscript.echo "________________________________________________"

sub recurse( byref folders)

set subfolders = folders.subfolders

set files = folders.files

wscript.echo ""

wscript.echo "Deleting Files under the Folder:" & folders.path

wscript.echo "__________________________________________________________________________"

for each file in files

if file.datelastmodified < newdate AND right(file.name, len(Extension)+1) = "." & Extension then

wscript.echo "Deleting " & folders.path & "\" & file.name & " last modified: " & file.datelastmodified

on error resume next

file.delete

end if

recurse folder

set files = nothing

end sub