DBCC DBReindex with "Sort In TembDB"

Question

DBCC DBReindex with "Sort In TembDB"

Jeff Moden

SSC Guru

Points: 1004436
More actions
May 7, 2009 at 7:51 am

#99686

First and to be sure, this is for an SQL Server 2000 Enterprise Edition.
One of the Systems DBA's that I work with recommended to another that we our conditional reindexing job should use the "Sort In TempDB" option to keep from blowing out the log files on individual databases. That would be a really cool thing to do but, Books Online doesn't say that option is available for DBCC DBReIndex nor can I find anywhere on the web that says it is an option. When I questioned the Systems DBA's about it, they simply ignored my question.
Personally, I don't believe the option exists for DBCC DBReIndex, but I've seen stranger omissions in BOL and even stranger undocumented features.
So my question is, can DBCC DBReIndex use the "Sort In TemDB" option and, if so, what is the syntax or configuration changes that I'd need to make?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

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

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 1

AFAIK - that option was made available in SQL Server 2005 when using the ALTER INDEX syntax. It is not available, even in 2005 using DBREINDEX.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Tara Kizer posted a stored proc on her blog for REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX) in SQL 2005. She has put a lot of work into working with the various options for 2005 to make it "production ready", so it is worth a look. You can get the code on the link below:

Defragmenting Indexes in SQL Server 2005:

http://weblogs.sqlteam.com/tarad/archive/2009/03/27/Defragmenting-Indexes-in-SQL-Server-2005Again.aspx

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 3

Thanks Jeff and Michael,

What I've found is that even if you use 2k5 tools, BOL states that DBCC REINDEX will be used and that the "Sort in TempDB" option will not take effect.

Looks like the SDBA's are a bit full of hooie.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Its an option under Create Index in sql 2000:

http://msdn.microsoft.com/en-us/library/aa174535(SQL.80).aspx

MJ

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 5

MANU (6/16/2009)
Its an option under Create Index in sql 2000:
http://msdn.microsoft.com/en-us/library/aa174535(SQL.80).aspx
MJ

Thanks Manu. What I was looking for, though, was a way to use DBCC DBReIndex with the sort-in-TempDB option. It doesn't exist in 2k databases (so far as I know) and the Create Index thing would only work if I decided to drop and recreate indexes instead of using DBCC DBReIndex. Lot'so pain there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)