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.
Message log archiving is enabled 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 a positive 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 less than the number specified in IQMsgNumFiles or -iqmsgnum, the server renames the current message log to create a new archive. Then 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.
