dbcc dbrepair (drop database)

I need to drop a damaged database and BOL no longer provides the syntax for
dbcc dbrepair. Drop database doesn't work because it's being replicated.
The database is in a 'loading' state, but is not actually being restored.
When I issue drop database command it tells me it can't because it's being
replicated. This is true but I can't get rid of the publications because
it's in the 'loading' state.
Anyone know the syntax for dbcc dbrepair or how I can drop this database?
A database in loading status can't be a publisher. If it's a subscriber, you
should be able to drop it.
Any reason why it's in loading?
"Colleen" <Colleen@.discussions.microsoft.com> wrote in message
news:90A47C0C-F0B0-41E5-B58A-668A785B90FC@.microsoft.com...
> I need to drop a damaged database and BOL no longer provides the syntax
for
> dbcc dbrepair. Drop database doesn't work because it's being replicated.
> The database is in a 'loading' state, but is not actually being restored.
> When I issue drop database command it tells me it can't because it's being
> replicated. This is true but I can't get rid of the publications because
> it's in the 'loading' state.
> Anyone know the syntax for dbcc dbrepair or how I can drop this database?
>

dbcc dbrepair (drop database)

I need to drop a damaged database and BOL no longer provides the syntax for
dbcc dbrepair. Drop database doesn't work because it's being replicated.
The database is in a 'loading' state, but is not actually being restored.
When I issue drop database command it tells me it can't because it's being
replicated. This is true but I can't get rid of the publications because
it's in the 'loading' state.
Anyone know the syntax for dbcc dbrepair or how I can drop this database?A database in loading status can't be a publisher. If it's a subscriber, you
should be able to drop it.
Any reason why it's in loading?
"Colleen" <Colleen@.discussions.microsoft.com> wrote in message
news:90A47C0C-F0B0-41E5-B58A-668A785B90FC@.microsoft.com...
> I need to drop a damaged database and BOL no longer provides the syntax
for
> dbcc dbrepair. Drop database doesn't work because it's being replicated.
> The database is in a 'loading' state, but is not actually being restored.
> When I issue drop database command it tells me it can't because it's being
> replicated. This is true but I can't get rid of the publications because
> it's in the 'loading' state.
> Anyone know the syntax for dbcc dbrepair or how I can drop this database?
>

dbcc dbreindex and update of statistics

I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?
Index stats are updated automatically when you do a DBCC DBREINDEX.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.comcast.com. ..
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?
|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/ar...A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

dbcc dbreindex and update of statistics

I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?Index stats are updated automatically when you do a DBCC DBREINDEX.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.comcast.com...
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That looks like a great script..
I been trying to run dbcc show contig and then decide which ones need to
have dbcc redindex on them.
What the different from dbcc reindex and DBCC INDEXDEFRAG
is there a way to write this to a report so i can see which ones it does
during the process...thanks
"Tracy McKibben" wrote:
> Dodo Lurker wrote:
> > I have to set up a nightly job on my database to drop and reindex indexes
> > on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> > also update the statistics on the rebuilt index or do I have to then issue
> > an update statistics on the table?
> >
> >
> Consider using this script, it will rebuild only those indexes that are
> badly fragmented, and you can control the method used...
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

dbcc dbreindex and update of statistics

I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?Index stats are updated automatically when you do a DBCC DBREINDEX.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.co
mcast.com...
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreinde
x
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/a...realsqlguy.com|||That looks like a great script..
I been trying to run dbcc show contig and then decide which ones need to
have dbcc redindex on them.
What the different from dbcc reindex and DBCC INDEXDEFRAG
is there a way to write this to a report so i can see which ones it does
during the process...thanks
"Tracy McKibben" wrote:

> Dodo Lurker wrote:
> Consider using this script, it will rebuild only those indexes that are
> badly fragmented, and you can control the method used...
> http://realsqlguy.com/serendipity/a.....htm
l
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

dbcc dbreindex ...

