Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We had to restart our SQL Server today, we had made no changes to it.

When it came back up we immediately started getting this error from the server

DATE/TIME: 2/27/2014 3:09:31 PM

DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec' (instance 'Database') of object 'SQLServer:Locks' is now above the threshold of 1.00 (the current value is 2.00).

COMMENT: (None)

JOB RUN: (None)

We ran the DBCC TRACEON (1204) command and have watched the log's but it's not reporting any deadlocks.

Any idea what could trigger this to just go off? We are getting the alert every minute yet can't find any actual deadlocks.

Edit: I should add that before this reboot we had never received this error

Edit 2: We used SQL Server Profiler as well to look for deadlocks, let it run for 5 minutes over which we received 5 error alerts and when we checked the details we had NO deadlocks found.

Edit 3: March 06/2014: Ran the query and it worked, but it reports what our other details have said that we have no locks we where still getting the error above the whole time.

Thanks again for all your help!

enter image description here

Edit 4: March 06/2014: I ran the query and here is a sampling of the result set, I will admit I am not exactly sure what I am looking at here, that is to say I am not sure if it shows me something that I can act on or not.

enter image description here

Edit 5: March 07/2014: Image below shows the Alert that generates this error all of a sudden.

enter image description here

Thanks

share|improve this question

migrated from stackoverflow.com Feb 27 at 20:30

This question came from our site for professional and enthusiast programmers.

    
What's the wait type on that super long wait? –  Zane Mar 6 at 16:39
    
Where exactly are you getting this alert every minute? In the SQL Server error log, by e-mail, ...? –  Aaron Bertrand Mar 7 at 11:34
    
It is generated from the SQL Server Agent, under Alerts you can see the screen shot I added to the main post of what it looks like. This alert generates an email every 1 minute with the text in it mentioned above –  Stephen Archbold Mar 7 at 14:46

2 Answers 2

Update March 5th 2014

-- Removed the CTE and any options that could be incompatible with SQL SERVER 2000

if OBJECT_ID(N'tempdb..#process', N'U') is not null
    drop table #process
GO

if OBJECT_ID(N'tempdb..#tmp', N'U') is not null
    drop table #tmp
GO

select spid as victim, blocked as blocker
into #process
from master..sysprocesses p1 
where spid > 50
and blocked > 0
and spid <> blocked

insert into #process
select spid as victim, blocked as blocker
from master..sysprocesses p 
inner join #process
on #process.blocker = p.spid
where spid > 50


select  distinct 'dbcc inputbuffer(' + CAST( victim as nvarchar(4)) + ')' as cmd, 'sp_who2 ' + CAST( victim as nvarchar(4)) as spwho, *
into #tmp
from #process 
where blocker = 0
order by victim


select *
from #tmp 
order by victim

DECLARE @cmd nvarchar(400)
DECLARE @spwho nvarchar(12)

DECLARE curseur CURSOR
FOR
    select  cmd, spwho
    from #tmp 
    order by victim

OPEN curseur
FETCH NEXT FROM curseur INTO @cmd, @spwho

WHILE @@FETCH_STATUS = 0
BEGIN
    print (@cmd)
    exec (@cmd)
    print (@spwho)
    exec (@spwho)
    FETCH NEXT FROM curseur INTO @cmd, @spwho
END

CLOSE curseur
DEALLOCATE curseur 

GO

select
    db_name(dbid),
    'dbcc inputbuffer(' + CAST(a.spid as nvarchar(4)) + ')',
    a.spid,
    a.blocked,
    a.status, 
    a.cmd, 
    a.cpu,
    a.waittime,
    a.loginame,
    Base = DB_NAME(a.dbid),
    *
from master.dbo.sysprocesses a
where 1 = 1
    and a.spid > 50
    and spid <> @@spid
order by
    a.blocked desc,
    a.waittime desc,
    a.cpu desc         

enter image description here

Update 3/6/2013

So we've seen that no sessions are being blocked. Now we can also look at all current locks on your system.

Show locks server wide MSSQL 2000

Monitoring Lock Activity

