Wednesday, March 7, 2012

DBCC DBREINDEX Help

DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
when the above given command is executed i get the result as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
instead of
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
how can i get the individual index id once they are reindexed?
pls respond me as soon as possible
Regards
Sudarshan SelvarajaWhy do you need the individual index ID? You specified an index so you
should know what it's ID is. In your case only that one index will be
rebuilt.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:136BB536-3A7D-462E-9FC0-9A714A40FF2C@.microsoft.com...
> DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
> when the above given command is executed i get the result as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> instead of
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> how can i get the individual index id once they are reindexed?
> pls respond me as soon as possible
> Regards
> Sudarshan Selvaraja
>
>|||Andrew,
He has taken the example from BOL. Coming to think of it, though I never
realised, I have never got any message when index is being rebuilt.
I do it for defrag once in a while, so use showcontig after I run this.
And, to answer your question. The following doesn't return any messages
either.
use northwind
DBCC DBREINDEX (Employees, '', 80)
And its got two indexes.|||To be honest I can't remember if it shows these messages or not. But if you
reindex a clustered index that is not unique it will have to rebuild all the
non-clustered indexes as well. This is due to the way it enforces uniqueness
on the clustered index in 2000. So what you see may depend on if the
clustered index is unique or not.
Andrew J. Kelly SQL MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
> Andrew,
> He has taken the example from BOL. Coming to think of it, though I never
> realised, I have never got any message when index is being rebuilt.
> I do it for defrag once in a while, so use showcontig after I run this.
> And, to answer your question. The following doesn't return any messages
> either.
> use northwind
> DBCC DBREINDEX (Employees, '', 80)
> And its got two indexes.|||In BOL they have mentioned that when DBCC DBREINDEX is executed the output
will be
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator
Actually i need this because when i reindex all the tables in the db i need
to know what are the index reindexed.
Regards
Sudarshan Selvaraja
"Andrew J. Kelly" wrote:

> To be honest I can't remember if it shows these messages or not. But if y
ou
> reindex a clustered index that is not unique it will have to rebuild all t
he
> non-clustered indexes as well. This is due to the way it enforces uniquene
ss
> on the clustered index in 2000. So what you see may depend on if the
> clustered index is unique or not.
> --
> Andrew J. Kelly SQL MVP
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
>
>|||USE Northwind --Enter the name of the database you want to reindex
go
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
When the above given code is executed i get the output as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I dont know exactly what are the index reindexed ...any suggestion pls
"sudarshan selvaraja" wrote:
> In BOL they have mentioned that when DBCC DBREINDEX is executed the output
> will be
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator
> Actually i need this because when i reindex all the tables in the db i nee
d
> to know what are the index reindexed.
> Regards
> Sudarshan Selvaraja
>
> "Andrew J. Kelly" wrote:
>|||This will reindex all of them. Do you really need to know which ones when
it is all of them? You can find out what indexes are on each table by
looking in sysindexes or sp_helpindex. You can also build a cursor based on
either and rebuild the indexes one at a time but why bother if you are going
to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
that will only reindex or Defrag indexes above a certain fragmentation
level. Maybe this is more of what you want.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
> USE Northwind --Enter the name of the database you want to reindex
> go
> 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
> When the above given code is executed i get the output as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I dont know exactly what are the index reindexed ...any suggestion pls
>
>
> "sudarshan selvaraja" wrote:
>|||Ok thx andrew i thought i could get the individual index id when it is
reindexed..any how it seems that i can use the code in BOL given with
showcontig..
"Andrew J. Kelly" wrote:

> This will reindex all of them. Do you really need to know which ones when
> it is all of them? You can find out what indexes are on each table by
> looking in sysindexes or sp_helpindex. You can also build a cursor based o
n
> either and rebuild the indexes one at a time but why bother if you are goi
ng
> to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
> that will only reindex or Defrag indexes above a certain fragmentation
> level. Maybe this is more of what you want.
> --
> Andrew J. Kelly SQL MVP
>
> "sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
> in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
>
>

No comments:

Post a Comment