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]




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!

Without this optimization, our applications run extremely slow.

Please help!


================================================== ========

Here's the proc:

CREATE PROCEDURE DTS_IndexDefragAndRebuild
(
@dbname varchar(100) ,
@isReportOnly bit = 0
)
AS

SET NOCOUNT ON

CREATE TABLE #tables(
rid int identity (1,1),
tabid int,
[name] varchar(100)
)

CREATE TABLE #indexes(
rid int identity (1,1),
indid int,
[name] varchar(100)
)

CREATE TABLE #fragreport(
fid int identity (1,1),
[timestamp] datetime default getdate(),
ObjectName sysname,
ObjectId int,
IndexName sysname,
IndexId int,
[Level] int,
Pages int,
[Rows] int,
MinimumRecordSize int,
MaximumRecordSize int,
AverageRecordSize float,
ForwardedRecords int,
Extents int,
ExtentSwitches int,
AverageFreeBytes float,
AveragePageDensity float,
ScanDensity float,
BestCount int,
ActualCount int,
LogicalFragmentation float,
ExtentFragmentation float,
DBName varchar(100) NULL,
PrePost varchar(20) NULL
)

CREATE TABLE #reindex(
rid int identity (1,1),
ObjectName sysname,
IndexName sysname
)

DECLARE @nrTables int,
@nrIndexes int,
@nrReIndexes int,
@iTable int,
@iIndex int,
@iReIndex int,
@thisTable int,
@tabname varchar(100),
@thisIndex int,
@indname varchar(100)

SET @iTable = 1

