Transaction Logs Explained
The transaction log is a log of all changes to your database. SQL writes these changes, known as transactions, to your transaction log before writing them to the database. This lets you restore the sequence of changes to your database should there be a system crash or other problem. Often the transaction log is also used by developers to ensure that a series of related changes to a database take place together or not at all.
In the event of a system or disk crash you may use the transaction log to revert the database to its previous state. The database would be restored to the most recent full backup (made daily), then all the changes recorded in the transaction log since that backup would be applied.
Information About Our Transaction Log Configuration
If the log records were never deleted the log files would grow forever. The process of deleting transaction logs is known as truncating the log. Truncation occurs nightly when your database is backed up.
Starting June 18th 2004, all new databases are going to be configured to use the Simple Recovery Model. This means that the transaction log will be truncated after every commited transaction. This keeps the log very small, and prevents any 'transaction log is full' error messages. Transactions are still logged, so the database can recover in case of a unexpected server reboot, and other similar crashes. The downside is that if we experience any hard media crashes, we would have to revert to backups. Backups are done daily, so you may not be able to recover any of the daily transactions that occured that day.
If your database was created before June 18, 2004 and you continually find yourself running out of transaction log space you may wish to switch to simple recovery model. If so please contact us and we'll make the switch for you.
If your database was created after June 18th, 2004 and you would like to use transaction logs (full recovery mode) please contact us and we'll make the switch.
Clearing Your Transaction Log
When your transaction log is full you will receive an error every time you attempt to write to your database.
To clear your transaction log, connect to your database using a tool such as query analyser and run the following command:
DUMP TRANSACTION DATABASENAME WITH NO_LOG
Note: replace DATABASENAME with your database name such as SQL9AAA111
If this is a continual problem, it may be necessary to purchase additional SQL storage space or switch to simple recovery mode as detailed above.