Dead logs while log backup is running

Question

Dead logs while log backup is running

gary1

SSC-Insane

Points: 21152
More actions
April 25, 2012 at 12:15 pm

#255427

Hi,
We are using litespped for backups and frequently getting dead locks during log backups.
Could you please advise how to rectify this issue?
2012-04-25 08:30:06.71 spid20s deadlock-list
2012-04-25 08:30:06.71 spid20s deadlock victim=process3c30718
2012-04-25 08:30:06.71 spid20s process-list
2012-04-25 08:30:06.71 spid20s process id=process3c30718 taskpriority=0 logused=0 waitresource=KEY: 41:72057594038976512 (8001fdcd970d) waittime=4156 ownerId=7253309733 transactionname=SELECT lasttranstarted=2012-04-25T08:30:02.510 XDES=0x9b590bf8 lockMode=S schedulerid=5 kpid=6964 status=suspended spid=248 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2012-04-25T08:30:02.510 lastbatchcompleted=2012-04-25T08:30:02.510 clientapp=Activitymanager hostname=SQL1hostpid=8592 loginname=abc\mssqlsrv isolationlevel=read committed (2) xactid=7253309733 currentdb=41 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128568
2012-04-25 08:30:06.71 spid20s executionStack
2012-04-25 08:30:06.71 spid20s frame procname=adhoc line=1 sqlhandle=0x02000000dbe72f28a44b8ef564e13c2cfe5b67f8bec53465
2012-04-25 08:30:06.71 spid20s (@1 tinyint)SELECT [ServerName],[ActivityID],[DatabaseID],[ActivityTypeID],[StartTime],[FinishTime],[StatusTypeID],[PID] FROM [LitespeedActivity] WHERE [StatusTypeID]=@1 AND [ReplicateID] IS NULL
2012-04-25 08:30:06.71 spid20s frame procname=adhoc line=1 sqlhandle=0x0200000024a0601b390dad11cf6a484be6813ee371d0cf4e
2012-04-25 08:30:06.71 spid20s select ServerName, ActivityID, DatabaseID, ActivityTypeID, StartTime, FinishTime, StatusTypeID, PID
2012-04-25 08:30:06.71 spid20s from LitespeedActivity
2012-04-25 08:30:06.71 spid20s where StatusTypeID = 1 and ReplicateID is null
2012-04-25 08:30:06.71 spid20s inputbuf
2012-04-25 08:30:06.71 spid20s select ServerName, ActivityID, DatabaseID, ActivityTypeID, StartTime, FinishTime, StatusTypeID, PID
2012-04-25 08:30:06.71 spid20s from LitespeedActivity
2012-04-25 08:30:06.71 spid20s where StatusTypeID = 1 and ReplicateID is null
2012-04-25 08:30:06.71 spid20s process id=process3c3ada8 taskpriority=0 logused=3712 waitresource=PAGE: 41:1:46184 waittime=4156 ownerId=7253309736 transactionname=UPDATE lasttranstarted=2012-04-25T08:30:02.510 XDES=0x3195033d0 lockMode=IX schedulerid=6 kpid=9628 status=suspended spid=277 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2012-04-25T08:30:02.507 lastbatchcompleted=2012-04-25T08:30:02.507 clientapp=LiteSpeed for SQL Server {c4ce7b6e-0ff6-4d4f-8ba1-b191fbe877b9} hostname=PGR hostpid=6044 loginname=abc\mssqlsrv isolationlevel=read uncommitted (1) xactid=7253309736 currentdb=41 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128056
2012-04-25 08:30:06.71 spid20s executionStack
2012-04-25 08:30:06.71 spid20s frame procname=LiteSpeedLocal.dbo.LiteSpeed_InsertUpdateDatabaseActivity line=73 stmtstart=4752 stmtend=6046 sqlhandle=0x03002900e5517260ffaf2701dc9f00000100000000000000
2012-04-25 08:30:06.71 spid20s unknown
2012-04-25 08:30:06.71 spid20s frame procname=adhoc line=3 stmtstart=154 stmtend=2028 sqlhandle=0x02000000350d2308ca178ecab54ebe3d433ab0a63e1faadd
2012-04-25 08:30:06.71 spid20s exec LiteSpeed_InsertUpdateDatabaseActivity
@ActivityID=@activityIdOut output,
@ServerName=N'PGR',
@DatabaseName=N'WSS_Content',
@ActivityType=2,
@status=2,
@PercentCompleted=100,
@ActivityDetail=N'@Database=WSS_Content, @BackupName=Transaction Log backup on (UTC): Wednesday, April 25, 2012 15:30:00, @FileName=\\PGR\SharePoint_LSBackups\B41E5958-55AD-4700-818C-C1900B7576D3_WSS_Content_20120425153000.trn, @With=SKIP',
@ErrorMessage=N'',
@ResultMessage=N'LiteSpeed(R) for SQL Server Version 6.5.1.1018
Copyright 2011 Quest Software, Inc.
2012-04-25 08:30:06.71 spid20s Processed 296 pages for database ''WSS_Content'', file ''WSS_Content_log'' on file 1.
BACKUP LOG successfully processed 296 pages in 0.122 seconds (19.846 MB/sec).
2012-04-25 08:30:06.71 spid20s ',
@PID='6044',
@DatabaseSize=266240.0,
@NativeSize=6.25,
@BackupSize=1.69,
@BackupTime=0.31,
@AttachedNativeSize=0.00,
@AttachedBackupSize=0.00,
@AttachedBackupTime=0.00
2012-04-25 08:30:06.71 spid20s inputbuf
2012-04-25 08:30:06.71 spid20s use [LiteSpeedLocal] declare @activityIdOut int
set @activityIdOut = 245738
exec LiteSpeed_InsertUpdateDatabaseActivity
@ActivityID=@activityIdOut output,
@ServerName=N'PGR',
@DatabaseName=N'WSS_Content',
@ActivityType=2,
@status=2,
@PercentCompleted=100,
@ActivityDetail=N'@Database=WSS_Content, @BackupName=Transaction Log backup on (UTC): Wednesday, April 25, 2012 15:30:00, @FileName=\\PGR\SharePoint_LSBackups\B41E5958-55AD-4700-818C-C1900B7576D3_WSS_Content_20120425153000.trn, @With=SKIP',
@ErrorMessage=N'',
@ResultMessage=N'LiteSpeed(R) for SQL Server Version 6.5.1.1018
Copyright 2011 Quest Software, Inc.
2012-04-25 08:30:06.71 spid20s Processed 296 pages for database ''WSS_Content'', file ''WSS_Content_log'' on file 1.
BACKUP LOG successfully processed 296 pages in 0.122 seconds (19.846 MB/sec).
2012-04-25 08:30:06.71 spid20s ',
@PID='6044',
@DatabaseSize=266240.0,
@NativeSize=6.25,
@BackupSize=1.69,
@BackupTime=0.31,
@AttachedNativeSize=0.00,
@AttachedBackupSize=0.00,
@AttachedBackupTime=0.00
select @
2012-04-25 08:30:06.71 spid20s resource-list
2012-04-25 08:30:06.71 spid20s pagelock fileid=1 pageid=46184 dbid=41 objectname=LiteSpeedLocal.dbo.LitespeedActivity id=lock47f6380 mode=S associatedObjectId=72057594040549376
2012-04-25 08:30:06.71 spid20s owner-list
2012-04-25 08:30:06.71 spid20s owner id=process3c30718 mode=S
2012-04-25 08:30:06.71 spid20s waiter-list
2012-04-25 08:30:06.71 spid20s waiter id=process3c3ada8 mode=IX requestType=wait
2012-04-25 08:30:06.71 spid20s keylock hobtid=72057594038976512 dbid=41 objectname=LiteSpeedLocal.dbo.LitespeedActivity indexname=PK_LitespeedActivity id=lock4b4b380 mode=X associatedObjectId=72057594038976512
2012-04-25 08:30:06.71 spid20s owner-list
2012-04-25 08:30:06.71 spid20s owner id=process3c3ada8 mode=X
2012-04-25 08:30:06.71 spid20s waiter-list
2012-04-25 08:30:06.71 spid20s waiter id=process3c30718 mode=S requestType=wait
2012-04-25 08:30:08
Thanks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 1

I would contact the vendor about this. If it is their software that's causing it they'll know it better than we do and will be able to change it. I would imagine that Quest would be helpful with something like this.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

You're getting deadlocks from your backup software? That's really not good. Contact the vendor. If they don't have an immediate fix, find another vendor. I have a suggestion on that if you need it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning