When attempting to upgrade the HIFIS database in our test environment using the database upgrade script for HIFIS version 4.0.60.2, we ran out of disk space.

This did not occur when upgrading our test environment to any version of HIFIS prior to 4.0.60.2.

To see what was causing this, I ran the database upgrade script on my laptop (which has more space).

The size of tempDB transaction log had increased from 4 GB to almost 30 GB after running the script.

Subsequent executions of the sp_UpdateClientChronicityStatus stored procedure after the initial run of the upgrade script caused tempDB’s transaction log to grow to 15 GB in size.

The cause of tempdb transaction log’s growth appears to be the logging of the numerous updates made to the #T_CLIENT_CALENDAR table from the sp_UpdateClientChronicityStatus stored procedure.

The artcle below suggests that using a SELECT INTO can be more efficient as Bulk operations are minimally logged under the Simple recovery model used by TempDB.

"The amount of logging for SELECT...INTO depends on the recovery model in effect for the database. Under the simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. With minimal logging, using the SELECT...INTO statement can be more efficient than creating a table and then populating the table with an INSERT statement. For more information, see The Transaction Log (SQL Server)."

Infrastructure Canada has been provided with this information and a re-written sp_UpdateClientChronicityStatus stored procedure that uses SELECT INTO instead of INSERT INTO and UPDATE.

Ticket 13385 has been opened for this issue.