You can maintain all of the information written to the message log file without allowing the file size to grow indefinitely by enabling message log archiving. A message log archive is a file in which the contents of the active .iqmsg message log file is saved.
Enable message log archiving by setting the IQMsgMaxSize server property using the sa_server_option system stored procedure or the -iqmsgsz server startup switch to a value greater than zero, and setting the IQMsgNumFiles server property or the -iqmsgnum server startup switch to the number of message log archives. The value of the server property takes precedence over the value of its corresponding server switch.
The names of the .iqmsg message log archives follow the pattern logname.iqmsg.n, where n is an integer greater than zero and logname is the name of the message path as specified in the CREATE DATABASE statement or is the database name with the suffix .iqmsg. The archives are created as read-only files in the same directory as the message log file.
When the message log file logname.iqmsg is full and the number of message log archives is fewer than the number specified in IQMsgNumFiles or -iqmsgnum, the server renames the current message log to create a new archive. A new message log is created with the name logname.iqmsg.
For example, if -iqmsgnum is equal to 5, the message log archives are created in the following order: logname.iqmsg.1, logname.iqmsg.2, logname.iqmsg.3, logname.iqmsg.4, and logname.iqmsg.5.
When the message log file logname.iqmsg is full and the maximum number of message log archives already exists, the oldest archive (with file extension .1) is deleted before the current message log is archived.
For example, when -iqmsgnum is 5 and logname.iqmsg.5 exists:
The file logname.iqmsg.1 is deleted.
The files logname.iqmsg.2 to logname.iqmsg.5 are renamed to logname.iqmsg.1 to logname.iqmsg.4, respectively.
The active message log file is renamed to logname.iqmsg.5.
A new message log file logname.iqmsg is created.
Using this method, the server always keeps the most recent message logs, when the value of IQMsgNumFiles (or -iqmsgnum) is greater than zero.
Message logging stops if the disk becomes full during message
logging. The error "Disk Full!!! Message logging
stopped." is written in the server log.
Message logging stops if the following errors occur during message log management:
File rename error: the server cannot archive the current active log or cannot rename any existing log archive
File creation error: the server cannot create the new message log
File deletion error: the server cannot delete the old archive
Disk I/O error
The server automatically resumes message logging when the error condition is resolved. A message is written to the server log when message logging stops and when logging resumes.
For example, if renaming the file fails, the message "Renaming
of <filename> failed. Message log could not be archived. Message
logging stopped." is written in the server
log. When logging resumes, the message "Message
logging resumed." is written in the server
log.
If the server fails to delete or rename a file because the file does not exist, the error is ignored and the log management process continues.