Does it drop all indexes and recreate it under the hood ?
Hi,
Using DBCC DBREINDEX on a table is 100% equalent to use DROP and CREATE
Indexes. Page density will be restored based on the
fill facter associated. The only advantage in DBCC DBREINDEX command over
DROP and CREATE INDEX is : You do not have to know
anything about the underlying table structure.
Only people from SQL Server development team can answer your question
exactly.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Does it drop all indexes and recreate it under the hood ?
>
>
|||It actually recreates the new indexes first and then drops the original
ones. Of coarse it does all the proper renaming etc. for you as well. That
is why it requires 1.2 times the size of the index in free space in the data
files to operate.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Does it drop all indexes and recreate it under the hood ?
>
>
|||Actually there is one very big difference - drop/create does not guarantee
that any constraints defined are adhered to between the drop and the create
statement (e.g. key uniqueness). Doing a dbreindex does.
Also, if you're operating on a clustered index, drop and recreate is a
terrible choice - as all non-clustered indexes must be rebuilt when the
clustered index is dropped, and then rebuilt again when the clustered index
is re-created.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
> It actually recreates the new indexes first and then drops the original
> ones. Of coarse it does all the proper renaming etc. for you as well.
> That is why it requires 1.2 times the size of the index in free space in
> the data files to operate.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
>
|||A transaction would ensure the same consistency as drop/create though,
right?
And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the non-clustered
index rebuilds problem..
Regards,
Greg Linwood
SQL Server MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Actually there is one very big difference - drop/create does not guarantee
> that any constraints defined are adhered to between the drop and the
> create statement (e.g. key uniqueness). Doing a dbreindex does.
> Also, if you're operating on a clustered index, drop and recreate is a
> terrible choice - as all non-clustered indexes must be rebuilt when the
> clustered index is dropped, and then rebuilt again when the clustered
> index is re-created.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
>
|||That still would not deal with RI. If you use DROP and CREATE you have to
manually deal with the RI first and then afterwards. DBREINDEX takes care
of that for you. The DROP_EXISTING will still rebuild the non-clustered
indexes if the clustered index was being rebuilt and is not unique.
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uLUjg14iFHA.1444@.TK2MSFTNGP10.phx.gbl...
>A transaction would ensure the same consistency as drop/create though,
>right?
> And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the
> non-clustered index rebuilds problem..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
>
|||What do you mean about no dealing with RI? RI wasn't in the question, but RI
maintenance can also be scripted fairly easily. I agree about non-unique
non-clustered indexes though. DBREINDEX is definitely the way to go
currently (what I mostly do)
Regards,
Greg Linwood
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234e%23Fz5iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> That still would not deal with RI. If you use DROP and CREATE you have to
> manually deal with the RI first and then afterwards. DBREINDEX takes care
> of that for you. The DROP_EXISTING will still rebuild the non-clustered
> indexes if the clustered index was being rebuilt and is not unique.
> --
> Andrew J. Kelly SQL MVP
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uLUjg14iFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
|||Well the question was about DBREINDEX but then other methods popped up<g>.
The point I was trying to make was that often people forget about RI and
attempt to drop and recreate the index and will get an error. Then if they
do drop the RI first they must remember exactly what it was to redo when
done. DBREINDEX makes that a no brainier since you don't have to remember
anything.
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eZ8Idc%23iFHA.1444@.TK2MSFTNGP10.phx.gbl...
> What do you mean about no dealing with RI? RI wasn't in the question, but
> RI maintenance can also be scripted fairly easily. I agree about
> non-unique non-clustered indexes though. DBREINDEX is definitely the way
> to go currently (what I mostly do)
> Regards,
> Greg Linwood
> SQL Server MVP
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%234e%23Fz5iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>

dbcc dbreindex ...

