EV SQL DB maintenance error

[Korbyn]

Been noticing in the sql maintenance logs the following error, and repeats it 3 times:

Rebuild Index (EVSQL1-SEVDB02\PRODSQL2)
Rebuild index on Local server connection
Databases: EVVSEVSERVeVS_3,EVVSEVSERVfVS_5,EVVSEVSERVgVS_6
Object: Tables and views
Original amount of free space
Task start: 2011-01-31T11:58:01.
Task end: 2011-01-31T11:58:01.
Failed:(-1073548784) Executing the query "ALTER INDEX [PK_HoldData_1] ON [dbo].[HoldData] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
" failed with the following error: "Online index operation cannot be performed for index 'PK_HoldData_1' because the index contains column 'HoldMetadata' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 

We're running EV 8.0 SP4 with SQL2005 SP3 on Windows 2003 R2 SP2 x64 machines.  And were following the maintenance tasks as defined in: http://www.symantec.com/business/support/index?page=content&id=TECH74666&actp=search&viewlocale=en_U...

SQL is in a cluster, so is EV, not that it should matter much... 

[JesusWept3]

Pass this to your SQL DBA's and have them change their maintenance places

[Korbyn]

Maintenance places???

If you mean plans, change them to what?

I reread the document again, perhaps it's because I'm not shutting down all EV services on 7 nodes, to rebuild the Indexes?

[GertjanA]

Hello Korbyn,

What JW means is that this is an issue on SQL, not EV. Your DBA's should be able to figure this out, and get it working properly.

Maintenance plans are 'scheduled tasks' within SQL to do 'maintenance' actions on databases.

[JesusWept3]

Yup, thats exactly what i mean, when the SQL Server complains about Indexes, it is talking about SQL Indexes as opposed to Enterprise Vault Indexes..... SQL indexes, allow quicker result sets to be returned from complex SQL Queries, views and stored procedures, and letting them get stale or fragmented is not good for performance.

As part of good house keeping and best practices, it is recommended to perform maintenance tasks, which rebuild SQL indexes, perform shrinks, backup and truncate the transaction logs etc etc, these however should be set and controlled by your SQL Server Administrators and/or database administrators.

The problem you have though is that the particular indexes that want to be rebuilt cannot be performed whilst the database is online, due to a complex data type, these operations can be likened to Exchange database maintenance, although you can do some operations online, the best results come from offline rebuilds and such




 

[Korbyn]

I don't suppose you have a ball park time frame of how long these rebuilds of the indexes might take?  On of the EV DB's is at 12 Gb's for example, or is that of no relavance?

We're ingested data for over 20,000 mailboxes now, another 30,000 to go, and thought that this might be a good time to perform this maintenance and see how long it takes for the existing amount of data.

If it takes longer then the change window, do you happen to know if there is a way of stopping it, or is it a situation where you must let it complete or you're fubar'ed?

[Korbyn]

I'm testing in the lab, all EV services are offline, following the defined process in http://www.symantec.com/docs/TECH74666

The only way I can get the maintenance job for Rebuild Indexes to run successfully, is to clear the "Keep Index Online while reindexing"

As all EV services are offline, I don't see this as a problem.  Am I in error, should the EV article: http://www.symantec.com/docs/TECH74666 be changed I wonder.

[Korbyn]

I did find an alteris document that states for it's SQL database, Keep Index Online... MUST be cleared because of the types of data objects being used.  I suspect the same is true for EV Databases.

Has anyone been checking their SQL maintenance logs to see if they've been successfull?

[Bruce_Cranksh1]

Korbyn I'm not a SQL DBA but I have had several cases where I have worked with SQL DBA to resolve these types of issues around the various EV tables

As mentioned you need to involve the SQL dba's in this process

Ask the DBA's to run DBCC check on the EV Databases

This will show them the levels of fragmentation.

They will have there own views on how to resolve this

 Normally I stop the EV services during the SQL Maintenance runs so the SQL Index tables can be rebuilt

