|
|
Posted by Erland Sommarskog on 03/10/06 00:57
traceable1 (tracykc@gmail.com) writes:
> I am running SQL Server 2000 SP4 (w/AWE hotfix) on a 32-bit Windows
> 2003 Server (SP1).
>
> I am trying to run an Index defrag and rebuild job. It has worked fine
> in the past, but lately I'm getting this:
>
> Msg 1205, Sev 13: Transaction (Process ID 73) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction. [SQLSTATE 40001]
>
> From what I've read, I can only lower the priority on other processes.
> Since there are multiple applications running several different things
> on this database, that seems impossible. I really just want this opt
> job to finish - it's fine if it takes a while or needs to go back and
> run something again, but it absolutely needs to finish!
That's somewhat surprising. I mean, would you rather have a user have
this message splashed in the face? I certainly would rather have a
background task to die, than have my users spit and swear.
One option would be to redesign the job, so that it stores data in
permanent tables, so when it restarts it can pick up where it last
was deadlock. You would set up the Agent job, so when a step fails
it restarts.
I see that your job has both DBCC DBREINDEX and INDEXDEFRAG. DBREINDEX
requires exclusive access to the tables, so it's not wise to run
day-time.
It's also possible that you should analyse why get so high fragmentation
and investigate if there is anything you can do to address it.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|