Maintenance: The Vault Server database

[jclausius]

Backup / Recovery
Performing regular database backups is essential in any data system. The same holds true for Vault. Creating reliable, consistent backups every 1 to 2 days is strongly recommended. In the case of a catastrophic / irrecoverable failure, a backup lets you get back to work while keeping data loss to a minimum.

While the Vault Admin Tool can perform simple backups when the server has been configured with SQL Server authentication, this is a manual operation. It's best to backup up your database automatically on a regular basis through SQL Server backup tools. http://msdn.microsoft.com/en-us/library/ms186289.aspx

A SQL Query Tool is also capable of creating backups using the T-SQL BACKUP DATABASE command. See the Microsoft's SQL Server Help manual for more information -
http://msdn.microsoft.com/en-us/library/ms186865.aspx.

Additionally, SQL Server Management Studio supports creating database backups -
http://msdn.microsoft.com/en-us/library/ms187510.aspx .

By default, the Vault database is configured to use the default Recovery model of your instance of SQL Server. The easiest to manage, but longest running recovery model is the Simple recovery model. With the Simple Recovery model, the database can be recovered to the point of the last backup. Since this strategy can result in data loss, the backup interval should be long enough to keep the overhead of creating a backup from affecting usage, but short enough to prevent the loss of any significant amount of data.

If the Simple Recovery model does not fit your needs, please feel free to explore / modify the Recovery model of the database.


Database Health

You should regularly check your Vault databases for any internal errors. A weekly check is recommended. The DBCC CHECKDB command will run through a thorough set of checks to make sure the database is in operating condition. This can be done within any SQL Server Query tool by running the Check DB command ( DBCC CHECKDB ( 'sgvault' ), DBCC CHECKDB('sgmaster'), etc. ). See SQL Server's help system for more information for DBCC CHECKDB - http://msdn.microsoft.com/en-us/library/ms176064.aspx

We have striven to keep required maintenance low; however, the Vault database require some tweaking. This is important when changes have been made by large deletions or inserts due to an import or big obliteration. Over time changes like these may cause a table's index to become fragmented or skew the data distribution within a table.

In these cases, you should run a couple of extra maintenance routines:

Updating the Statistics - SQL Server keeps internal meta-information about the table to help determine a plan of execution for queries issued by the Vault Server. You can force SQL Server to update these statistics using the UPDATE STATISTICS T-SQL command -
http://msdn.microsoft.com/en-us/library/ms187348.aspx

Depending on usage, it is recommended updating database statistics on all of the Vault related databases at least once a week. If a large number of added items, branches, deletions, and shares are made within Vault's repositories, updating database statistics may need to be run more frequently.

Rebuilding and Defragging indices - Sometimes when rows are deleted from tables, an index used to help lookups within a database table may become severely fragmented. These can be either defragged in place ( DBCC INDEXDEFRAG ) or rebuilt ( DBCC DBREINDEX ). Please note, statistics are updated as a side effect of DBCC DBREINDEX, while DBCC INDEXDEFRAG will not update statistics.

It is recommended to Defrag the Vault databases' indices at least once a month. If there have been a large number of added items, branches, deletions, and shares made within Vault's repositories, this can be done more frequently.

Rebuilding the databases' indices is a bit more thorough and can replace the Index Defragmentation routine. You can also configure to rebuild the indices while taking the database offline. Regardless of the configuration, it is recommended to rebuild the Vault databases' indices at least twice a year. This can be done more often if severe repository changes are frequent or an index defrag is not utilized.

DBCC DBREINDEX - http://msdn.microsoft.com/en-us/library/ms181671.aspx
DBCC INDEXDEFRAG - http://msdn.microsoft.com/en-us/library/ms177571.aspx

When the Vault database is stored on conventional I/O subsystems ( non-RAID / non-striped / non-SAN ), SQL Server can sometimes run into problems related to physical disk fragmentation. You can determine whether disk fragmentation exists using system tools provided Windows or from third parties to analyze these drives. In these environments, you should correct disk fragmentation before running any database index defragmentation.

As a courtesy to our customers, the following SQL scripts have been provided to assist with any maintenance needs:

• Scripts for SQL 2000/2005/2008 users
  
  Scripts for SQL 2012/2014 users

These commands could be utilized with the sqlcmd.exe command line tool, and set to run on a regular basis.
http://msdn.microsoft.com/en-us/library/ms162773.aspx

Note to SQL Server 2000 Users:
Due to Vault's use of a computed column within an index, SQL Server Enterprise Manager Optimization plans may fail. Enterprise Manager's extended stored procedure is configured with an incorrect initial environment, which does not allow it to run the optimization commands on any database containing computed columns within an index. For these purposes, we recommend developing your own plan using the previously discussed T-SQL commands.

If you're running SQL 2000 SP4, there is an additional flag you can use to allow the wizard-generated SQL maintenance jobs to run. The details are here: http://support.microsoft.com/kb/902388/

[Beth]

SQL 2000/2005/2008

This sample script will UPDATE statistics on all tables within the sgvault database. It should be modified for the other Vault Standard/Professional related database (sgmaster, sgnotify, sgvaultindex, and sgdragnet).

Code: Select all

USE sgvault
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

DECLARE @@name [nvarchar](128),
   @@stmt [nvarchar](384)

DECLARE c CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
OPEN c

