We have a blocking scenario in our environment (OLAP,SQL Server 2014). Session 1 is doing a select from the table X and session 2 is trying to do  a truncate of the same table . Truncate(session2) is getting blocked by the select (session1). The isolation level of the database has been changed to read commited snapshot.

The select queries are mostly run by users trying to run their reports.

Do we have a way of avoiding this blocking and let the truncate progress(off course killing the session is an easy option)

I am skeptical of using (with nolock) with select statements  in this scenario as the truncate will still require a Sch-M lock which it will not be able to acquire in this scenario.

Could you please share your expert opinion on this.

Hello,

As Long as someone accesses the table you can't perform modifications like TRUNCATE, which requires an exclusive lock on the table and there is no way around; you have to wait for the ex-lock.

as mentioned in my post what if we are using with (nolock) and a 'read commited snapshot' level isolation level. Can we implemnt a more optimistic isolation level and do the trick. I belive the answer will be still a 'no'

• Edited by DJ_DBA 29 minutes ago