Does it drop all indexes and recreate it under the hood ?Hi,
Using DBCC DBREINDEX on a table is 100% equalent to use DROP and CREATE
Indexes. Page density will be restored based on the
fill facter associated. The only advantage in DBCC DBREINDEX command over
DROP and CREATE INDEX is : You do not have to know
anything about the underlying table structure.
Only people from SQL Server development team can answer your question
exactly.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Does it drop all indexes and recreate it under the hood ?
>
>|||It actually recreates the new indexes first and then drops the original
ones. Of coarse it does all the proper renaming etc. for you as well. That
is why it requires 1.2 times the size of the index in free space in the data
files to operate.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Does it drop all indexes and recreate it under the hood ?
>
>|||Actually there is one very big difference - drop/create does not guarantee
that any constraints defined are adhered to between the drop and the create
statement (e.g. key uniqueness). Doing a dbreindex does.
Also, if you're operating on a clustered index, drop and recreate is a
terrible choice - as all non-clustered indexes must be rebuilt when the
clustered index is dropped, and then rebuilt again when the clustered index
is re-created.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
> It actually recreates the new indexes first and then drops the original
> ones. Of coarse it does all the proper renaming etc. for you as well.
> That is why it requires 1.2 times the size of the index in free space in
> the data files to operate.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Does it drop all indexes and recreate it under the hood ?
>>
>|||A transaction would ensure the same consistency as drop/create though,
right?
And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the non-clustered
index rebuilds problem..
Regards,
Greg Linwood
SQL Server MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Actually there is one very big difference - drop/create does not guarantee
> that any constraints defined are adhered to between the drop and the
> create statement (e.g. key uniqueness). Doing a dbreindex does.
> Also, if you're operating on a clustered index, drop and recreate is a
> terrible choice - as all non-clustered indexes must be rebuilt when the
> clustered index is dropped, and then rebuilt again when the clustered
> index is re-created.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> It actually recreates the new indexes first and then drops the original
>> ones. Of coarse it does all the proper renaming etc. for you as well.
>> That is why it requires 1.2 times the size of the index in free space in
>> the data files to operate.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Does it drop all indexes and recreate it under the hood ?
>>
>>
>|||That still would not deal with RI. If you use DROP and CREATE you have to
manually deal with the RI first and then afterwards. DBREINDEX takes care
of that for you. The DROP_EXISTING will still rebuild the non-clustered
indexes if the clustered index was being rebuilt and is not unique.
--
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uLUjg14iFHA.1444@.TK2MSFTNGP10.phx.gbl...
>A transaction would ensure the same consistency as drop/create though,
>right?
> And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the
> non-clustered index rebuilds problem..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
>> Actually there is one very big difference - drop/create does not
>> guarantee that any constraints defined are adhered to between the drop
>> and the create statement (e.g. key uniqueness). Doing a dbreindex does.
>> Also, if you're operating on a clustered index, drop and recreate is a
>> terrible choice - as all non-clustered indexes must be rebuilt when the
>> clustered index is dropped, and then rebuilt again when the clustered
>> index is re-created.
>> Thanks
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> It actually recreates the new indexes first and then drops the original
>> ones. Of coarse it does all the proper renaming etc. for you as well.
>> That is why it requires 1.2 times the size of the index in free space in
>> the data files to operate.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Does it drop all indexes and recreate it under the hood ?
>>
>>
>>
>|||What do you mean about no dealing with RI? RI wasn't in the question, but RI
maintenance can also be scripted fairly easily. I agree about non-unique
non-clustered indexes though. DBREINDEX is definitely the way to go
currently (what I mostly do)
Regards,
Greg Linwood
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234e%23Fz5iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> That still would not deal with RI. If you use DROP and CREATE you have to
> manually deal with the RI first and then afterwards. DBREINDEX takes care
> of that for you. The DROP_EXISTING will still rebuild the non-clustered
> indexes if the clustered index was being rebuilt and is not unique.
> --
> Andrew J. Kelly SQL MVP
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uLUjg14iFHA.1444@.TK2MSFTNGP10.phx.gbl...
>>A transaction would ensure the same consistency as drop/create though,
>>right?
>> And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the
>> non-clustered index rebuilds problem..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
>> Actually there is one very big difference - drop/create does not
>> guarantee that any constraints defined are adhered to between the drop
>> and the create statement (e.g. key uniqueness). Doing a dbreindex does.
>> Also, if you're operating on a clustered index, drop and recreate is a
>> terrible choice - as all non-clustered indexes must be rebuilt when the
>> clustered index is dropped, and then rebuilt again when the clustered
>> index is re-created.
>> Thanks
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> It actually recreates the new indexes first and then drops the original
>> ones. Of coarse it does all the proper renaming etc. for you as well.
>> That is why it requires 1.2 times the size of the index in free space
>> in the data files to operate.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Does it drop all indexes and recreate it under the hood ?
>>
>>
>>
>>
>|||Well the question was about DBREINDEX but then other methods popped up<g>.
The point I was trying to make was that often people forget about RI and
attempt to drop and recreate the index and will get an error. Then if they
do drop the RI first they must remember exactly what it was to redo when
done. DBREINDEX makes that a no brainier since you don't have to remember
anything.
--
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eZ8Idc%23iFHA.1444@.TK2MSFTNGP10.phx.gbl...
> What do you mean about no dealing with RI? RI wasn't in the question, but
> RI maintenance can also be scripted fairly easily. I agree about
> non-unique non-clustered indexes though. DBREINDEX is definitely the way
> to go currently (what I mostly do)
> Regards,
> Greg Linwood
> SQL Server MVP
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%234e%23Fz5iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> That still would not deal with RI. If you use DROP and CREATE you have to
>> manually deal with the RI first and then afterwards. DBREINDEX takes
>> care of that for you. The DROP_EXISTING will still rebuild the
>> non-clustered indexes if the clustered index was being rebuilt and is not
>> unique.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
>> news:uLUjg14iFHA.1444@.TK2MSFTNGP10.phx.gbl...
>>A transaction would ensure the same consistency as drop/create though,
>>right?
>> And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the
>> non-clustered index rebuilds problem..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
>> Actually there is one very big difference - drop/create does not
>> guarantee that any constraints defined are adhered to between the drop
>> and the create statement (e.g. key uniqueness). Doing a dbreindex does.
>> Also, if you're operating on a clustered index, drop and recreate is a
>> terrible choice - as all non-clustered indexes must be rebuilt when the
>> clustered index is dropped, and then rebuilt again when the clustered
>> index is re-created.
>> Thanks
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> It actually recreates the new indexes first and then drops the
>> original ones. Of coarse it does all the proper renaming etc. for you
>> as well. That is why it requires 1.2 times the size of the index in
>> free space in the data files to operate.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Does it drop all indexes and recreate it under the hood ?
>>
>>
>>
>>
>>
>

