Optimizing Dragnet database with SQL Enterprise Manager

[TBiker]

We configure our SQL server to perform routine backup and maintence for all databases including VAULT (sgvault) and Dragnet (sgdragnet). When I try to include the sgdragnet database in the optimizations update job it causes the job to exit with a failure. If I do not include that database, it runs fine. I believe it is because one of the indexes in that database is on a "computed" column. The particular job that fails updates the statistics that the query optimizer uses to maintain query efficiency weekly.

Further info regarding these can be found at:

http://msdn.microsoft.com/library/defau ... tquery.htm

I believe the particular problem I am encountering is discussed in the following knowledgebase article:

http://support.microsoft.com/default.as ... -us;301292

I am not a certified SQL administrator, and know only enough to make me dangerous, so this is as far as I go with the problem. The VAULT and DRAGNET databases are backing up, so I don't think there is a panic here, but I believe you should look into how the optimizations sould be setup properly and suggest code to implement the knowledgebase requirements. I have left the job active with the databases that do optimize without a problem (sgvault), and have just turned off the checkbox for the database that fails to optimize.

[jclausius]

That is entirely possible. The table, keywords, has a computed index for the lower case text of a word for case insensitve searches.

Has anyone changed any of the DB options?

In any case, try the following script. Does it alleviate the problem?

ALTER DATABASE [sgdragnet] SET ARITHABORT ON
GO
ALTER DATABASE [sgdragnet] SET CONCAT_NULL_YIELDS_NULL ON
GO
ALTER DATABASE [sgdragnet] SET QUOTED_IDENTIFIER ON
GO
ALTER DATABASE [sgdragnet] SET ANSI_NULLS ON
GO
ALTER DATABASE [sgdragnet] SET ANSI_PADDING ON
GO
ALTER DATABASE [sgdragnet] SET ANSI_WARNINGS ON
GO

ALTER DATABASE [sgdragnet] SET NUMERIC_ROUNDABORT OFF
GO