Difference between revisions of "IQ truncate transaction log file"
(→With the dbdbackup utility (no downtime)) |
|||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
This page describes how you can truncate the transaction log file in Sybase IQ (so not for ASE !). The procedure is only described for a simplex environment. | This page describes how you can truncate the transaction log file in Sybase IQ (so not for ASE !). The procedure is only described for a simplex environment. | ||
| − | According to the Sybase documentation you should truncate the IQ transaction log file once a month or when it's size is more than 100 Mb. | + | According to the Sybase documentation you should truncate the IQ transaction log file once a month or when it's size is more than 100 Mb. |
| + | |||
| + | There are a couple of methods available to do this task and two are described here. | ||
| + | |||
| + | ===Adding -m to the .cfg file (with IQ downtime)=== | ||
| + | This method requires some down-time, although it's just a few minutes. | ||
Find the location of the transaction log file by looking in the server log file. An example of the name of that file is "IQ1.025.srvlog". Look for the text "Transaction log:". | Find the location of the transaction log file by looking in the server log file. An example of the name of that file is "IQ1.025.srvlog". Look for the text "Transaction log:". | ||
| Line 32: | Line 37: | ||
Restart IQ. A new transaction log file is automatically created. | Restart IQ. A new transaction log file is automatically created. | ||
| + | ===With the dbdbackup utility (no downtime)=== | ||
| + | First create a directory where you would like to keep the transaction log dumpfiles. | ||
| + | mkdir /var/dbbackups/IQ1 | ||
| + | Run the dbbackup utility as in the following example | ||
| + | dbbackup -c "uid=<username>;pwd=<password>;eng=<engine name>;dbn=<database name>" -x dump -t | ||
| + | Adaptive Server Anywhere Backup Utility Version 9.0.2.2041 | ||
| + | (2 of 2 pages, 100% complete) | ||
| + | Transaction log truncated | ||
| + | Transaction mirror log truncated | ||
| + | Database backup completed | ||
| + | The next invocation of the dbbackup utility will ask if you would like to replace the previous backup, so it makes sense to rename the backup files and add a timestamp to it. | ||
| + | |||
| + | You can also use -xo in stead of -x to truncate the transaction log, without making a backup. | ||
| + | |||
| + | '''WARNING''': This method should not be used on IQ version 12.5, it may lead to a situation that no checkpoints can be done on the database. You will get the message "checkpoint already in progress". | ||
| + | |||
| + | |||
Note: Sybase recommends that you keep a copy of the old transaction log file around for diagnostic purposes. | Note: Sybase recommends that you keep a copy of the old transaction log file around for diagnostic purposes. | ||
[[Category:IQ]] | [[Category:IQ]] | ||
Latest revision as of 11:45, 30 January 2010
This page describes how you can truncate the transaction log file in Sybase IQ (so not for ASE !). The procedure is only described for a simplex environment.
According to the Sybase documentation you should truncate the IQ transaction log file once a month or when it's size is more than 100 Mb.
There are a couple of methods available to do this task and two are described here.
Adding -m to the .cfg file (with IQ downtime)
This method requires some down-time, although it's just a few minutes.
Find the location of the transaction log file by looking in the server log file. An example of the name of that file is "IQ1.025.srvlog". Look for the text "Transaction log:".
(The location of the transaction log is specified with the "create database" command.)
Go to the directory where the cfg file for IQ is located, for example:
cd /opt/sybase/admin/IQ1
Shutdown IQ
stop_asiq
Modify the configuration file for the IQ server (example: IQ1.cfg) and add the -m flag to it. You should put that flag somewhere in the top of the file, not at the end.
-n IQ1 -m -c 48M -s none <cut>
Start IQ as you normally do
start_asiq @IQ1.cfg IQ1.db
Then shut it down again.
stop_asiq
You will now notice that the transaction log file no longer exists. Modify the cfg file and remove the -m flag.
Restart IQ. A new transaction log file is automatically created.
With the dbdbackup utility (no downtime)
First create a directory where you would like to keep the transaction log dumpfiles.
mkdir /var/dbbackups/IQ1
Run the dbbackup utility as in the following example
dbbackup -c "uid=<username>;pwd=<password>;eng=<engine name>;dbn=<database name>" -x dump -t Adaptive Server Anywhere Backup Utility Version 9.0.2.2041 (2 of 2 pages, 100% complete) Transaction log truncated Transaction mirror log truncated Database backup completed
The next invocation of the dbbackup utility will ask if you would like to replace the previous backup, so it makes sense to rename the backup files and add a timestamp to it.
You can also use -xo in stead of -x to truncate the transaction log, without making a backup.
WARNING: This method should not be used on IQ version 12.5, it may lead to a situation that no checkpoints can be done on the database. You will get the message "checkpoint already in progress".
Note: Sybase recommends that you keep a copy of the old transaction log file around for diagnostic purposes.
