When Creating a VenueDataRecovery, the Database is too big to restore on SQL Express instances due to TransComment Table

Overview

Problem:

Why is the Venuedatabase Recovery are over 10GB in size? The entire TransComment Table is being copied which includes comments for all Venues, 

This is due to the TransComment Table not having a VenueID field.

To work around this issue, at present we need to reduce the size of this table so we can perform VenueDataRecoveries at a Reasonable DB size. 

A future fix has been logged, to ensure this process does not need to be done manually and will be catered for in the VenueDataRecovery Process.

 

 

 

As highlighted below in the Example, is the size a TransComment table can contribute to the Size of a VenueDataRecovery. 

---

Solution:

--Stop HO Smartcontroller 

--Clone TransComment Table (Backup)

SELECT * INTO Backup_TransComment from TransComment

 

--Quick compare of TransComment Vs Backup_TransComment

Select count(*) as Count from Transcomment

Select count(*) as Count from Backup_TransComment

 

--Clear TranComment.Comment Field (Empty String '')

UPDATE TransComment

SET Comment = ''

 

--Start HO Smartcontroller again 

 

Example of TransComment Size after removing all comments from 20GB to 82MB

--Create VenueDataRecoveries as per required venue(s).

--Once all Venues have been restored.

 

--STOP HO Smartcontroller

--Restore Backup_TransComment.comment to TransComment.comment

UPDATE TransComment

SET Comment = t2.comment

FROM TransComment t1

INNER JOIN Backup_TransComment as t2

    On t1.TransCommentID = t2.TransCommentID

 

--Drop Backup_TransComment

Drop Table Backup_TransComment;

 

Linked Issues: