VistaDB 6
VistaDB / Getting Started / Developer's Reference - Database Maintenance
In This Topic
    Developer's Reference - Database Maintenance
    In This Topic

    Regardless of where your application is storing data a common set of maintenance concerns should be addressed such as:

    VistaDB is no different.  To provide the best support for your users once your application is deployed you should incorporate some features in your system to address each of these issues.

    Backing Up and Restoring

    This is generally simpler than when using SQL Server - there are no custom SQL Commands to learn and no special permissions are required to backup or restore because a VistaDB database is a single file.  By copying that file to a new location you've made a backup.  Copy it back to the original name and you've restored it.  You will need to either have no connections open to the database or only readers since any updates will prevent the operating system from getting a clean copy of the file.

    Incorporate a Backup feature in your application that stops access to the database, copies it to a known location, and then continues.  Use a date-encoded file name so it's easy to find the latest backup.  Recommend your users do this backup periodically.

    Maintaining Performance

    LIke SQL Server, VistaDB does require some periodic maintenance to function best.  In SQL Server this would typically consist of index defragmentation/rebuilding and DBCC CheckDB to detect and resolve minor data file corruption.  In VistaDB these functions are done by Pack and Repair. 

    Why pack a database? It:

    If the database has become truly corrupted (where the data on disk can't be loaded by the engine) a Pack is the first step since it will identify any uncorrectable problems.  If it can't resolve the issue, see Handling Corruption below.

    Incorporate a Pack feature in your application that stops access to the database, performs a pack, and reports the results to the user.  Make sure they can access this feature even if your application is having trouble opening the database to simplify support.

    For more information see How To - Pack a Database.

    Handling Corruption

    If you store data on enough computers or access it over a network long enough at some point it'll get corrupted.  It may be due to an underlying hardware or OS problem, running out of disk space at a really bad moment, a hard drive writing data out of order or any of a number of esoteric reasons.  When this happens to your application you want to be ready.  If you find yourself with a database that is throwing odd errors when you access it what do you do?

    1. Back Up the Database: While working to fix the problem it's useful to have a baseline to be sure if something goes really wrong while fixing it you can get back to your initial state.
    2. Pack the Database: Since the Pack process rebuilds the data file completely and in the process discards the original index data and rebuilds it many examples of file corruption will simply be ignored by the Pack process and the result is a file that's ready to go.  If not, the Pack process will provide a clear technical fault that can be used to determine the appropriate next step.
    3. Repair the Database: VistaDB has a Repair process that will attempt to create a valid VistaDB database by dropping indexes that can't be created (typically due to constraint violations) and data pages that can't be read.  Be sure you have a backup at this point because Repair may end up dropping more data than you can live with.  Otherwise, you can use the repaired database and add back missing indexes once you've corrected the data constraint problems.
    4. Database Recovery: If all else fails, Gibraltar Software offers a service where we will step through the database in a debugger and hand-correct problems to attempt to recover as much data as possible from the database.  There is a fee for this service.  Contact Support for more information.

    The most common sources of file corruption are:

    Incorporate a Repair feature in your application.  Be sure that a user first has to perform a normal Pack and use the option to create a backup to be sure users don't use this feature except as a last resort.

    For more information on repairing a database see How To - Pack a Database.

    Handling Transient Errors

    Some errors VistaDB throws indicate a clear problem not worth retrying - like a missing foreign key value or SQL code that won't parse.  Like SQL Server, there are some errors that you should retry because they probably will not recur.  In VistaDB the most common retryable errors are the Concurrency Error and Lock Timeout Error

    Exceptions will often wrap other exceptions so it's important to check each VistaDB exception in the exception chain.  To help detect this, when you perform a database operation in a Try/Catch block you can use the Contains method to see if a particular error code is anywhere in the exception chain.  The following code sample shows a simplistic approach to this:

    public void ExecuteWithRetry(DbCommand command)
        catch (VistaDBException ex)
            if (ex.Contains(Errors.dda_TransactionConcurrency) || ex.Contains(Errors.dda_LockTimeout))
                //this is a retryable error
                throw; //we don't want to retry these so let the exception continue to propagate
    Public Sub ExecuteWithRetry(command As DbCommand)
        Catch ex As VistaDBException
            If ex.Contains(Errors.dda_TransactionConcurrency) OrElse ex.Contains(Errors.dda_LockTimeout) Then
                'this is a retryable error
                'we don't want to retry these so let the exception continue to propagate
            End If
        End Try
    End Sub

    In your application you should determine a suitable place to put retry logic that minimizes the code you write and fits with your architecture and data access approach.

    For more information, see How To - Handle SQL and VistaDB Exceptions.

    See Also