select convert(varchar(30), suser_sname(p.sid)) as login,
       convert (smallint, req_spid) As spid,
       convert(varchar(30), db_name(rsc_dbid)) As db_name,
       case rsc_dbid when db_id()
            then convert(varchar(30), object_name(rsc_objid))
            else convert(varchar(30), rsc_objid) end As Object,
       rsc_indid As indid,
       substring (lock_type.name, 1, 4) As Type,
       substring (lock_mode.name, 1, 12) As Mode,
       substring (lock_status.name, 1, 5) As Status,
       substring (rsc_text, 1, 16) as Resource
   from master..syslockinfo s
   join master..spt_values lock_type on s.rsc_type = lock_type.number
   join master..spt_values lock_status on s.req_status = lock_status.number
   join master..spt_values lock_mode on s.req_mode = lock_mode.number -1
   join master..sysprocesses p on s.req_spid = p.spid
   where lock_type.type = 'LR'
     and lock_status.type = 'LS'
     and lock_mode.type = 'L'
     and db_name(rsc_dbid) not in ('master', 'msdb', 'tempdb', 'model')
order by spid, lock_type.number
go

The output will you tell you 1. what type of lock is being held: database, table, page. 2. the mode of the lock: S shared, X exclusive, RangeS-S where a range of values is locked in an index

enter image description here

Update 3/7/2014

I have updated your screen shot with some basic explanations. What we are seeing, is just a bunch of shared database locks.

To be brief, the locks on your system are shared (in other words, they don't block other sessions from accessing the database) and those locks are meant to prevent the database from being dropped, detached or renamed while data is being read and/or written from and to that database and its contents.

Getting back to your original question; there are no deadlocks or blocked sessions on your server. I don't remember having seen this message before, but from what you are sending back, this is definitely not about deadlocks and more likely to be just an informational message.

Or use sp_lock

sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

Which will give you a more detailed view of all locks currently being held on your server.

http://technet.microsoft.com/en-us/library/aa238824(v=sql.80).aspx

share|improve this answer
    
I tried to run your query and it gave me the below error: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'with'. Msg 208, Level 16, State 1, Line 2 Invalid object name 'master.sys.dm_exec_requests'. –  Stephen Archbold Mar 3 at 15:46
    
Hello @StephenArchbold, I updated the query, there was a left over usage of exec_requests that is not compatible with 2000. –  Craig Efrein Mar 3 at 16:17
    
Thanks for your help, Still getting an error around the 'with' statement for incorrect syntax –  Stephen Archbold Mar 4 at 17:17
    
@StephenArchbold - I redid the SP this time without the CTE (common table expression) and some other isolation options that might cause problems. –  Craig Efrein Mar 5 at 9:13
    
Ran your query and you can see the results in my original post –  Stephen Archbold Mar 6 at 15:47

While this counter says /sec, I believe it is a cumulative counter. If I'm right, this means you have had a TOTAL of 2 deadlocks since the service has been up.

I'm not at a machine where I can test this, but I suspect that if you simulate your own deadlock - once - you'll suddenly be getting 3.0 deadlocks "per second" until another deadlock occurs or you restart the service.

There are many scripts out there that will help you force a deadlock, basically:

-- window 1

Create table dbo.x(a int); create table dbo.y(b int);

Insert dbo.x(a) select 1: insert dbo.y select 2;

Begin transaction;

Update dbo.x set a = a + 1; 

-- window 2

Begin transaction;
Update dbo.y set b = b + 1;
Update dbo.x set a = 5;

-- go back to window 1

Update dbo.y set b = 22;

Now, if the counter goes up to 3.0, you know what to do: go find that pesky alert and disable it, or keep getting the alert but become desensitized to it unless the number changes drastically.

share|improve this answer
    
I tried what you suggested and forced a deadlock. I did not start receiving any more alerts though and we adjusted the threshold on the alert up to 2 so that the current error would stop spamming us while we look into it. so if it had moved up to 3 then it should have started spamming again. –  Stephen Archbold Mar 7 at 15:01
    
@Stephen so the counter is still sitting at 2? –  Aaron Bertrand Mar 7 at 15:03
    
Yes sir, it does not seem to have increased –  Stephen Archbold Mar 7 at 15:05
    
@Stephen Well something certainly doesn't smell right. How could there always be 2 deadlocks/second occurring, yet no deadlocks show up in trace etc.? I still say shut off the alerts. If you really must know every time a deadlock happens, run a server-side trace to a table, and then set up a trigger or event notification or something. –  Aaron Bertrand Mar 7 at 15:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.