[GertjanA]

I had an issue a while ago where the issue was related to :

2 - EnterpriseVault Database Reorganize Indexes, Shrink DB's and UPdate Statistics Weekly

 Executing the query "ALTER INDEX [PK_tblIntDiscoveredItems] ON [dbo].[tblIntDiscoveredItems] REORGANIZE WITH ( LOB_COMPACTION = ON )" failed with the following error: "The index "PK_tblIntDiscoveredItems" (partition 1) on table "tblIntDiscoveredItems" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 I’ve found a posting that when Enterprise Vault is upgraded, Page Level Locking is disabled on the SQL databases, which explains the 2^nd error. Is it possible (and ‘best practice’!) to re-enable this Page Level Locking again? Is it possible to run a query to check if more indexes are having PLL disabled?

Check this entry, it has a check script: https://www-secure.symantec.com/connect/forums/maintenance-plan-fail-after-upgrade-due-page-level-lo...

And here is the 'idea' describing the issue: https://www-secure.symantec.com/connect/idea/page-level-locking

Thanks to Liam.

[Korbyn]

The dba's didn't even know to build a plan to shrink the logs, so forgive me if I don't ask them...

I tried running that script by Scanner001, didn't run successfully for me:

Msg 102, Level 15, State 1, Line 26

Incorrect syntax near '1'

This was a fresh install of EV 8.0 SP4, no upgrades.  We've upgrade in the lab to SP5 and can still reproduce the issue.

time to open a case I guess.

[JesusWept3]

I'm not sure how much EV support will be able to do with it being a SQL issue and all

You have several options really

use DBCC DBReindex
http://msdn.microsoft.com/en-us/library/ms181671(SQL.90).aspx

Change your maintenance plans so that the index rebuild state does not require to keep the index online, for example
ALTER INDEX [indexName] ON [dbo].[tableName] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

Another option maybe to use IndexOptimize by Ola Hallengren
http://ola.hallengren.com/

[Korbyn]

Except its a SQL issue as a result of EV coding for the DB.  They've spent so many years building these DB's, tables and stored procedures, that I would expect Symantec to resolve this type of problem.  And IF the PLL being left on is accidental, then is should be Symantec to resolve it.

[JesusWept3 · Accepted solution]

There's nothing *to* resolve though, for the most part the SQL databases that EV use, whether its the Directory, Vault Store or Fingerprint, are normalized databases, with many indexes, primary keys, foreign keys, constraints, stored procedures, views, user defined types etc etc.

So where as you can say its a result of how the database is designed, I would agree with you except this is not a negative, for the databases to be as performant as they are, there really isn't any other choice.

Asking them to resolve an issue that quite clearly sits with SQL Server is like asking Symantec not to write indexes or DVS files that would cause fragmentation on the drive, its just not possible as its handled by Windows and the storage technology itself, and its the same way with SQL indexes becoming fragmented, its just part and parcel that goes with databases and their maintenance

If you actually Google the message that you are getting, you will find thousands of results, such as
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/623d635e-94c7-4cdd-80f5-b46ab17c...

or
http://blog.sqlauthority.com/2007/08/17/sql-server-2005-explanation-and-script-for-online-index-oper...

I guess my point is that you can't take what is pretty much SQL 101 for a regular DBA and have EV support perform that function for you, because there are many instances where a good DBA can make sure that your databases are integral and performant, just as a SAN Admin can make sure you have the correct and performant storage solutions for your needs, or your networking team can make sure your network is optimized etc, none of those functions should be expected to be replaced by a vendors support.







 

[Korbyn]

What I meant JW, is that IF Symantec designed the table, view or what ever to have that Lock in place, then we should be deselecting the "Keep online..." option.

However, if it's accidental that the Lock is in place, I can only believe it to be a result of the installation process or upgrade process, as half the guys say that it may be.

[JesusWept3]

ah yeah, gotcha, sorry, the Page Level Locking has zero to do with this, its due to the data types and foreing keys used