Warning
Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support Services for assistance.
Issue
TAC Vista SQL transaction log file is very large and won't shrink.
Product Line
TAC Vista
Environment
- Vista 5.1.X
- SQL server 2005, 2008
Cause
An SQL transaction log consists of a number of virtual files whose size is defined in the database's properties. Once the virtual file is full, the next virtual file in sequence is used. These files are an essential part of the 'rollback' functionality, allowing SQL to roll back a transaction if for some reason (i.e. disk space runs out) it can't be completed. With the simple model, the virtual file will be marked as reusable once the transaction(s) contained within the file is complete. So a database may look like this, with the '2' in the status column indicating that the virtual file is still in use:
With the recovery model set to a simple, a shrink operation will remove all the virtual files that aren't in use.
With the full or bulk-logged recovery model shrink operations behave differently. A full description of this can be found here, but the most important point is that a backup of the transaction log must be made before any virtual files can be marked as reusable.
A shrink operation may not remove all virtual files straight away either so multiple shrinks may be necessary to remove all unused/expired
Resolution
Run the following queries:
DBCC loginfo
This will show the file structure of the SQL log file.
BACKUP LOG taclogdata TO DISK = 'c:\taclogdata.bak'
Run DBCC loginfo again. Check to see if there is any change.
Run a 'Shrink' command (via SQL Management Studio) and then run DBCCloginfo again. Check the size of the database: sp_helpdb taclogdata
Keep repeating the process until the database logfile is as small as it can be.