SET @@stmt = N'--'
FETCH FROM c INTO @@name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- print the name of the table
   PRINT N'Table Name: ' + @@name

   -- statement to update table's statistics
   SET @@stmt = N'UPDATE STATISTICS ' + @@name 
   EXEC sp_executesql @@stmt
   
   -- next table
   FETCH NEXT FROM c INTO @@name
END

CLOSE c
DEALLOCATE c

This sample SQL script will defrag all indices of all tables within the sgvault database. It should be modified for the other Vault related database (sgmaster, sgnotify, sgvaultindex, and sgdragnet).

Code: Select all

USE sgvault
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

DECLARE @@name [nvarchar](128),
   @@idx [nvarchar](128),
   @@stmt [nvarchar](384)

DECLARE c CURSOR FOR SELECT so.name, si.name FROM sysobjects so INNER JOIN sysindexes si ON (so.id = si.id) AND ( (si.name LIKE N'pk_%') OR (si.name LIKE N'idx%') OR (si.name LIKE 'uniq%')) WHERE so.type = N'U' ORDER BY so.name, si.name
OPEN c

SET @@stmt = N'--'
FETCH FROM c INTO @@name, @@idx
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- print the name of the table / index
   PRINT N'Table Name: ' + @@name + N' Index: ' + @@idx

   -- statement to update defrag an index
   SET @@stmt = N'DBCC INDEXDEFRAG(sgvault, ' + @@name + ', ' + @@idx + ')'
   EXEC sp_executesql @@stmt

   FETCH NEXT FROM c INTO @@name, @@idx
END

CLOSE c
DEALLOCATE c

This sample SQL Script will rebuild indices within within the sgvault database. It should be modified for the other Vault related database (sgmaster, sgnotify, sgvaultindex, and sgdragnet).

Code: Select all

USE sgvault
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

DECLARE @@name [nvarchar](128),
   @@stmt [nvarchar](384)

DECLARE c CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
OPEN c

SET @@stmt = N'--'
FETCH FROM c INTO @@name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- print the name of the table
   PRINT N'Table Name: ' + @@name

   -- this statement will completely rebuild all indices on the table
   SET @@stmt = N'DBCC DBREINDEX(''sgvault.dbo.' + @@name + ''', '''', 0)' 
   EXEC sp_executesql @@stmt

   -- next table
   FETCH NEXT FROM c INTO @@name
END

CLOSE c
DEALLOCATE c

[Beth]

SQL Server 2012 and later


This sample script will UPDATE statistics for tables within the sgvault database. It should be modified for the other Vault related database (sgmaster, sgnotify, sgvaultindex, and sgdragnet).

Code: Select all

    USE sgvault
    GO

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @@name [nvarchar](128),
    @@stmt [nvarchar](384)

    DECLARE c CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
    OPEN c

    SET @@stmt = N'--'
    FETCH FROM c INTO @@name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    -- print the name of the table
    PRINT N'Table Name: ' + @@name

    -- statement to update table's statistics
    SET @@stmt = N'UPDATE STATISTICS ' + @@name + N' WITH FULLSCAN'
    EXEC sp_executesql @@stmt

    -- next table
    FETCH NEXT FROM c INTO @@name
    END

    CLOSE c
    DEALLOCATE c

This sample SQL script will defrag all indices of all tables within the sgvault database. It should be modified for the other Vault related database (sgmaster, sgnotify, sgvaultindex, and sgdragnet).

Code: Select all

USE sgvault
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

DECLARE @@name [nvarchar](128),
@@idx [nvarchar](128),
@@stmt [nvarchar](384)

DECLARE c CURSOR FOR SELECT so.name, si.name FROM sysobjects so INNER JOIN sysindexes si ON (so.id = si.id) AND ( (si.name LIKE N'pk_%') OR (si.name LIKE N'idx%') OR (si.name LIKE 'uniq%')) WHERE so.type = N'U' ORDER BY so.name, si.name
OPEN c

SET @@stmt = N'--'
FETCH FROM c INTO @@name, @@idx
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- print the name of the table / index
PRINT N'Table Name: ' + @@name + N' Index: ' + @@idx

-- statement to update defrag an index
SET @@stmt = N'ALTER INDEX [' + @@idx + N'] ON [dbo].[' + @@name + N'] REORGANIZE WITH ( LOB_COMPACTION = ON )'

EXEC sp_executesql @@stmt

FETCH NEXT FROM c INTO @@name, @@idx
END

CLOSE c
DEALLOCATE c

This sample SQL Script will rebuild indices within the sgvault database. It should be modified for the other Vault related database (sgmaster, sgnotify, sgvaultindex, and sgdragnet).

Code: Select all

USE sgvault
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

DECLARE @@name [nvarchar](128),
@@stmt [nvarchar](384)

DECLARE c CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
OPEN c

SET @@stmt = N'--'
FETCH FROM c INTO @@name
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- print the name of the table
PRINT N'Table Name: ' + @@name

-- this statement will completely rebuild all indices on the table using a 10% FREE (90% utilized FILLFACTOR)
SET @@stmt = N'ALTER INDEX ALL ON ' + @@name + N' REBUILD WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90);'

EXEC sp_executesql @@stmt

-- next table
FETCH NEXT FROM c INTO @@name
END

CLOSE c
DEALLOCATE c