January 14, 2009 at 2:57 pm
Hi all,
We have an index on a view that gets disabled, on an almost daily basis, and I can't figure out why.
I am aware that an index can be disabled using the ALTER INDEX command or sometimes when SQL Server is upgraded, but I don't believe this is what is happening.
The underlying data are reloaded every day, and the view along with its index are rebuilt every day, so something seems to be happening after the early AM batch process and before I come into the office each morning.
Any ideas? :pinch:
TIA!
January 14, 2009 at 3:13 pm
Check the default trace on the server. See if a disable command is being issued.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 3:43 pm
They're probably disabling the indexes, then re-enabling and rebuilding them.
Not uncommon and a good way to go.
I'm guessing they are missing that one in their load script.
~BOT
Craig Outcalt
January 14, 2009 at 5:40 pm
The index is being re-created every morning after the data load. It exists the rest of the day, but at some point, early on, it's being disabled.
January 15, 2009 at 6:55 am
The default trace should have the command in it that is doing the disabling. That should give you the source.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 1:10 pm
GSquared (1/15/2009)
The default trace should have the command in it that is doing the disabling. That should give you the source.
We finally discovered the source. Nope, there is no trace record in this case. When an index on an upstream clustered index is dropped, the downstream view's index is quietly disabled.
So, now I know of 3 ways to disable an indexed view:
1) ALTER INDEX
2) Drop upstream indices.
3) SQL Server update
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply