June 6, 2008 at 6:11 am
Hey all,
In order to simplify things, I want to keep all my TLOG backups in one file, per database. While this is not a hard task, cleaning up the file is. I found that both native and litespeed support a WITH RETAINDAYS option. I have this set to 1, but after viewing the backup file, it appears that is not purging any old backups. I don't want to use maintenance plans or any external process (c#, vbscript, etc.) to be able to clear files from the file system based on date, and I don't want to have to shell out from SQL (cmdshell) or even use CLR. Can I accomplish this?
BACKUP LOG @DBName
TO DISK = @BUName
WITH RETAINDAYS = 1
,STATS = 10
EXEC master.dbo.xp_backup_log
@Database=@DBName
,@Filename=@BUName
,@Threads=2
,@CompressionLevel=2
,@With='RETAINDAYS = 1'
,@With='STATS = 10'
What am I doing wrong here?
Thanks
June 6, 2008 at 6:23 am
Well, an easy way out would be to run a TLOG backup with INIT after my daily full ... but it seems like there should another way to accomplish this?
June 6, 2008 at 6:39 am
So specifying with INIT overwrites the entire file ... I thought it would only purge anything that fell outside of the RETAINDAYS ... hmm, I'm missing something obvious here.
I've never liked maintenance plans, but it appears that both 2000 and 2005 have the ability to remove files after X days? If they can do it, what is the command to do so? xp_delete_file? That wouldn't help much with want to remove backup files inside a backup. If I switched my TLOGS to include something like MMDDHHMM and kept them separate, I still wouldn't be able to have SQL cleanup old files.
Is what I'm attempting to do possible? I just want to have a rolling 24 hours of TLOGS stored inside a file per database using nothing more than TSQL. I know I can easily do this with vbscript or c#, but as I said, I really want to keep this in SQL.
Thanks!
June 6, 2008 at 9:59 am
Well ... I'm not to thrilled about it, but I changed it from one file per database to separate files based on YYYY_MM_DD_HHMM. Then when my daily backups come in, I use xp_delete_file to delete older than 2 days. This sucks and I don't like it ...
If there is an alternative method, I'm all ears.
Thanks
June 9, 2008 at 8:06 am
RETAINDAYS is a bit of a misnomer (IMO)... It ony prevents backups in a file from being deleted, yet there is no way to purge individual backups from a single file. The only way to purge a backup file containing multiple backups is to INIT it (which wipes the entire file).
It's also not reccomended (from my experiences) to backup a large number of t-logs to a single file. When the t-log backup starts, it queries the device itself (RESTORE HEADERONLY, IIRC) and that can be an expensive operation if there are a lot of backups appeneded. This 'lot of backups appended' number will vary system to system....
Your friendly High-Tech Janitor... 🙂
June 9, 2008 at 8:12 am
Yeah after watching it over the weekend I decided not to go with a single file for this particular reason, that and the times in which we do our file system backups.
For now, I ended up simply adding a second ActiveX (VBScript) step into the daily backup jobs to clean up the tlogs based on date. Not happy with it, but it works for now ...
June 9, 2008 at 1:02 pm
If you're interested, I have a backup stored procedure that deletes old backup files after a successfull backup.
http://ola.hallengren.com/sql-server-backup.html
Ola Hallengren
June 10, 2008 at 1:33 am
I've used the same method of dropping TLogs to the file system and having a script maintain the folders to keep a rolling 5 days of logs.
Three things I've discovered that are easy to overlook are:
A days logs aren't too bad, even when dumping every minute, but many days at very frequent intervals causes Windows Explorer to die when querying the directory. If possible have your backup script create seperate directories for each day or so.
Daylight savings (or whatever you call shoving your clock time forward and back where you are) is a PITA, so its best to use UTC time and record the modifier in the file name eg. DBNAME_YYYYMMDD_UTCMODMINUTES.LOG (and yes I use the +/- in the file name too). That way if you dont have a smart system admin with a time stepper your logs don't get broken once a year.
Transaction Logs can take more space than the activity on the DB indicates, so you need to size disk correctly (and monitor well).
June 10, 2008 at 10:16 am
Nick, are you refering to using xp_delete_file?
How many files did you have in a directory and how long time did it take to do the delete?
Ola Hallengren
June 10, 2008 at 5:47 pm
No, I was referring to using Windows Explorer to view the filesystem, and if you view the folder where your dumping logs to, if you have 20,000 odd files in there (from dumping logs every minute for 20 days) then Explorer tends to have some difficulty and makes your server all slow. So if your going to get 1440 files a day, then its nicer to split them by day.
Then you can just delete the folder, and be done with it.
I've never used xp_delete_file, I tend to use xp_cmdshell or an activex script for this maintenance. We try to stay away from the undocumented procedures as MS had a big whinge about the use of them the last time we were audited, and it was put on an official report which went to people with bigger sticks than me.
And performing a delete with anything other than Windows Explorer tends to be relatively quick. 15,000 files takes a few seconds at most.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply