ReIndex database but exclude a table

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

General SQL Server Forums

New to SQL Server Administration

ReIndex database but exclude a table

Author

Topic

dstpaul
Starting Member

10 Posts

Posted - 2011-04-19 : 12:01:14

I found a script to reindex tables in a database. I have corruption in one table only - table "abcdef" Until I solve the problem I need to have a nightly routing to reindex all tables exluding the offending table. I found this routine below! How can I modify it to skip table "abcdef"

USE database --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-19 : 12:03:45

are you using SQL SERVER 2000 ???

--------------------------
http://connectsql.blogspot.com/

dstpaul
Starting Member

10 Posts

Posted - 2011-04-19 : 12:17:50

Yes - as soon as I remove the corruption I will be moving to SQL 2008

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-19 : 12:24:14

Change:

DBCC DBREINDEX(@TableName,' ',90)

to:

IF @TableName<>'abcdef' DBCC DBREINDEX(@TableName,' ',90)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-20 : 03:48:51

Once you are migrated to SQL Server 2008, it would be better to use Tara's Stored Procedure, you can find it here
http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx
or you can use
http://connectsql.blogspot.com/2011/01/sql-server-simple-method-to-resolve-all.html

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-20 : 08:47:32

That is not a best practice to schedule these type of maintenance because it will create high Logs then if you have space issue then database will not execute DML even you have a SIMPLE MODEL of database

This script will not apply in SQL Server 2005 or Later

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-20 : 09:29:43

quote:
This script will not apply in SQL Server 2005 or Later

Um, what? Who's script are you talking about?

quote:
That is not a best practice to schedule these type of maintenance because it will create high Logs then if you have space issue then database will not execute DML even you have a SIMPLE MODEL of database

Regular index maintenance most certainly is a best practice. Log growth needs to be managed regardless of maintenance schedules or the lack thereof. A properly sized transaction log will not cause any issues.

If a DBREINDEX/REBUILD generates too much log traffic then DBCC INDEXDEFRAG is a viable alternative.

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-20 : 11:46:59

DBCC REINDEX use in SQL Server 2000 and if you are using SQL Server 2005 then you should use ALter index statement to rebuild or reorganize the index

Regular index maintenance most certainly is a best practice. Log growth needs to be managed regardless of maintenance schedules or the lack thereof. A properly sized transaction log will not cause any issues.

thats why I mentioned dont schedule maintenance just do maintenance with monitoring

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-20 : 11:51:00

quote:
thats why I mentioned dont schedule maintenance just do maintenance with monitoring

I don't understand your point at all.

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-20 : 11:54:01

If you schedule everything then there is no need of DBA

Regards,

Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-20 : 11:55:58