dbcc dbreindex ...

Does it drop all indexes and recreate it under the hood ?Hi,
Using DBCC DBREINDEX on a table is 100% equalent to use DROP and CREATE
Indexes. Page density will be restored based on the
fill facter associated. The only advantage in DBCC DBREINDEX command over
DROP and CREATE INDEX is : You do not have to know
anything about the underlying table structure.
Only people from SQL Server development team can answer your question
exactly.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Does it drop all indexes and recreate it under the hood ?
>
>|||It actually recreates the new indexes first and then drops the original
ones. Of coarse it does all the proper renaming etc. for you as well. That
is why it requires 1.2 times the size of the index in free space in the data
files to operate.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Does it drop all indexes and recreate it under the hood ?
>
>|||Actually there is one very big difference - drop/create does not guarantee
that any constraints defined are adhered to between the drop and the create
statement (e.g. key uniqueness). Doing a dbreindex does.
Also, if you're operating on a clustered index, drop and recreate is a
terrible choice - as all non-clustered indexes must be rebuilt when the
clustered index is dropped, and then rebuilt again when the clustered index
is re-created.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
> It actually recreates the new indexes first and then drops the original
> ones. Of coarse it does all the proper renaming etc. for you as well.
> That is why it requires 1.2 times the size of the index in free space in
> the data files to operate.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
>|||A transaction would ensure the same consistency as drop/create though,
right?
And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the non-clustered
index rebuilds problem..
Regards,
Greg Linwood
SQL Server MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Actually there is one very big difference - drop/create does not guarantee
> that any constraints defined are adhered to between the drop and the
> create statement (e.g. key uniqueness). Doing a dbreindex does.
> Also, if you're operating on a clustered index, drop and recreate is a
> terrible choice - as all non-clustered indexes must be rebuilt when the
> clustered index is dropped, and then rebuilt again when the clustered
> index is re-created.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
>|||That still would not deal with RI. If you use DROP and CREATE you have to
manually deal with the RI first and then afterwards. DBREINDEX takes care
of that for you. The DROP_EXISTING will still rebuild the non-clustered
indexes if the clustered index was being rebuilt and is not unique.
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uLUjg14iFHA.1444@.TK2MSFTNGP10.phx.gbl...
>A transaction would ensure the same consistency as drop/create though,
>right?
> And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the
> non-clustered index rebuilds problem..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
>|||What do you mean about no dealing with RI? RI wasn't in the question, but RI
maintenance can also be scripted fairly easily. I agree about non-unique
non-clustered indexes though. DBREINDEX is definitely the way to go
currently (what I mostly do)
Regards,
Greg Linwood
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234e%23Fz5iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> That still would not deal with RI. If you use DROP and CREATE you have to
> manually deal with the RI first and then afterwards. DBREINDEX takes care
> of that for you. The DROP_EXISTING will still rebuild the non-clustered
> indexes if the clustered index was being rebuilt and is not unique.
> --
> Andrew J. Kelly SQL MVP
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uLUjg14iFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||Well the question was about DBREINDEX but then other methods popped up<g>.
The point I was trying to make was that often people forget about RI and
attempt to drop and recreate the index and will get an error. Then if they
do drop the RI first they must remember exactly what it was to redo when
done. DBREINDEX makes that a no brainier since you don't have to remember
anything.
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eZ8Idc%23iFHA.1444@.TK2MSFTNGP10.phx.gbl...
> What do you mean about no dealing with RI? RI wasn't in the question, but
> RI maintenance can also be scripted fairly easily. I agree about
> non-unique non-clustered indexes though. DBREINDEX is definitely the way
> to go currently (what I mostly do)
> Regards,
> Greg Linwood
> SQL Server MVP
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%234e%23Fz5iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>

DBCC DBREINDEX

After we run a dbcc dbreindex for a specific table, we have noticed a huge
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
ThxThis is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx