The transaction log file

The transaction log file contains information that allows Sybase IQ to recover from a system failure. The transaction log is also required for auditing. The default file name extension for this file is .log.

Sybase IQ records in the transaction log the information necessary to recover from a system failure. Although the information logged is small for each committed transaction, the transaction log continues to grow in size. In systems with a high number of transactions that change data, over a period of time the log can grow to be very large.

Log truncation generally requires the Sybase IQ servers involved to be taken off line. When to truncate the log is really up to the DBA responsible for supporting the Sybase IQ systems, and depends on the growth profile of the log file and the operational procedures at the site. The log truncation procedure should be scheduled at least once a month or more frequently if the log file is exceeding 100MB.

Use the –m server start-up switch to truncate the transaction log of a non-multiplex database. Note that leaving the –m server start-up switch permanently set is not recommended. This switch should only be used to start Sybase IQ for a transaction log truncation. How this is done is up to the DBA, but the following procedure provides a suggestion.

1. Create a copy of the server switches .cfg file with a name identifying the file as the log truncation configuration setting and edit this copy of the file to add the –m switch.

2. Perform normal full backup procedures, including making copies of the .db and .log files.

3. Shut down Sybase IQ. Verify that ‘CloseDatabase’ was written in the iq.msg file.

4. Restart Sybase IQ with the configuration file containing the –m option. Note that no user access or transactions should be allowed at this time.

5. Shut down Sybase IQ and restart using the configuration file without the –m option set.

1. Back up the database from the write server, if you have not already done so.

2. Set the DELETE_OLD_LOGS option on the write server:

   SET OPTION Public.Delete_Old_Logs=’On’
   

3. Stop the write server’s dbremote and restart it with the -x command line switch. (Create a special version of the start_dbremote.bat script, found in the write server’s database directory, to do this.) This truncates the log at the write server. For example:

   cd \Server01\mpxdb\cmd /c 
   start dbremote -q -v -x -o
   "d:\Server01\mpxdb\dbremote.log" -c
   "uid=DBA;pwd=SQL;eng=Server01;dbf=
   d:\Server01\mpxdb\mpxdb;
   links=tcpip{port=1704;host=FIONA-PC}" 
   

4. Clear the DELETE_OLD_LOGS option on the write server:

   SET OPTION Public.Delete_Old_Logs=’Off’