Attaching the table structure and indexes on deadlock related tables just to complete information....

USE [Lightyear]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[dms_topic_entry](

[subscriber_id] [numeric](19, 0) NOT NULL,

[msg_id] [numeric](19, 0) NOT NULL,

[delivery_date] [numeric](19,...

sam rebus (2/3/2010)

---

well, that's just the thing. there is nothing running at these times. This server is hosting 3 SQL instances that have backup jobs at different times, around 9...

CirquedeSQLeil (2/3/2010)

---

More than likely not. As Jeff and Elliott said - it...

Now this is crazy one I am lost whats happening here...

Here's the question why 944 has exclusive lock on dms_topic_msg_idx here its just doing an update on another table(dms_msg) completely...

GilaMonster (2/4/2010)

---

There's no character limit to included columns in...

This is exactly...

I suspect you are using some process which is using nay extended procedures or clr ones causing memory leak

or

Have a external backup utility like veritas or litespeed purging memory

or...

Every counter has its own guidelines for thresholds, though i would suggest to see the trend and define a baseline for acceptable threshold...even what is suggested online may not exactly...

Quite confusing what exactly is the problem, I would say you need to explain it better to get right help...

Are you saying that after cleaning buffers first time procedure run...

One of the simplest way to load data is to store in CSV format and then use openrowset to read it

Though you need to create a schema file for the...

Since its happening at 00 and 12 hours, my best guess is there could be a backup job (Ideally that should be an external utility like litespeed) running that time...

There are various things to look here though I will first try to see whats taking the memory so lets try this....

Run below statement, it will give you exactly what...

Right, for...

Michael Valentine Jones (2/2/2010)

---

Since the deadlock victim is the select, there is a good chance that setting the database to READ_COMMITTED_SNAPSHOT will prevent the deadlocks and it will not require...

try this...

declare @command1 varchar(8000)

select @command1 =

'

IF ''@'' ''master'' AND ''@'' ''model'' AND ''@'' ''msdb'' AND ''@'' ''tempdb''

begin

use [@] execute sp_MSForEachTable ''print ''''?'''' ''

end

'

exec...