DBCC INDEXDEFRAG causing tran log growth

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DBCC INDEXDEFRAG causing tran log growth

Author

Topic

imarchenko
Yak Posting Veteran

57 Posts

Posted - 2006-10-03 : 17:44:31

Hello!

I have noticed that when executing DBCC INDEXDEFRAG on big table (SQL
Server 2000 SP4), transaction log starts growing significantly. I was hoping
that INDEXDEFRAG is implemented as a serious of short transactions and
shouldn't cause significant transaction log growth. Our database is in
SIMPLE recovery mode. We have the same database/defragmentation process
running on SQL Server 2005 and never experienced this problem.
Any thoughts on this matter are greatly appreciated.

Thanks,
Igor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-03 : 18:54:20

Just imagine what your tlog would look like if you ran DBCC DBREINDEX! Both require tlog space, but DBCC INDEXDEFRAG uses significantly less space.

Tara Kizer

Luis Martin
Yak Posting Veteran

54 Posts

Posted - 2006-10-03 : 20:26:29

One alternative is to defrag only those indexes fragmented.
Try this one script.

-- SP code starts here

/* uso: sp_defragment_indexes %defrag en decimal */

CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL

AS

/*

This stored procedure checks index fragmentation in a database and defragments
indexes whose scan densities fall below a specified threshold, @magfrag, which
is passed to the SP.

Must be run in the database to be defragmented.
*/
-- Declare variables

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname

SELECT @dbname = db_name()
IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
BEGIN
PRINT 'This procedure should not be run in system databases.'
RETURN
END

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT so.name
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

SELECT * FROM #fraglist

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexName, ScanDensity
FROM #fraglist
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstr = 'DBCC DBREINDEX (' + RTRIM(@tablename) + ',
' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT (@execstr)
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- Delete the temporary table
DROP TABLE #fraglist

GO

Kristen
Test

22859 Posts

Posted - 2006-10-04 : 02:07:36

Not sure where you got that from Luis - but I do think you should credit the source please.

This is almost identical and clearly originates from the same source, but more up to date (handles table owners etc.)

http://www.sql-server-performance.com/tp_automatic_reindexing.asp

It also explains that the Sproc should be created in Master, which is why it is named sp_xxx - which would be bad practice otherwise.

See also Tara's blog: http://weblogs.sqlteam.com/tarad/archive/2004/07/30/1834.aspx

Kristen

Luis Martin
Yak Posting Veteran

54 Posts

Posted - 2006-10-04 : 13:07:37

I get that exactly from the same source. But I used years ago so I copy and paste from my laptop.
Orginal is from Tom Pullen.

Also I modify original to spanish version.

All in Love is Fair
Stevie Wonder

imarchenko
Yak Posting Veteran

57 Posts

Posted - 2006-10-04 : 16:37:29

Thanks for your feedback, guys! My expectations were that since DBCC INDEXDEFRAG is implemented as series of short transactions and database is in SIMPLE recovery mode, transaction log shouldn't be growing significantly. I have a similar automated process that checks fragmentation level and decided whether to run DBCC DBREINDEX or DBCC INDEXDEFRAG.

Thanks,
Igor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources