|
|
Posted by traceable1 on 03/09/06 21:42
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!
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
[Back to original message]
|