There's a little on SIX locks here - http://msdn.microsoft.com/en-us/library/ms175519.aspx. Trying to dig up more clear comments.

Jeff Moden (3/30/2010)

---

Unless you work for Microsoft or have a Microsoft URL stating that, I'm pretty sure you...

I take it I'm not the only one irritated by the odd salutations around here.

Before you declare it SEP¹, check the procs that call these, and any that call those, etc and make sure that there's no explicit BEGIN TRANSACTION in them.

(1) Someone else's...

The deadlock graph mentions a user transaction, and neither of the procs that you've posted start or commit a transaction. There's a proc called earlier, somewhere, that starts a transaction...

Also http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

Not as well written as the articles.

Post the stored procedures and the deadlock graph please.

If the database is in simple recovery, you...

CTEs and table variables are totally different.

A table variable, like a temp table, is a table in TempDB, kept in memory if possible, spilt to disk if not.

A CTE,...

CirquedeSQLeil (3/30/2010)

---

If the MVP were paid by Microsoft to be on this review board, then that person could no longer be an MVP (MS Employees can't be MVP). ...

Why the hell are you truncating your log and shrinking your database?

Please read through this - Managing Transaction Logs[/url]

Shrinking causes massive fragmentation and will just result in the data file...

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Please note that was a mistype and should have read SQL Server 2008.

It's possible. There's a documented option to backup to do just that.

Interesting... The error is in the snapshot that checkDB creates and uses...

I've referred this to a corruption expert for assistance. It may be necessary to restore from your last...