Search This Blog

Tuesday, September 28, 2010

A simple VB script to retain Errorlogs worth 90 days (or as you like)

So far ,I heard retaining X number of errlogs which is widely used (So I am not writing that script here)...But one of our clients asked us to retain errlogs worth only 90 days .The client was not ready to recycle the errorlogs and wanted us to keep them to the default ...

Finally , we could come out with a simple VB script that can do it .The script code is mentioned below .

Code :

sFolder = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG"
iMaxAge = 90
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FolderExists(sFolder) Then
for each oFile in oFSO.GetFolder(sFolder).Files
If DateDiff("d", oFile.DateLastModified, Now) > iMaxAge and ( "ERRORLOG.1" or "ERRORLOG.2" or "ERRORLOG.3" or "ERRORLOG.4" or "ERRORLOG.5" or "ERRORLOG.6") Then
wscript.echo "Deleting" &oFile.Name
End If
End If

You will need to create a scheduled task/Or SQL Server job using xp_cmdshell, to run at a specific time .Once its kicked off , if any of the files mentioned in the code (Note : errorlog will not be tried upon) have a timestamp greater than 90 days from the day you are executing the file , It will delete those files ...for example if I have 7 files below :

File Timestamp
Errorlog 12/9/2009
Errorlog.1 12/8/2009
Errorlog.2 12/7/2009
Errorlog.3 12/6/2009
Errorlog.4 12/5/2009
Errorlog.5 12/4/2009
Errorlog.6 12/3/2009

The files deleted will be : Errorlog.3,Errorlog.4,Errorlog.5 and Errorlog.6

You need to change the Path of sFolder variable ..
Happy learning ...


No comments: