Performing regular database backups is essential in any data system. The same holds true for Dragnet. 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
For a reliable backup solution, a simple SQL Query Tool is 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/library/defau ... z_35ww.asp. Additionally, SQL Server's Enterprise Manager also supports creating database backups. Note - Enterprise Manager does not ship with MSDE 2000.
By default, the Dragnet database is configured to use 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 check your Dragnet database for any internal errors on regular intervals. 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 ( 'sgdragnet' ) ). See SQL Server's help system for more information for DBCC CHECKDB - http://msdn.microsoft.com/library/defau ... c_00gy.asp
We have striven to keep required maintenance low; however, the Dragnet database may sometimes require some tweaking. This is important when changes have been made by large additions or edits due to a a batch edit. 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 may want to 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 Dragnet Web Application. You can force SQL Server to update these statistics using the UPDATE STATISTICS T-SQL command -
http://msdn.microsoft.com/library/defau ... z_1mpf.asp
Rebuilding and Defragging indices - Sometimes when rows are deleted from tables, the an index used to help lookups within a database table may become severely fragmented. These can be either rebuilt ( DBCC DBREINDEX ) or defragged in place ( DBCC INDEXDEFRAG ). Please note, statistics are updated as a side effect of DBCC DBREINDEX, while DBCC INDEXDEFRAG will not update statistics.
DBCC DBREINDEX - http://msdn.microsoft.com/library/defau ... c_94mw.asp
DBCC INDEXDEFRAG - http://msdn.microsoft.com/library/defau ... c_30o9.asp
When the Dragnet 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.
One final note, due to Dragnet'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.
As a courtesy to our customers, the following SQL scripts have been provided to assist with any maintenance needs:
This sample script will UPDATE statistics on all Dragnet tables.
Code: Select all
USE sgdragnet
GO
DECLARE @@name [nvarchar](64),
@@stmt [nvarchar](256)
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 cCode: Select all
USE sgdragnet
GO
DECLARE @@name [nvarchar](64),
@@idx [nvarchar](64),
@@stmt [nvarchar](256)
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'pkl%') 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(sgdragnet, ' + @@name + ', ' + @@idx + ')'
EXEC sp_executesql @@stmt
FETCH NEXT FROM c INTO @@name, @@idx
END
CLOSE c
DEALLOCATE cCode: Select all
USE sgdragnet
GO
DECLARE @@name [nvarchar](64),
@@stmt [nvarchar](256)
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(''sgdragnet.dbo.' + @@name + ''', '''', 0)'
EXEC sp_executesql @@stmt
-- next table
FETCH NEXT FROM c INTO @@name
END
CLOSE c
DEALLOCATE c