Database Backup -- Renaming Transaction Log

[Former Member]

We use ASA 11.0.1 build 2044.

I recently split up our database file and log by moving the log to the C: drive and keeping the database on the 😧 drive -- something that is recommended in the documentation. The database is 15GB and growing and the log can get pretty big over the course of a day (sometimes over a gig).

After doing this, I noticed that we are getting renamed log files (i.e. 100324AA.log) in the folder on the C where the main log file now resides after backups.

BACKUP DATABASE DIRECTORY 'D:\\Db\\DailyBackup' WAIT BEFORE START TRANSACTION LOG RENAME;

This wasn't happening before. The DailyBackup folder contained the backup of the db and log and the folder where the db resided had the live db and log -- no renamed log files in the format shown above. The problem is that if these renamed log files keep getting added we would have a space issue on the C: drive. What is the best practice for managing this growth of these log files? I could write a batch file that deletes these renamed log files periodically. Is there a better alternative? Thanks, Tom

[VolkerBarth]

AFAIK, the behaviour is fully expected, as you explicetly ask for a log rename. The question is whether you need the renamed log files (e.g. because they are critical for a synchronization/replication setup). If not, you may just use TRANSACTION LOG TRUNCATE instead if space is an issue.

[Breck_Carter]

Are you positive about the following? (1) you previously ran exactly the same BACKUP command, and (2) you did not see any yymmddxx.log files appearing in the folder where the active log resides. I just tested both scenarios (db and log on same and different drives) using 11.0.1.2276, and got the expected behavior: the yymmddxx.log files appear in the same folder as the active log.

[Breck_Carter]

FWIW, an alternative approach to keeping a manageable number of backup copies is here: http://sqlanywhere.blogspot.com/2009/03/rotating-database-backups-revisited.html

[Former Member]

You were both right. I put the log file and db back together again on the staging server and confirmed the creation of the renamed log files. I then found out why the renamed log files were missing on the production database server. We have a PB app that controls the upload process of client data into our site. Buried within there was a function to delete renamed transactions logs -- one of those undocumented gems I inherited. Anyway, thanks for your help and particularly for pointing me to the concept of rotating backups. This answered what would have been my next post: is it better to do backups in sybase central with an event or with batch files? Tom