deadlocks caused by QS_IndexLogWaitCounts.sql (74866)

version 7.0.1.4515

No Errors in Spotlight, only SQL Log. This is the SQL that's causing it:

Microsoft bug

It is Microsoft issue encountered when we run the sql causing the deadlock.

So Here is the suggestion on how to disable the sql "QS_IndexLogWaitCounts.sql "

Steps:

- find and backup the following 2 files on the DiagnosticServer-machine:

C:\Program Files\Quest Software\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight\2005\QS_IndexLogWaitCounts.sql

C:\Program Files\Quest Software\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight\2008\QS_IndexLogWaitCounts.sql

Then put /* and */ comments before the first and after the last command in both scripts in the way below:

-- SoSSE: 7.0.1.4515

-- File: QS_IndexLogWaitCounts.sql

-- *

-- * Copyright 2010 Quest Software, Inc.

-- * ALL RIGHTS RESERVED.

-- *

-- * This software is the confidential and proprietary information of

-- * Quest Software Inc. ("Confidential Information"). You shall not

-- * disclose such Confidential Information and shall use it only in

-- * accordance with the terms of the license agreement you entered

-- * into with Quest Software Inc.

-- *

-- * QUEST SOFTWARE INC. MAKES NO REPRESENTATIONS OR

-- * WARRANTIES ABOUT THE SUITABILITY OF THE SOFTWARE,

-- * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED

-- * TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS

-- * FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. QUEST

-- * SOFTWARE SHALL NOT BE LIABLE FOR ANY DAMAGES

-- * SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING

-- * OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.

-- *

/*

if isnull(object_id('tempdb..#qs_indexopstatscounts'),0)<> 0

begin

drop table #qs_indexopstatscounts

end

create table #qs_indexopstatscounts (

dbid int,

tableid int,

indexid int,

inserts bigint,

deletes bigint,

updates bigint

)

if isnull(object_id('tempdb..#qs_indexopstatsoutput'),0)<> 0

begin

drop table #qs_indexopstatsoutput

end

create table #qs_indexopstatsoutput (

keyval nvarchar(384),

dbid int,

dbname nvarChar(128),

tableid int,

tablename nvarChar(128),

indexid int,

indexname nvarchar(128),

inserts bigint,

deletes bigint,

updates bigint

)

insert into #qs_indexopstatscounts

select

database_id,

object_id,

index_id,

convert(bigint,leaf_insert_count + nonleaf_insert_count),

convert(bigint,leaf_delete_count + nonleaf_delete_count),

convert(bigint,leaf_update_count + nonleaf_update_count)

from sys.dm_db_index_operational_stats(null,null,null,null)

where

(leaf_insert_count > 0) or

(leaf_delete_count > 0) or

(leaf_update_count > 0) or

(nonleaf_insert_count > 0) or

(nonleaf_delete_count > 0) or

(nonleaf_update_count > 0)

declare @DBID int

declare @sql nvarchar(4000)

set @DBID = 0

while 1=1

begin

select @DBID = min(dbid)

from #qs_indexopstatscounts with (readpast)

where dbid > @DBID and has_dbaccess(db_name(@DBID)) <> 0

if @DBID is null

break

set @sql = 'use ' + quotename(db_name(@DBID))

set @sql = @sql + 'insert into #qs_indexopstatsoutput select

db_name(dbid) + ''|'' + isnull(object_name(tableid),'''') + ''|'' + isnull(si.name,''<Heap>''),

dbid,

db_name(dbid) as dbname,

tableid,

object_name(tableid) as tablename,

indexid,

si.name as indexname,

inserts,

deletes,

updates

from

#qs_indexopstatscounts ios

inner join sys.objects so with (readpast) on ios.tableid = so.object_id

inner join dbo.sysindexes si with (readpast) on ios.indexid = si.indid and ios.tableid = si.id and ios.dbid = ' + Convert(NVARCHAR,@DBID)

+ ' where

(so.type = ''U'')'

exec(@sql)

end

select top 100 * from #qs_indexopstatsoutput order by inserts + deletes + updates desc

drop table #qs_indexopstatsoutput

drop table #qs_indexopstatscounts

*/

- Save changes then. It will prevent the SQL that caused the problem from running.

This issue is happening on version 7.0.1 only