quote:

---

Originally posted by abeman

i got one idea as we had the same sort of issue with our software.

Have you looked at SIDS being re-used due to muti- threading in SQL server?

Is there any chance an application is reusing the same SID , causing the deadlock?

---

quote:

---

Originally posted by AndrewMurphy

try to staggger the servers to work at 1am, 2am & 3am.... to totally eliminate any possibility of cross-server interference.

can you also stagger the database updates to eliminate cross-database links.

when all else fails, grasp at straws....or post on the internet.

---

quote:

---

Originally posted by russell

Do these jobs operate on data only on their own server or do they all perform operations on the same set of data? If the former, staggering the job times won't help.

Are the reads and writes occurring against the same tables or are you reading from one and writing to another?

Is the cursor read only?

Is the select statement in the cursor utilizing an index?

Sure nothing else is happening at the same time on server that may be contributing to the deadlocks?

---

-- Outside loop
DECLARE eventCursor CURSOR LOCAL READ_ONLY FOR SELECT
	[id]
	, [employeeId]
	, [effectiveDate]
FROM
	ApprovalEvent
WHERE
	[status] IN ('P', 'A')
	AND [effectiveDate] < @tomorrow
	AND [companyId] = @companyId
	AND	(
		[employeeID] = @employeeID
		OR @employeeID IS NULL
		)

-- Inside loop
DECLARE eventRowCursor CURSOR LOCAL READ_ONLY FOR SELECT
		[tableName]
		, [idValue]
		, [columnName]
		, [newValue]
		, [oldValue]
	FROM
		ApprovalEventRow
	WHERE
		[eventId] = @eventId
	ORDER BY
		[tableName]
		, [idValue]

quote:

---

Sure nothing else is happening at the same time on server that may be contributing to the deadlocks?

---

SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT
...
...
<stuff>

quote:

---

Originally posted by spirit1

here's a method that will tell you about deadlocks and give yo uthe whole report without you having to run profiler and strain the system. it can even send you an email.

http://weblogs.sqlteam.com/mladenp/archive/2008/07/18/Immediate-deadlock-notifications-without-changing-existing-code.aspx

note that you have to enable service broker on the monitored database

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.7 out!

---

quote:

---

Originally posted by AndrewMurphy

1. whats different is the portion of the database that is updated each night. bacause of the future dated transactions I suspect different employees are beign updated each night. any chance some employees are having simialr updtes on problem nights.


2. can you load a "problem" night into a test environment and re-run there? you should get the same results if the environment & machine power is the same

---

DECLARE
	@Body VARCHAR(4000)
	, @From VARCHAR(50)
	, @To VARCHAR(50)
	, @Subject VARCHAR(100)
	, @companyId INT
	, @tomorrow DATETIME
	, @error INT
	, @message XML
	, @ErrorNumber INT
	, @ErrorMessage NVARCHAR(2048)
	, @ErrorSeverity INT
	, @errorState INT
	, @MailBody NVARCHAR(MAX)
SET @companyId = 3
SET @tomorrow = CONVERT(VARCHAR(50), DATEADD(DAY, 1, GETDATE()), 101)
EXEC logStartFlexJobAudit @companyId, 'adminEffectEvents', 1
BEGIN TRAN
BEGIN TRY
EXEC @error = adminEffectEvents @companyId, @tomorrow
COMMIT
IF @error <> 0 BEGIN
	SELECT @Body = 'The overnight process has failed during the adminEffectEvents step, with return code '+CONVERT(VARCHAR(50),@error)
	SELECT @From = '<REMOVED>'
	SELECT @To = '<REMOVED>'
	SELECT @Subject = 'SQLMail: Overnight Job Failure - [' + @@SERVERNAME + '] ' + DB_NAME()
	EXEC master..sp_send_cdosysmail @From, @To, @Subject, @Body 
END
END TRY
BEGIN CATCH
	SELECT
		@error = ERROR_NUMBER()
		, @errorMessage = ERROR_MESSAGE()
		, @ErrorSeverity = ERROR_SEVERITY()
		, @ErrorState = ERROR_STATE()

	IF @error = 1205 BEGIN
		SET @subject = 'OVERNIGHT DEADLOCK ON ' + @@SERVERNAME + '.' + DB_NAME()
		EXEC master.dbo.lockMailer
			@subject
			, '<REMOVED>'
	END
	RAISERROR(@errorMessage, @errorSeverity, @errorState)
	ROLLBACK
	RETURN
END CATCH
EXEC logCompleteFlexJobAudit @companyId, 'adminEffectEvents', @error, 1

ALTER PROCEDURE [dbo].[lockMailer]
	@subject NVARCHAR(255)
	, @recipient NVARCHAR(255)
