Unable to upgrade SQL log database.

[Product_Support]

Issue

Unable to upgrade SQL log database from a pre-5.1.7 database to the new Version 2 format that is used (for new installations and optional as upgrade) in 5.1.7. Get the error message:

Test of connection successful.
Connecting to TAC Vista SQL database.
Checking SQL server version and database size.
SQL Server Version: Microsoft SQL Server 2008 R2 (SP1) .
SQL Server Edition: Developer Edition (64-bit).
Data Allocated Space: XXXXXMB.
Data Used Space: XXXXMB.
Log Allocated Space: XXXMB.
Checking TAC Vista SQL database schema version.
Checking user access.
Upgrading TAC Vista SQL database from version 1.0.0.15 to version 2.0.0.0.
Failed to execute SQL upgrade script. Error : Error 9002, line 585: The transaction log for database 'taclogdata' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Environment

Vista 5.1.7 SQL log database - either upgrade when installing Vista or using the SQL Database upgrade tool.

Cause

There is insufficient space for the transaction log to grow during the upgrade process.

It is important to make sure that there is enough disk space available before upgrading the TAC Vista SQL database layout. During the upgrade procedure the database will increase its size with approximately 40%. If a SQL database is upgraded in a Microsoft SQL Server 2005 Express environment the database cannot exceed 2GB in size; the upgrade will not allow it. It is possible to upgrade to a Microsoft SQL Server 2008 Express environment where the database size limit for the upgrade is 5 GB. After the upgrade is completed, this additional storage space is not consumed by any data; the additional storage space will be allocated for future data entries in the TAC Vista SQL database. The actual data in the SQL database will decrease with approximately 30% after the conversion is completed. Do not shrink the SQL database size after a conversion; doing so will cause major fragmentation.

Resolution

Firstly, confirm that there is enough disk space (at least twice the size of the log database) for the database to grow during the upgrade.

Secondly, changes the file settings for the database to allow for the increase in size during upgrade

1. Open SQL Management Studio (download here if required) and locate the taclogdata database.
   1. Connect to TACVISTA (default) SQL instance.
   2. Expand the menu-tree and then expand the databases tree.
   3. Locate the taclogdata (default) database.
2. Right-click on the taclogdata database and select properties.
3. Go to the sub-property menu Files.
   
4. Change the autogrowth settings for the database log file.
   1. Go to the list of database files and identify the log file (file type Log).
   2. Click the button in the field Autogrowth.
   3. Check the size given in the Restricted file growth (MB) field.
   4. Either increase this to roughly twice the size of the database to be upgraded or
   5. Click the Unrestricted File Growth radio button.
5. Upgrade the database to Version 2 layout.