DBCC CHECKTABLE didn't work for me.

I had to follow instruction described in this article (http://gursethi.blogspot.com.au/2010/08/error-2601-severity-14-state-1-cannot.html) to delete records in sys.syscommittab which then fixed the issue for me.

A few troubleshooting queries.

--Run below the query you should see a queue with state "DROPPED". According to my understanding, this is not normal. Once our issue was resolved, no queue...

The message stopped appearing followed by our monthly patching which restarted the server hence the instance. Below is the summary supplied by Microsoft before the case was closed.

Symptom

===============

Below error...

Looks like not many people saw this error before. Just logged a call with Microsoft. Hopefully the cause can be found.

The instance is on 2012 sp1 cu12

I have tried the script. It's very useful. Thanks for sharing it.

chiesa.alberto (1/17/2014)

---

It seems to me that a lot of people is not getting really HOW a query is executed.

But that should be querying 101.

I would just point out the T-SQL...

Couldn't agree more.

Thanks for sharing. Very nice. Looking to use in our environment.

Very nice script. Thanks for sharing. As many people have said here, it has a good basis and great potential to be extended to suit people's own needs.

This is completely nothing to do with the technical discussion here.

Though you may think partitioning or other methods could do the job better (well, you do understand the version limitation...

Have to come back to say thanks. I had a glance at this article the other day. Today it did save me.

Thanks for sharing. I have a similar monitoring in place using event notification but it's much simpler than yours. I only monitor changes to functions, stored procedures for selected databases...

Thanks Andy. A very nice post.

I had same problem. The same query does massive logical reads on 2008 R2.

Stats on 2008.

Table 'Worktable'. Scan count 5, logical reads 5763460, physical reads 0, read-ahead reads 0, lob...