Vista 5 full SQL database issue

[Product_Support]

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

1. TAC Vista stopped trending or cannot read trend value.
2. SQL reaches 4GB or 10GB limitation (depending on version, Vista 5.0.1 - Vista 5.1.6 supports up to 4 GB and Vista 5.1.7 and newer support up to 10 GB)
3. $queues folder keep growing

Product Line

TAC Vista

Environment

• TAC Vista 5
• Logging errors
• SQL data Log full, shrink

Cause

The SQL 2005 Express version that come with Vista 5.0.1-5.1.6 has a 4GB limitation and the SQL 2008 Express version that comes with Vista 5.1.7 and newer has a 10GB limit. Once the SQL database reaches this limitation, Vista will not be able to read/write data from/into the SQL database. To fix this issue, the SQL database needs to be purged and a new SQL database needs to be created, or the SQL server needs to be upgraded.

Once the SQL database is full, log data will be stored in the $queues folder wanting to be written into the SQL database. If the $queues folder gets too big that even creating a new SQL database with the same size limitations will not allow the Vista Server to run then data in the $queues folder will need to be removed or an upgrade to a version of SQL with larger size limits will need to be performed.

Resolution

Note: In deciding which resolution below will be most appropriate, consideration should be given to how quickly the SQL database is growing and how long it took to fill up initially. When the SQL database fills up it is a perfect time to carefully look at the various trends and alarms that consume space in the database and consider what if anything might reasonably be removed. Also, note Vista 5.1.7 and newer include SQL Express 2008 which has a 10 GB storage limit. This is 2.5 times greater than the SQL Express 2005 version that was included with earlier versions of Vista 5 and is a free upgrade from any Vista 5.1.X versions.

Please find the scenario that matches your issue and read the resolution section accordingly.

	Scenario A	Scenario B	Scenario C	Scenario D
Data in "$queues" folder	Not needed	Needed	Not needed	Needed
Data in SQL Db	Not needed	Not needed	Needed	Needed

NOTE:  Administrator rights on the computer and to the SQL database is required in all scenarios in order to resolve this issue.

In order to prevent the same issue occurring again, a log backup should be performed regularly.  Refer to Performing database maintenance with Vista 4.5.0 for additional information. Additionally, consideration should be given to whether an upgrade to full version of SQL should be installed to best suit the needs of the system.

Scenario A: Data in the $queues folder and SQL database are not needed.

In this scenario, empty the $queues folder and create a new SQL database is the resolution. Please follow the steps below:

1. Empty $queues folder
   1. Go to database folder. Its path can also be found in TAC Vista Server Setup (start->all programs/Schneider Electric/TAC Vista Server5.X.X. For TAC Vista 5.1.3 and prior, go to start->all programs/TAC/TAC Vista Server5.X.X instead).  Under “Vista Database” tab, the path is listed in the “Database folder path” textbox.
      
      
       
   2. Delete everything in the $queues/insertevents folder (NOTE: don’t delete the folder).
   3. Delete everything in the $queues/insertlogvalues folder (NOTE: don’t delete the folder).
       
2. Delete taclogdata.
   1. Go to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data (default path).
   2. Delete “taclogdata.mdf” (default name, or go to Vista Server Setup and find the SQL name under SQL Server tab, Log database name).
   3. Delete “taclogdata_log.LDF “ (default name,  or go to Vista Server Setup and find the SQL name under SQL Server tab, Log database name).
       
3. Open “Server Setup” (start->all programs/Schneider Electric/TAC Vista Server5.1.X. For TAC Vista 5.1.3 and prior, go to start->all programs/TAC/TAC Vista Server5.X.X instead) and select “SQL Server” tab.
   1. Under “SQL configuration”, choose “Custom”.
      
      
       
   2. Under “Custom connect using”, make a new name to “Log database name” (example:  change “taclogdata” to “taclogdata2”).
      
      
      NOTE: DO NOT change the user name and password.
       
   3. Click Apply
      1. Click “OK” for “TAC Vista integrated SQL backup/restore function will not be available in custom configuration”.
         
         
          
      2. Click yes at the prompt that says “No log database was found on SQL Server. Create and configure a log database?"
         
         
          
      3. Click OK.

Issue should be resolved at this point.

Scenario B: Data in the $queues folder is needed, but SQL database is not needed.
If $queues folder is too big, need to upgrade to a full version of SQL database in this scenario.  $queues folder can be found inside the Vista database folder (refer to Scenario A step 1 for how to find the $queue folder). If $queues folder’s size is not too big do the following:

1. Leave everything in the $queues folder. Data will be written into SQL database automatically after a new SQL database is established.
   NOTE: depends on the size of the $queues folder, it may take hours to transfer all data from the $queues folder into the SQL database.
    
2. Follow Scenario A steps 2-3 to purge the SQL database and create a new one.

Issue should be resolved at this point.

Scenario C: Data in the $queues folder is not needed, but SQL database is needed.
If the SQL database is needed, it can be removed from the Microsoft SQL Express folder and stored somewhere else. However, an upgraded version of SQL server is needed in order to view the old SQL database.  If the Vista version is <5.1.7Please follow the steps below:

1. Follow Scenario A step 1 to empty the $queues folder.
    
2. Move taclogdata
   1. Go to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data (default path).
   2. Move “taclogdata.mdf” out of the folder to somewhere else (for example: Desktop). NOTE: taclogdata is the default name, or go to Vista Server Setup and find the SQL name under SQL Server tab, Log database name.
   3. Move “taclogdata_log.LDF “out of the folder to somewhere else (for example: Desktop).
      NOTE: taclogdata is the default name, or go to Vista Server Setup and find the SQL name under SQL Server tab, Log database name.
       
3. Follow Scenario A step 3 to create a new SQL database.

Issue should be solved at this point.

Scenario 😧 Data in the $queues folder and SQL database are both needed.
If the SQL database is needed, it can be removed from the Microsoft SQL Express folder and stored somewhere else. However, an upgraded version of SQL server is needed in order to view the old SQL database.

Versions of Vista between 5.0.1 and 5.1.6 (which include SQL Express 2005) can be upgraded to Vista 5.1.7 or newer (which use SQL Express 2008).  This will upgrade from a 4 GB database size limit to a 10 GB limit and is a free upgrade for and version of Vista 5.1. (Note that it is a paid upgrade from Vista 5.0.X.) In scenarios where SQL Express 2008 is already being used and is full or where 10 GB will not be an adequate upgrade for SQL Express 2005 then a full version of SQL Server will need to be purchased and installed.

If $queues folder is too large, it will be necessary to upgrade to a full version of SQL server in this scenario.  $queues folder can be found inside the Vista database folder (refer to Scenario A step 1 for how to find the $queue folder). If $queues folder’s size is not too big do the following:

1. Leave everything in the $queues folder. Data will be written into SQL database automatically after a new SQL database is established.
   NOTE: Depending on the size of the $queues folder, it may take hours to transfer all data from the $queues folder into the SQL database.
    
2. Follow Scenario A steps 2-3 to purge the SQL database and create a new one.

Issue should be resolved at this point.