AS BEGIN
	DECLARE
		@spid INT
		, @dbname NVARCHAR(255)
		, @lockedObjNAme NVARCHAR(512)
		, @sqlText NVARCHAR(MAX)
		, @loginName NVARCHAR(512)
		, @hostName NVARCHAR(512)
		, @isUserTran BIT
		, @transName NVARCHAR(512)
		, @msgSubject VARCHAR(2000)
		, @msgBody VARCHAR(8000)
		, @uniqueBatch UNIQUEIDENTIFIER
		, @from VARCHAR(255)

	SET @uniqueBatch = NEWID()
	SET @from = '<REMOVED>'

	DECLARE lockCursor CURSOR LOCAL FOR SELECT
		ISNULL([SPID], -1)
		, ISNULL([DatabaseName], 'NULL')
		, ISNULL([LockedObjectName], 'NULL')
		, ISNULL([SqlStatementText], 'NULL')
		, ISNULL([LoginName], 'NULL')
		, ISNULL([HostName], 'NULL')
		, ISNULL([IsUserTransaction], 1)
		, ISNULL([TransactionName], 'NULL')
	FROM
		master.dbo.vLocks
	ORDER BY
		[spid]

	OPEN lockCursor		

		FETCH NEXT FROM lockCursor INTO
			@spid
			, @dbname
			, @lockedObjNAme
			, @sqlText
			, @loginName
			, @hostName
			, @isUserTran
			, @transName

		WHILE ( @@FETCH_STATUS = 0 ) BEGIN

			SET @msgSubject = @subject
				+ ' (BATCH: '
				+ CAST(@uniqueBatch AS VARCHAR(64))
				+ ') (SPID: '
				+ CAST(@spid AS NVARCHAR(255))
				+ ')'

			SET @msgBody = '
<body>
<p>
<table>
<tr><td>SPID: ' + CAST(@spid AS VARCHAR(255)) + '</td></tr>
<tr><td>DBNAME: ' + @dbName + '</td></tr>
<tr><td>LOCKED: ' + @lockedObjName + '</td></tr>
<tr><td>HOST: ' + @hostName + '</td></tr>
<tr><td>USER TRAN: ' + CASE @isUserTran WHEN 1 THEN 'YES' ELSE 'NO' END + '</td></tr>
<tr><td>TRAN NAME: ' + @transName + '</td></tr>
<tr><td>SQL: ' + CAST(@sqlText AS VARCHAR(1000)) + '</td></tr>
</table>
</p>
</body>'

			-- Send the message
			EXEC master.dbo.sp_send_cdosysmail
				@from
				, @recipient
				, @msgSubject
				, @msgBody

			FETCH NEXT FROM lockCursor INTO
				@spid
				, @dbname
				, @lockedObjNAme
				, @sqlText
				, @loginName
				, @hostName
				, @isUserTran
				, @transName
		END

	CLOSE lockCursor
	DEALLOCATE lockCursor
END

USE [master]
GO
/****** Object:  View [dbo].[vLocks]    Script Date: 12/03/2009 13:07:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[vLocks] AS SELECT
	L.[request_session_id] AS SPID
	, DB_NAME(L.[resource_database_id]) AS DatabaseName
	, O.[Name] AS LockedObjectName
	, P.[object_id] AS LockedObjectId
	, L.[resource_type] AS LockedResource
	, L.[request_mode] AS LockType
	, ST.[text] AS SqlStatementText
	, ES.[login_name] AS LoginName
	, ES.[host_name] AS HostName
	, TST.[is_user_transaction] AS IsUserTransaction
	, AT.[name] AS TransactionName
FROM
	sys.dm_tran_locks L
	LEFT JOIN sys.partitions P ON P.[hobt_id] = L.[resource_associated_entity_id]
	LEFT JOIN sys.objects O ON O.[object_id] = P.[object_id]
	LEFT JOIN sys.dm_exec_sessions ES ON ES.[session_id] = L.[request_session_id]
	LEFT JOIN sys.dm_tran_session_transactions TST ON ES.[session_id] = TST.[session_id]
	LEFT JOIN sys.dm_tran_active_transactions AT ON TST.[transaction_id] = AT.[transaction_id]
	LEFT JOIN sys.dm_exec_requests ER ON AT.[transaction_id] = ER.[transaction_id]

	CROSS APPLY sys.dm_exec_sql_text(ER.[sql_handle]) AS ST
WHERE
	resource_database_id = db_id() 

quote:

---

Originally posted by Transact Charlie

Thanks for the suggestion Tara - that's something I can set on the individual batch isn't it?

SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT
...
...
<stuff>

The only problem is that testing such changes is really hard when the problem is so intermittent. I'll change the isolation level and see though.

---

quote:

---

Originally posted by tkizer

quote:

---

Originally posted by Transact Charlie

Thanks for the suggestion Tara - that's something I can set on the individual batch isn't it?

SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT
...
...
<stuff>

The only problem is that testing such changes is really hard when the problem is so intermittent. I'll change the isolation level and see though.

---

I recommend doing this at the database level and not at the query level. It does require downtime though since the database needs exclusive access during the change. You can make the change via ALTER DATABASE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

---

quote:

---

I would assume you could test it at the query level, but I've never tried that. Just to repeat what I earlier said, switching the isolation level will only help if your deadlocks are between reads and writes. If they are between writes and writes, then it will not help. Have you identified the deadlock victim and owner via SQL Profiler or the trace flag?

---