Created On:  31 March 2011

Problem:

StarTeam is reporting "The transaction log for database 'XXXXX' is full" whenever trying to log on or when trying to perform actions in the CPC.

The StarTeam CPC / Server will usually show the following error

transaction log is full image

There will be corresponding errors in the StarTeam Server log similar to the following:

38 00000004 2010-06-23 14:15:56 Exception thrown from Unknown: Class = SA_DBMS_API_Error, Code = 9002(0x0000232a)
Msg = The transaction log for database 'XXXXXXXXX' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Resolution:

This occurs on SQL Server databases when the database transaction log(s) are full and can no longer grow to accommodate the database transaction information. No further database activity is allowed until either more space is allocated for the transaction logs, or the transaction logs are backed-up and / or shrunk in size. Some options to deal with a full transaction log include:
  • Backing up the transaction log
  • Freeing disk space so that the log can automatically grow
  • Moving the log file to a disk drive with sufficient space
  • Increasing the size of one or more log files
  • Adding a log file on a different disk

The first option – backing up the transaction log, should be the first attempt as this will usually solve the problem immediately but in some cases may only gain some time before it occurs again. However, this will give you time to research and implement one of the other options.

How to back up the Transaction Log:

  1. Using SQL Server Management Studio, connect to the SQL Server instance that stores the StarTeam database. In Object Explorer, click the server name to expand the server tree. 
  2. Expand Databases and right-click the StarTeam database. Select ‘Tasks | Back Up’. The “Back Up Database” dialog box appears. (see below) 
  3. In the Database list box, verify the database name. 
  4. Verify that the recovery model is either FULL or BULK_LOGGED. (if recovery model was ‘SIMPLE’ it is unlikely that backing up transaction log will recover / reduce log size). 
  5. In the Backup type list box, select Transaction Log *important*
     
    DB backup general options
  6. Choose the type of backup destination (normally ‘Disk’ will suffice), and if required modify the destination path & file name of the backup file. (Suggestion to us a .trn extension rather than .bak to distinguish between the backup types).
  7. To view or select the advanced options, click “Options” in the Select a page pane.

    DB backup options images

  8. Ensure that 'Truncate the transaction log' is selected. For the other options it is suggested that 'Overwrite all existing backups sets' (backup sets can get rather large over time if using the append option) & ‘Verify backup when finished’ (this will increase the time taken for the backup) are both selected.
  9. Click 'OK' and the transaction log backup will proceed. Once the Transaction Log backup is complete, you may not notice a significant reduction in the size of the log transaction files. This is because although the logs have been backed up the physical disk space the transaction logs occupied is still allocated.

These files should now be shrunk by the following command;

See below for information regarding 'Tasks | Shrink | Files' through the Management Studio, although running the command may be more convenient.

SHRINKFILE translog size WITH NO_INFOMSGS
-- 1st param: Logical name of log file;
-- 2nd param: preferred size (in Mb) to try to shrink log file to

For example, assuming you have 3 transaction log files and you wish to try and shrink (after backing up) the physical files to 5Mb each;

DBCC SHRINKFILE (STARTEAM_log_01, 5) WITH NO_INFOMSGS
DBCC SHRINKFILE (STARTEAM_log_02, 5) WITH NO_INFOMSGS
DBCC SHRINKFILE (STARTEAM_log_03, 5) WITH NO_INFOMSGS

Alternatively, you can attempt to shrink each file through the SQL Server Management Studio as shown below. Ensure the ‘File type’ is ‘Log’, and you have selected ‘Reorganize pages before releasing unused space’. Also enter the requested minimum size to attempt to shrink to.

Please note you may have to repeat this task for each log file listed in the ‘File name’ drop-down.

DB shrink general image