INSERT INTO #tables([tabid], [name])
EXEC ('SELECT [id], [name] FROM ' + @dbname + '.dbo.sysobjects WHERE
xtype = ''U'' and [name] <> ''dtproperties'' ' )


SELECT @nrTables = count(*) FROM #tables

WHILE @iTable <= @nrTables
BEGIN
SET @iIndex = 1

SELECT @thisTable = tabid,
@tabname = ltrim(rtrim([name]))
FROM #tables
WHERE rid = @iTable

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName],
[IndexId], [Level], [Pages], [Rows], [MinimumRecordSize],
[MaximumRecordSize], [AverageRecordSize], [ForwardedRecords],
[Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity],
[ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation],
[ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH
ALL_INDEXES, TABLERESULTS')

UPDATE #fragreport
SET PrePost = 'PRE'
WHERE PrePost is NULL

INSERT #indexes([indid], [name])
EXEC ('SELECT indid, [name] FROM ' + @dbname + '.dbo.sysindexes WHERE
[id] = ' + @thisTable + ' AND [name] not like ''_WA%'' AND indid NOT IN
(0, 255)')

SELECT @nrIndexes = count(*) FROM #indexes

WHILE @iIndex <= @nrIndexes
BEGIN
SELECT @thisIndex = indid,
@indname = LTRIM(RTRIM([name]))
FROM #indexes
WHERE rid = @iIndex

IF @isReportOnly = 0
DBCC INDEXDEFRAG (@dbname, @tabname, @indname)
ELSE
PRINT 'INDEXDEFRAG ' + @dbname + ', ' + ', ' + @tabname + ', ' +
@indname

SET @iIndex = @iIndex + 1
END

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName],
[IndexId], [Level], [Pages], [Rows], [MinimumRecordSize],
[MaximumRecordSize], [AverageRecordSize], [ForwardedRecords],
[Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity],
[ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation],
[ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH
ALL_INDEXES, TABLERESULTS')

UPDATE #fragreport
SET PrePost = 'POST'
WHERE PrePost is NULL

SET @iTable = @iTable + 1
TRUNCATE TABLE #indexes
END

INSERT INTO #reindex([ObjectName],[IndexName])
SELECT LTRIM(RTRIM(ObjectName)), LTRIM(RTRIM(IndexName))
FROM #fragreport
WHERE IndexId NOT IN (0, 255)
AND ScanDensity < 90
AND LogicalFragmentation > 10
AND PrePost = 'POST'

SELECT @nrReIndexes = COUNT(*) FROM #reindex

SET @iReIndex = 1

WHILE @iReIndex <= @nrReIndexes
BEGIN
SELECT @tabname = ObjectName,
@indname = IndexName
FROM #reindex
WHERE rid = @iReIndex

IF @isReportOnly = 0
EXEC('DBCC DBREINDEX([' + @dbname + '.dbo.' + @tabname + '],[' +
@indname + '])')
ELSE
PRINT 'DBCC DBREINDEX([' + @dbname + '.dbo.' + @tabname + '],[' +
@indname + '])'

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName],
[IndexId], [Level], [Pages], [Rows], [MinimumRecordSize],
[MaximumRecordSize], [AverageRecordSize], [ForwardedRecords],
[Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity],
[ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation],
[ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + '],[' +
@indname + ']) WITH TABLERESULTS')

SET @iReIndex = @iReIndex + 1
END

UPDATE #fragreport
SET PrePost = 'REINDEXED'
WHERE PrePost is NULL

UPDATE #fragreport
SET DBName = @dbname

IF @isReportOnly = 0
INSERT INTO DB_Rpt_Fragmentation ([timestamp], [ObjectName],
[ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows],
[MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize],
[ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes],
[AveragePageDensity], [ScanDensity], [BestCount], [ActualCount],
[LogicalFragmentation], [ExtentFragmentation], [DBName], [PrePost])
SELECT [timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId],
[Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize],
[AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches],
[AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount],
[ActualCount], [LogicalFragmentation], [ExtentFragmentation], [DBName],
[PrePost] FROM #fragreport
ELSE
BEGIN
PRINT '(Not logging table fragmentation summary : Displaying results)'
SELECT [timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId],
[Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize],
[AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches],
[AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount],
[ActualCount], [LogicalFragmentation], [ExtentFragmentation], [DBName],
[PrePost] FROM #fragreport
END

DROP TABLE #tables
DROP TABLE #indexes
DROP TABLE #fragreport
DROP TABLE #reindex

PRINT 'Index Maintenence complete. Job report in
[DB_Rpt_Fragmentation]'
GO

traceable1 wrote:

I have long wanted MS to separate deadlock priority from run priority.
If I have a big backround admin task, I would like to say that the run
priority is *low*, taking only idle cycles, but the deadlock priority
is very high, so that once the job begins, it will definitely finish at
some point.
Joe

Just to clarify: my transaction is being killed during the DBCC:
Defrag phase of index

Hi traceable1,

Is it possible to run this late at night as a scheduled job, or are the
applications constantly in use?

--
-Dick Christoph
"traceable1" <tracykc (AT) gmail (DOT) com> wrote

traceable1 (tracykc (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks for the input!

I run this job on the weekends in the evening, but we have several
other load jobs going on at the same time.
Of course I would not want a user to get this message, but like Joe
mentioned, it would be great if it could have low priority, but
continue to run.

What I think I'll try is Erland's recommendation to update my proc so I
can have it restart where it left off. Since the DBREINDEX requires
exclusive access, this will probably be my best bet. But it seems to
me that the DEFRAG also requires exclusive access, since the job is
running the DEFRAG when it's killed.
I actually have another version of this proc I use to run on instances
which have hundreds of databases and it stores the database name and
datetime of when the job ran successfully so it can pick up where it
left off if it fails. I'll just modify that one to do the same with
the tables.

I guess I should also analyze the fragmentation and adjust the storage
parameters to see if this will help.

Thank you so much!

traceable1 (tracykc (AT) gmail (DOT) com) writes:
OK, that makes sense.

Well, on SQL 2005 you could achieve this with the new TRY-CATCH handling.
Of course, the DEFRAG operation would be aborted, but the procedure
could continue to execute. On SQL 2000, however, a deadlock terminates
the batch, so you need cover up from somewhere outside SQL Server.

I think the likelyhood of getting deadlocks with DBREINDEX is small,
since it takes out one big fat table lock to keep everyone else out.

INDEXDEFRAG, on the other hand, is an online operation. Obviously, it
must still lock the pages it's currently working with. I'm not too
well versed with the internals of DBCC INDEXDEFRAG, but I could envision
that you get a deadlock, because the defragmenation collides with a
different process.

Again, this may work better in SQL 2005, where the online rebuild
makes use of the new snapshot technology. This permits most of the
defrag to be done without any blocking effect on users. (There is a
slight impact in the beginning and in the end.)


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx