Hi All,
Good day.
our Reorganise index Maintenance paln always failed for a while.
I never figure out what is wrong before.
today I used the SSIS package "Reorganize Index Task" to run the task, finally find out what cause the job failure.
the error message is :
Msg 2552, Level 16, State 1, Line 1The index "IND_TW_SLI_DETAIL_BYPASS_spid" (partition 1) on table "TW_SLI_DETAIL_BYPASS" cannot be reorganized because page level locking is disabled.
This is v9.0 release Notes:
985
Indexes on the following tables have been reorganized to improve performance: TX_PERF_PMAP, TX_APPEAL_MEDIA_TYPE, T_MEMB_ORG, T_EADDRESS, T_ROLE, T_PRODUCTION, T_CUST_ACTIVITY, T_ISSUE_ACTION, T_ACCOUNT_DATA and T_LINEITEM. In addition the index on the work table TW_SLI_DETAIL_BYPASS has been changed to disallow page_locks to improve concurrency. Indexes were added to T_BATCH and T_SEAT to cover some frequently occurring queries. Two indexes on T_LINEITEM were removed, as they were used very infrequently.
Final solution:
USE [impresario]GOALTER INDEX [IND_TW_SLI_DETAIL_BYPASS_spid] ON [dbo].[TW_SLI_DETAIL_BYPASS] SET (ALLOW_PAGE_LOCKS = ON)GO
run my Reorganise index Job
USE [impresario]GOALTER INDEX [IND_TW_SLI_DETAIL_BYPASS_spid] ON [dbo].[TW_SLI_DETAIL_BYPASS] SET (ALLOW_PAGE_LOCKS = OFF)GO
have fun
Ben
Ken,
What do you do about internal tables? I’m getting errors and my SQL consultant describes it here:
We have a weekly Index Maintenance procedure for the [impresario] DB’s which works fine for all of the system tables and user tables.
If zero “Internal Tables” were active at the time when we did our Index Maintenance, then the Index Maintenance job would succeed.
Solving the current issue requires me to figure out how to deal with any (temporarily active) “Internal Tables” in the DB.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain Sent: Monday, June 27, 2016 9:16 PM To: Gloria Ormsby Subject: Re: [Tessitura Technical Forum] Maintenance Plan Reorganise index
Hi Gloria
I've posted the current version of the scripts to my Files here
It's been changed a bit since this original forum message, but fundamentally the same process.
The major change, courtesy of my colleague Rob Dunn, was to run it in several chunks with a small break between each, so that it if has to do a major rebuild, the database gets a chance to take a few deep breaths every now and again, to let the log catch up and avoid making the temp db grow too much.
That means there's now four components rather than just the one proc - two procs, a local table (which is stored in another database to avoid cluttering up Impresario - you'd need to modify that location obviously.) and a job that runs the procs in sequence.
Ken
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 6/27/2016 12:26:31 PM
Hi Ken,
Would you be able to share your custom reindex procedure that is "tessiturized?"
Gloria
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
We've never had an issue with internal tables, as far as I'm aware.
Since their names can't be referenced in transact-sql statements, and only their metadata is available for viewing, I would assume that they're entirely unaffected (directly) by stored procs of any kind, pretty much by definition. So our script never tries to touch them.