SELECT Operation with SNAPSHOT ISOLATION failed due to REBUILD INDEX and UPDATE STATISTICS job at sametime, On week-End

Question

Post reply

SELECT Operation with SNAPSHOT ISOLATION failed due to REBUILD INDEX and UPDATE STATISTICS job at sametime, On week-End

patelchandresh330 80387

Mr or Mrs. 500

Points: 512
More actions
March 30, 2015 at 2:27 am

#299090

Hi,
We have used "SNAPSHOT ISOLATION LEVEL"
and we have REBUILD INDEX and UPDATE STATISTICS job on week-end but our "SELECT" (that only get the data) Stored procedure get failed due to "SNAPSHOT ISOLATION LEVEL" and parallelly REBUILD INDEX and UPDATE STATISTICS job.
Our database server is in UK (UK timezone) and users uses the application from India, Singpore and UK
so we dont have any specific time to decide when we have to execute the maintenance job.
Please suggest me possible ways to avoid this situation ?
Thanks
Chandresh Patel

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

With snapshot isolation you mean exactly snapshot isolation or read committed snapshot?

How are you maintaining your indexes? A maintenance plan, a custom stored procedure/job or what?

-- Gianluca Sartori

patelchandresh330 80387 Mr or Mrs. 500 Points: 512 More actions · Answer 2

Hi Gianluca Sartori,

I have used "SET TRANSACTION ISOLATION LEVEL SNAPSHOT" in store procedure and dynamic script (rebuild indexes and update statistics) are written in Job.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why are you trying to have index rebuilds running in snapshot isolation level? What's the point?

From Books Online:

The transaction isolation levels define the type of locks acquired on read operations.

Index rebuilds aren't read operations.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

patelchandresh330 80387 Mr or Mrs. 500 Points: 512 More actions · Answer 4

Hi GilaMonster,

It is something like,

Session 1: executed get stored procedure of business logic. means only SELECT statement is in that SP under SNAPSHOT ISOLATION LEVEL.

means running application conflict with following step in week-end

Session 2: SQL Job, Weekly scheduled on server. (Dynamic Code is written for rebuild index and update Statistics)

both are seperate, and if execute at same time then its throw following error :

Msg 3961, Level 16, State 1, Line 6

Snapshot isolation transaction failed in database 'test' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The error's right, you can't run anything in Snapshot isolation while DDL changes are happening, and index rebuilds are DDL.

Can you schedule the index changes to some other time?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

patelchandresh330 80387 Mr or Mrs. 500 Points: 512 More actions · Answer 6

Hi GilaMonster,

Thanks GilaMonster for reply..

you are right we can not use DDL command during Snapshot isolation..

but usage of application is almost 24 hours. Is there any other way so that i can put all running transactions in wait ? so end user just feel slowness but not error message or log-out from system.

Thanks

Chandresh Patel