June 28, 2011 at 9:23 am
Hi,
I rebuild indexes for one of my prod db weekly.
Last 2 times it is failing with the same error --
Failed:(-1073548784) Executing the query "ALTER INDEX [exportrecord_pk] ON [dbo].[exportrecord] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF )
" failed with the following error: "Transaction (Process ID 77) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I checked all other jobs which run at same time for deadlocking, but there are none, also this job was running fine for a long period of time, before this happened.
So, I rebuild the indexes of the particular table ( exportrecord) manually...
After that i again ran the job in the night but still I get the same error..
I also ran dbcc and update stats every weekend and everythign is gud.
Can some1 shed light to this.?
Thanks.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 28, 2011 at 9:31 am
You may want to exclude that table for the main index rebuild job and then add it to a secondary plan.
You have something that is running at the same time as the rebuild causing a deadlock. You need to find what is contributing to the deadlock and then work from there to fix the problem.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 30, 2011 at 8:54 am
SQLRNNR (6/28/2011)
You may want to exclude that table for the main index rebuild job and then add it to a secondary plan.You have something that is running at the same time as the rebuild causing a deadlock. You need to find what is contributing to the deadlock and then work from there to fix the problem.
The rebuilding job runs in the night , so is it possible for me to start the trace (maybe another schedled job) just few mins prior to the job?
Will it be effective for me to check the next morning?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 30, 2011 at 8:58 am
Will the option
"Keep index online while reindexing" help solve the deadlock issue?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 30, 2011 at 9:08 am
SKYBVI (6/30/2011)
Will the option"Keep index online while reindexing" help solve the deadlock issue?
Regards,
Sushant
sorry, cant perform that operation as its a standard edition not an enterprise edition
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 30, 2011 at 9:19 am
SKYBVI (6/30/2011)
SQLRNNR (6/28/2011)
You may want to exclude that table for the main index rebuild job and then add it to a secondary plan.You have something that is running at the same time as the rebuild causing a deadlock. You need to find what is contributing to the deadlock and then work from there to fix the problem.
The rebuilding job runs in the night , so is it possible for me to start the trace (maybe another schedled job) just few mins prior to the job?
Will it be effective for me to check the next morning?
Regards,
Sushant
You can schedule a trace to run via job for that time frame. Other options might include setting up some sort of monitoring to fire off an alert to you when a deadlock occurs. The solution would need to trap the queries from both sides of the deadlock so you can effectively investigate.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 1, 2011 at 1:42 am
Hi!
I run the trace for Dedlock graph that can give you all information about the deadlocks on server. I start the trace when SQL Server Agent starts and I never turn it off. Here is the code for the trace definition...
declare @traceoptions int
declare @stoptime DATETIME
declare @tracefilename nvarchar(245)
declare @filecount int
SELECT @tracefilename = physical_name FROM sys.backup_devices
WHERE NAME = 'deadlock_trace_bd'
set @traceoptions = 2 -- (TRACE_FILE_ROLLOVER) <trace_options,int,0>
set @maxfilesize = 10 -- <max_file_size_mb, bigint, 5>
set @stoptime = null-- <stop_time, datetime, NULL>
set @filecount = 10
exec @rc = sp_trace_create @TraceID output, @traceoptions, @tracefilename, @maxfilesize, @stoptime, @filecount
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 51, @on
exec sp_trace_setevent @TraceID, 148, 4, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 26, @on
exec sp_trace_setevent @TraceID, 148, 60, @on
exec sp_trace_setevent @TraceID, 148, 64, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 148, 41, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
//Sergey
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply