Posted by DickChristoph on 03/09/06 23:55

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@gmail.com> wrote in message
news:1141933326.518327.292730@v46g2000cwv.googlegroups.com...
>
> 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]