My solution stores graphic files (not yet very big but growing). The sybase .db file is currently >574MB and also growing. The .log file however is >797MB and growing fast. Can I safely delete it every now and again and will it be recreated? If so, will it simply pick up (size wise) where it left off?
How does one avoid the .log file growing and growing?
Thanks
Bevil
Hi Bevil
I frequently rename Log Files and move to Backup storage - Sybase starts a new one automatically. Think I read somewhere that very big log files slow the DB startup.
Graham Greensall
Worxinfo Ltd
Make sure that your database is stopped before you remove logfiles.
Thanks Jan and Graham
Graham, what is the purpose of storing and backing them up? Assuming something goes wrong with your solution, what would having the log files achieve? (only asking because I think I might be missing something and don’t wanna get caught short…)
Apart from being able to look up old database events (I guess to prove someone did something at a certain time) how do they help is my question..
Thanks
Bevil
Belt & braces instinct
Figure that if things go badly wrong it’s better to have backups in multiple places and multiple formats. Of course I’m not clever enough to know how to stitch tgether the old log files but I’m sure someone out there is.
Graham Greensall
Worxinfo Ltd
After realizing that our log files were becoming very large I pulled out our Sybase manuals and tried to determine the best way to reduce their file size. Page 459 of the SQL Studio - Adaptive Server Anywhere Administration Guide explains how to “Backup a database using the dbbackup command-line utility”. In this section of the manual I noticed that the -x switch can be used to delete and restart the transaction log, so I changed our backup command to:
cd C:\PROGRA~1\Servoy\sybase~1\win32\
C:\PROGRA~1\Servoy\sybase~1\win32\dbbackup.exe -y -c "uid=dba;pwd=sql;dbn=contacts" -x C:\Backups\01
copy "C:\PROGRA~1\Servoy\servoy.properties" C:\Backups\01
(to keep things simple I am just showing the backup command for one database named ‘contacts’)
The above example backs up database files named contacts.db and contacts.log to the c:\backups\01 folder, deletes the original contacts.log file in the database folder and restarts a new log file.
To make the above work, I placed a win32 folder inside our sybase_db folder. The win32 folder contains the following files:
dbbackup.exe (100 KB)
dblgen9.dll (572 KB)
dblib9.dll (440 KB)
dbsvc.exe (112 KB)
dbtool9.dll (1,052 KB)
Everything seems like it is working perfectly, however, when I compare file sizes of the backed up files in the Backups folder for the last four days they are as follows:
12/09/06 contacts.db 10,834 KB
12/09/06 contacts.log 22 KB
12/10/06 contacts.db 10,834 KB
12/10/06 contacts.log 24 KB
12/11/06 contacts.db 11,098 KB
12/11/06 contacts.log 106 KB
12/12/06 contacts.db 11,098 KB
12/12/06 contacts.log 66 KB
The 9th and 10th were over the weekend, so I can understand why there was little change during that period, however, why would the log file be smaller for December 12th without making the contacts.db file bigger? During that time more data was entered than was deleted.
When exactly is data moved over from the log file to the db file? Is there some kind of optimization that takes place that would explain the described changes in file size? Could it just be an inaccurate reading by the operating system? We are using Windows Server 2003.
The manual talks about log files being important because they keep track of checkpoints, so that data can be restored back to a particular point in time. If someone can explain a little more about the actual mechanics of when and how the log and db files interact it would be greatly appreciated. I have not been able to locate this information in the manuals.
Dean
grahamg:
Belt & braces instinctFigure that if things go badly wrong it’s better to have backups in multiple places and multiple formats. Of course I’m not clever enough to know how to stitch tgether the old log files but I’m sure someone out there is.
Graham Greensall
Worxinfo Ltd
Watch out! the log files can be very important!
A while ago, we had a customer, that had deleted 1000 records!
But they called us 4 days later. And in that 4 day’s all kind of transactions where made.
So with Sybase Central, we translated the log file, (which was there at the beginning) and translated the file to a SQL file.
We searched for the delete statement, uncomment that one, and builded a complete new DB, WITH all the transactions, so ALL data was restored, even the data that was changed afterwards.
We did that in about 20 minutes!
I think THAT’S COOL!
Thumbs up for Sybase!
So Westy, every action or transaction that is done is logged into the log-file, that’s way the logfile is almost everytime bigger than the DB file. With Sybase Central you can translote the log-file to a readable SQL file.
Hope that helps
Well done Harjo
I guessed someone out there would be clevererer than me and know how to do it
Graham Greensall
Worxinfo Ltd
Just read the manual!
So Westy, every action or transaction that is done is logged into the log-file, that’s way the logfile is almost everytime bigger than the DB file. With Sybase Central you can translote the log-file to a readable SQL file.
As described above, we have been using the -x to copy the log file to a backups folder every day and delete the original, so I believe this means we have a backup of everything. Can the individual log files be stitched back together again if needed?
Dean
The transaction log is a key element in back-up and recovery scenarios. It stores a list of all the queries executed against the database file. In the event of a failure of some sort, the queries stored in transaction logs can be applied against an earlier copy of the database, ensuring you can recover your database right up to the time of failure.
You can manually back up the transaction log files by doing a simple file copy. You can also back up the transaction logs by using the dbbackup command line utility. Lastly, you can execute SQL statements to back up the transaction log. A sample SQL statement would be:
BACKUP DATABASE
DIRECTORY ‘d:\temp\dir_to_backup_to’
TRANSACTION LOG RENAME
The transaction log will automatically be moved to the backup directory specified and renamed to YYMMDDnn.log where YY is the year, MM the month, DD the day, and nn a unique identifier.
You can also put that SQL statement into a scheduled event so that the back-up of the transaction log happens, say, every night or when the database has been idle for a log time.
During recovery you can recover from multiple transaction log files using the transaction log utility, dblog.exe.
Lastly, you should make sure that you back up the database file on a regular basis as well and not just the log files.
Hope that helps!
Hi there,
I have accidentally deleted the log file for one of my sybase db.
Now I could not connect to the database. When I try to connect to that database through the sybase central I get the following error message:
[Sybase][ODBC Driver][Adaptive Server Anywhere]Unable to start specified database: abcd.log: transaction log file not found
SQLCODE: -82
SQLSTATE: 08001
Is there any possibility to get the log file back or able to connect to the database without the log file? Actually I do not need the log file.
Your help would be greatly appreciated.
You probably deleted it with a running db?
As far as I know, with this message, you need the log file to get up and running again. Don’t you have a backup of the log file?
BTW I also said I did not need a log file until I forgot to backup and could use the log file to restore some of the stuff that was deleted from the db.
You can however force it to create a new log file.
I know I have done this before (a couple of years ago) but I would have to look it up in the manuals again.
So in short, it’s fixable.
Hope this helps.
I did a quick search (well I did dbeng9 -n in the commandline) and I believe it’s the following syntax:
dbeng9 -f databasename
This will force the database to start without transaction log. Just close the process again and launch it like you normally would do and you are set.
Hope this helps.
Hi Robert,
Thanks a lot. Your method worked perfectly. I was actually spending half a day searching the web for an answer.
The db is a simple db which has my personal data only. But still I think I need to keep the log files. It would be better I can keep the log files in chunks instead of a huge file. MySql does that automatically. I am sure there is a way with sybase as well.
Anyway thank you all for your help to recover my db. Fantastic!
Hi Hameed,
You can use the backup procedure of Sybase Central. Dean Westover made a nice step by step manual for that.
The iAnywhere online reference also have more (background) info on this.
Anyway, from what I read is that after the backup you can truncate (i.e. empty) the logfile so that in fact you have chunks of logs instead of one large one.
Hope this helps.