People Inc. FAQ

Managing SQL Server Transaction Logs

This FAQ describes the management of the size of the SQL Server transaction logs associated with People Inc. databases. In a default installation of SQL Server (and People Inc.) these log files may expand to an unacceptable size. The expansion of the transaction logs can make it difficult to back data up and in the most serious scenario, make the associated databases unusable.

In SQL Server 2005 (and SQL Server 2000) each database contains a data file and a transaction log file. SQL Server stores the data physically in the data file. The transaction log file stores the details of all the modifications that are performed on the database and the details of the transactions that performed each modification. This transaction-logging cannot be turned off in SQL Server.

SQL Server databases can be configured such that, as the volume of transactions grows, the size of the transaction log files increases automatically. In this situation, it is possible that the transaction log file may become very large and in extreme situations, the server may run out of disk space as a result. If this happens, SQL Server will no longer be able to add to the transaction log and as a consequence, it will no longer be possible to make any changes to the database. Additionally, SQL Server may mark your database as suspect because of the lack of space for the transaction log expansion.

Reducing the size of SQL Server transaction logs

To recover from a situation where SQL Server Transaction Logs have grown to an unacceptable limit, the IT administrator must first truncate any inactive transactions and then shrink the transaction log file. Note that the transaction logs are designed to maintain the transactional integrity of the database and must not be deleted.

When the transaction logs grow to an unacceptable limit, the transaction log should be backed up (immediately). While the backup of the transaction log files is created, SQL Server automatically truncates the inactive part of the SQL Server Transaction Logs (this contains details of completed transactions). SQL Server can then reuse this space in the transaction log.

Shrinking the SQL Server Transaction Log file

Backing up the databases does not reduce the log file size. To reduce the size of the transaction log file, the IT administrator must shrink the transaction log file. To shrink a transaction log file, select the option on the context menu in SQL Server Management Studio.

Managing SQL Server Transaction Logs

Preventing the SQL Server transaction logs file from growing unexpectedly

To prevent the SQL Server transaction logs file from growing unexpectedly change the recovery model of each of the databases to Simple.

By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups.

By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database.

You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation. To do this, right-click on the database entry in SQL Server Management Studio and select Properties.

SQLLogs2

Alternatively, it is possible to prevent the transaction log files from growing unexpectedly by:

  • Setting the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
  • Backing up the transaction log files regularly to delete the inactive transactions in the transaction log.

If you require any additional information please contact AgathonHR by phone 01242 663974

Or by emailing helpdesk@agathonhr.co.uk

Very little maintenance will needed to be done by your IT Team but our help desk is at hand to give advice about technical issues relating to 3rd part applications used with People Inc.

agathonhr

  • Implementation Difficulty 85%
  • Benefit 85%
  • Set up Time/Costs 45%
  • Ease of use 10%

New Feature Rating

Our ratings show how easy it is to implement this new feature and how the benefits outweigh the time and costs for implementing it.

Note: Features with a Difficulty rating of over 50% will need to be set up by one of our developers.

New Feature Rating

Our ratings show how easy it is to implement this new feature and how the benefits outweigh the time and costs for implementing it.

Note: Features with a Difficulty rating of over 50% will need to be set up by one of our developers.