need help with DBCC INDEXDEFRAG

need help with DBCC INDEXDEFRAG

Post by Andy Rittin » Tue, 12 Feb 2002 23:38:05



I was asked to defrag a table's index.  I tried to run DBCC INDEX DEFRAG on
a TABLE OWNED by a user called PD7333.  The command didn't like the
following error:

DBCC INDEXDEFRAG (JDE_PD7333, PD7333.F98950, F98950_2)
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

It doesn't like the object owner id.  but this command obviously won't work
either:

DBCC INDEXDEFRAG (JDE_PD7333, [F98950], F98950_2)

Server: Msg 2501, Level 16, State 45, Line 1
Could not find a table or object named 'F98950'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

I gues I have the option to use the object id for the table,  but WHY can't
I use the owner ID in the command?

 
 
 

need help with DBCC INDEXDEFRAG

Post by Paul S Randal [MS » Thu, 14 Feb 2002 01:25:54


Put quotes round the table name. Eg:

use master
go
create table t1 (c1 int)
create clustered index t1c1 on t1 (c1)
go
dbcc indexdefrag (master, 'dbo.t1', t1c1)
go

--
Paul Randal
DBCC, SQL Server Storage Engine
Microsoft, Redmond, WA

This posting is provided "AS IS" with no warranties, and confers no rights.

Quote:> I was asked to defrag a table's index.  I tried to run DBCC INDEX DEFRAG
on
> a TABLE OWNED by a user called PD7333.  The command didn't like the
> following error:

> DBCC INDEXDEFRAG (JDE_PD7333, PD7333.F98950, F98950_2)
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.

> It doesn't like the object owner id.  but this command obviously won't
work
> either:

> DBCC INDEXDEFRAG (JDE_PD7333, [F98950], F98950_2)

> Server: Msg 2501, Level 16, State 45, Line 1
> Could not find a table or object named 'F98950'. Check sysobjects.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.

> I gues I have the option to use the object id for the table,  but WHY
can't
> I use the owner ID in the command?


 
 
 

need help with DBCC INDEXDEFRAG

Post by Andy Rittin » Fri, 15 Feb 2002 04:33:25


Thanks!



> Put quotes round the table name. Eg:

> use master
> go
> create table t1 (c1 int)
> create clustered index t1c1 on t1 (c1)
> go
> dbcc indexdefrag (master, 'dbo.t1', t1c1)
> go

> --
> Paul Randal
> DBCC, SQL Server Storage Engine
> Microsoft, Redmond, WA

> This posting is provided "AS IS" with no warranties, and confers no
rights.


> > I was asked to defrag a table's index.  I tried to run DBCC INDEX DEFRAG
> on
> > a TABLE OWNED by a user called PD7333.  The command didn't like the
> > following error:

> > DBCC INDEXDEFRAG (JDE_PD7333, PD7333.F98950, F98950_2)
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '.'.

> > It doesn't like the object owner id.  but this command obviously won't
> work
> > either:

> > DBCC INDEXDEFRAG (JDE_PD7333, [F98950], F98950_2)

> > Server: Msg 2501, Level 16, State 45, Line 1
> > Could not find a table or object named 'F98950'. Check sysobjects.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.

> > I gues I have the option to use the object id for the table,  but WHY
> can't
> > I use the owner ID in the command?

 
 
 

1. DBCC INDEXDEFRAG and DBCC DBREINDEX - on SQL Server 2000

Books Online says under "DBCC INDEXDEFRAG" the following
      Unlike DBCC DBREINDEX (or the index building operation in general),
      DBCC INDEXDEFRAG is an online operation.

Yet the entry for "DBCC DBREINDEX" says nothing about being offline and it
can be run with users connected to and using the database.

I believe the latter would have a bigger performance impact than just the
defrag, but how much more?

What is meant by the "online" comment?

Thanks,
Deac

2. Onarchive restore time

3. DBCC DBReindex and DBCC INdexDefrag

4. READ THE WEB-SITE THAT THE KIKES TRY TO CENSOR

5. DBCC INDEXDEFRAG and DBCC DBREINDEX

6. 4GL JOB - P/A - Scottsdale, AZ

7. Capture Results of DBCC INDEXDEFRAG

8. dbcc indexdefrag not working???

9. DBCC INDEXDEFRAG

10. DBCC INDEXDEFRAG and MSMERGE_CONTENTS

11. dbcc inDEXDEFRAG

12. DBCC indexdefrag and showcontig