The optimise for ad-hoc workloads option does not exist in SQL 2005. It was added in SQL 2008.

p.s. you may want to change 'allow updates' back to 0 if you...

Don't use Task Manager to check SQL's memory. It can be completely wrong.

If the restore completed but was run WITH NORECOVERY then run RESTORE DATABASE <db name> WITH RECOVERY

If you aborted the restore part way through, drop the DB and run the...

I would advise that you don't. There's nothing that suggests in any way that there's any corruption or damage to the DB, and if there was, repair software would not...

SQL cannot compare two different data types, nor can it multiply by a string. Hence, any time you ask it to do that, it will do an implicit conversion. As...

Autogrow is not logged to the error log unless it times out, takes excessively long or fails. Try the default trace.

Craigmeister (12/17/2012)

---

Would you say this is an issue of these processes not being optimized so they are not completing fast enough and thus trip on each other? Or is it...

No, the trigger is not causing lock escalation. Lock escalation is to table always, we're dealing with page and row locks...

All right, so if we discard all the parallelism mess (which is mostly irrelevant noise), we have this:

spid=52, running a statement within a trigger

UPDATE BART_V3_TBL_ORDERS SET BART_V3_TBL_ORDERS.DateLastChanged = GETDATE()

FROM BART_V3_TBL_ORDERS

INNER...

Performance Monitor. Total Server memory counter (it's a SQL counter)

Don't use Task Manager to check SQL's memory usage. It doesn't report correctly.

Profiler.

And index hints should be your last resort, not your first idea.

Can you post the entire deadlock graph please? By the looks of it, you've simplified out info that I need to see what's happening.

Can you also post the table definition...

Restoring or recovering?

Restoring means that the DB is in the process of being restored from a backup. Check the error log, see what happened.

Try RESTORE DATABASE <database name> WITH RECOVERY,...

Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

DBCC TRACEON(1222,-1)

How...