Date parsed: 22/10/2007 01:47:23
Date: Mon, 22 Oct 2007 08:47:23 -0700
I suggest you have a look at this first:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Johnny" <Johnny@discussions.microsoft.com> wrote in message
news:B802E1F3-5C51-4F68-AC43-CAFB65F434F7@microsoft.com...
> What is the recovery mode should I set to while running Indexdefrag ?
> Should
> I set it to either Full or Bulk-Logged which I may have change to backup
> transaction log to free up spaces ?
>
> What is about DBCC dbreindex ? Does it create lot of data to the
> transaction log if running simple recovery mode ?
> (If DBReindex recycle the log for simple recovery mode, so it has
> advantage
> over Indexdefrag). in my case anyway....
>
>
> "Andrew J. Kelly" wrote:
>
>> DBCC INDEXDEFRAG is always a fully logged operation regardless of the
>> recovery mode and can log a lot of data to the transaction log. If
>> somewhere you had an open transaction that would prevent the log from
>> truncating. Also if the db was never changed to Simple mode it would
>> continue to fill up.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>>
>> "Johnny" <Johnny@discussions.microsoft.com> wrote in message
>> news:EA998E0D-2A88-4F63-B0C1-258BE2326D03@microsoft.com...
>> >I ran the following procedure which supplied by sql books-online to
>> >defrag
>> > indexes which the fragmentation is > 15%. The procedure will set the
>> > database in a simple recovery mode. I am not sure why the transaction
>> > log
>> > kept expanding until it ran out of disk space and failed.
>> >
>> > Any ideas/suggestions is appreciated. Thanks.
>> >
>> > USE XXXXX
>> > SET NOCOUNT ON
>> > DECLARE @sdbname varchar(25)
>> > set @sdbname = 'XXXXX'
>> > -- set dboption to trunc on checkpoint
>> > exec sp_dboption @dbname = @sdbname, @optname = 'trunc. log on chkpt.',
>> > @optvalue = 'true'
>> > DECLARE @tablename VARCHAR (128)
>> > DECLARE @owner VARCHAR (128)
>> > DECLARE @execstr VARCHAR (255)
>> > DECLARE @objectid INT
>> > DECLARE @indexid INT
>> > DECLARE @frag DECIMAL
>> > DECLARE @maxfrag DECIMAL
>> >
>> > set @execstr = 'use '+ @sdbname
>> > EXEC (@execstr)
>> >
>> > -- Decide on the maximum fragmentation to allow
>> > SELECT @maxfrag = 15.0
>> >
>> > -- Declare cursor
>> > DECLARE tables CURSOR FOR
>> > select a.name,b.name owner from sysobjects a,sysusers b
>> > where a.uid = b.uid and a.xtype = 'U'
>> >
>> > -- 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,@owner
>> >
>> > WHILE @@FETCH_STATUS = 0
>> > BEGIN
>> > -- Do the showcontig of all indexes of the table
>> > INSERT INTO #fraglist
>> > EXEC ('DBCC SHOWCONTIG (''' + @owner+'.'+@tablename + ''')
>> > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
>> > FETCH NEXT
>> > FROM tables
>> > INTO @tablename,@owner
>> > END
>> >
>> > -- Close and deallocate the cursor
>> > CLOSE tables
>> > DEALLOCATE tables
>> >
>> > -- Declare cursor for list of indexes to be defragged
>> > DECLARE indexes CURSOR FOR
>> > SELECT ObjectName, ObjectId, IndexId, LogicalFrag
>> > FROM #fraglist
>> > WHERE LogicalFrag >= @maxfrag
>> > AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
>> >
>> > -- Open the cursor
>> > OPEN indexes
>> >
>> > -- loop through the indexes
>> > FETCH NEXT
>> > FROM indexes
>> > INTO @tablename, @objectid, @indexid, @frag
>> >
>> > WHILE @@FETCH_STATUS = 0
>> > BEGIN
>> > PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
>> > ' + RTRIM(@indexid) + ') - fragmentation currently '
>> > + RTRIM(CONVERT(varchar(15),@frag)) + '%'
>> > SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
>> > ' + RTRIM(@indexid) + ')'
>> > EXEC (@execstr)
>> >
>> > FETCH NEXT
>> > FROM indexes
>> > INTO @tablename, @objectid, @indexid, @frag
>> > END
>> >
>> > -- Close and deallocate the cursor
>> > CLOSE indexes
>> > DEALLOCATE indexes
>> >
>> > -- Delete the temporary table
>> > DROP TABLE #fraglist
>> >
>> > DBCC UPDATEUSAGE(0) -- update statistics of current database
>> > exec sp_dboption @dbname = @sdbname, @optname = 'trunc. log on chkpt.',
>> > @optvalue = 'false'
>> > GO
>> >
>>
>>
