DBreindexing or indexdefrag

Question

DBreindexing or indexdefrag

Jasmina Patel

Mr or Mrs. 500

Points: 538
More actions
May 4, 2004 at 3:03 pm

#82575

I am new to SQL Server...but I have a test SAP database, which is huge. The stats on the database are not good. I am trying to reindex the database. I would like to defrag the database, but any time I execute either command to do this, the log file fills up and the commands error out and cannot complete. SQL Server requires me to backup the log file and restart the command. Then the process repeats, the log file fills up, I backup the log file and get an error that the log file is full. I have also tried to use Bulk logged mode but that fills the log file quicker. I am running SQL Server 2000. ANY info will help, PLEASE! Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Indexdefrag creates the smallest Log footprint between the two from what I have seen. However looks like you may have a space issue altogether. If you have a VLDB and a lot of free space in the DB you might want to shrink it then try again otherwise I would seriously consider more drives or move the LOG file to another array.

Xearo SSC-Addicted Points: 448 More actions · Answer 2

You can try adding more log files on the drives wherever you have available free space. Hopefully this will solve your problem.

The other way round (which is really cumbersome) is that you can drop the fragmented indexes. Then do a log shrink. Then recreate them again this might buy you some extra space you are looking for.

Best luck!!

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 3

Hi,

We have SAP on SQLServer too.

I have to disagree with Antares (first time. I apologize) but our experiences in that size of database shows that indexdefrag creates a larger log, then dbreindex. However you can run indexdefrag while your users are online.

The way we are doing is following:

Make a backup of your database
set the recovery mode to bulk logged
start dbreindex in batches started by the largest tables
in between you can trunc the log if you want
set back the recovery mode to full
make an another full backup

Bye
Gabor

Raino SSChasing Mays Points: 648 More actions · Answer 4

Tr to use this script as in the SQL Books Online (SQL 2000). In the SELECT @maxfrag = 5.0 parameter you specify the maximum fragementtaion allowed as per object.

This will automatically defrag all objects not confirming to @maxfrag and is very usefull to schedule automated DB defrags. It is however advisable to include a DBCC CheckDB('DBName',REPAIR_FAST) to make sure there is nothing wrong with DB Objects before attempting a defrag.

PS Have LOTS of space avaialble for the TLogbackup after running this. In most cases the TLOg backup is as big as the DB data device.

Script:

USE DBName

-- Declare variables

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow

SELECT @maxfrag = 5.0

-- Declare cursor

DECLARE tables CURSOR FOR

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

-- 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

-- 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

GO

KerryH SSChasing Mays Points: 632 More actions · Answer 5

In the past I've had to defrag tables on a server where there was not enough room on the disk to expand the database to a size which would allow for rebuilding a clustered index on a large table. I accomplished the task by BCPing out the table in sorted order because it had a clustered index, dropping the indexes on the table, truncating the table, BCPing in the data, rebuilding the clustered index with the SORTED_DATA option, and then rebuilding the other indexes. This allows for the least amount of logging and does not require a large amount of free space in the database.