DBfile usage: scripts

Showing posts with label scripts. Show all posts

Thursday, March 8, 2012

DBCC error 913

Hi
I am receiving following error while running DBCC through scripts on my
database.
Server: Msg 913, Level 16, State 10, Line 1
Could not find database ID 1128543719. Database may not be activated yet or
may be in transition.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Microsoft KB says that it will be rectified by the installing SQL SP3, but
my SQL server is already having SP3 loaded.
Previously only one database was showing this message, now another databases
is starting showing the same.
When I run DBCC manully i am not receiving any such message.
Nedd help.Does the procedure runs whenever SQL Server starts? if so than it's possible
that the database is in recovery state.
If you are still having problems than please post the following information:
1) Script
2) Where/when are you executing this script
3) What is the database doing during that time.
4) Post SP_Who2 snapshot as well.
Also, I would suggest run SQL Profiler and see what's going on.
HTH
Saleem@.sqlnt.com
"Satyaki" wrote:
> Hi
> I am receiving following error while running DBCC through scripts on my
> database.
> Server: Msg 913, Level 16, State 10, Line 1
> Could not find database ID 1128543719. Database may not be activated yet or
> may be in transition.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> Microsoft KB says that it will be rectified by the installing SQL SP3, but
> my SQL server is already having SP3 loaded.
> Previously only one database was showing this message, now another databases
> is starting showing the same.
> When I run DBCC manully i am not receiving any such message.
> Nedd help.|||Hi
Thanks for the response.
The script run on schedule.
There is absolutely no problem with the scripts.
This is simple DBCC script I am running on the server every sunday. The
database is online and in live. I can't provide the scripts, but the script
uses regular DBCC checkdb, DBCC checkalloc and DBCC checkcatalog commands on
each database.
As DBCC runs on single user mode, at that time the database is only used by
the DBCC script.
I can't provide sp_who2 as it run late in night.
I think this much information will help you.
"Saleem Hakani" wrote:
> Does the procedure runs whenever SQL Server starts? if so than it's possible
> that the database is in recovery state.
> If you are still having problems than please post the following information:
> 1) Script
> 2) Where/when are you executing this script
> 3) What is the database doing during that time.
> 4) Post SP_Who2 snapshot as well.
> Also, I would suggest run SQL Profiler and see what's going on.
> HTH
> Saleem@.sqlnt.com
> "Satyaki" wrote:
> > Hi
> > I am receiving following error while running DBCC through scripts on my
> > database.
> >
> > Server: Msg 913, Level 16, State 10, Line 1
> > Could not find database ID 1128543719. Database may not be activated yet or
> > may be in transition.
> > Server: Msg 5069, Level 16, State 1, Line 1
> > ALTER DATABASE statement failed.
> >
> > Microsoft KB says that it will be rectified by the installing SQL SP3, but
> > my SQL server is already having SP3 loaded.
> >
> > Previously only one database was showing this message, now another databases
> > is starting showing the same.
> >
> > When I run DBCC manully i am not receiving any such message.
> > Nedd help.|||I assume that you use ALTER DATABASE to set in single user mode... Can you post that command? What
rollback options are you using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:7362432E-A3EA-440E-B0E7-4E6AC29B60C8@.microsoft.com...
> Hi
> Thanks for the response.
> The script run on schedule.
> There is absolutely no problem with the scripts.
> This is simple DBCC script I am running on the server every sunday. The
> database is online and in live. I can't provide the scripts, but the script
> uses regular DBCC checkdb, DBCC checkalloc and DBCC checkcatalog commands on
> each database.
> As DBCC runs on single user mode, at that time the database is only used by
> the DBCC script.
> I can't provide sp_who2 as it run late in night.
> I think this much information will help you.
> "Saleem Hakani" wrote:
>> Does the procedure runs whenever SQL Server starts? if so than it's possible
>> that the database is in recovery state.
>> If you are still having problems than please post the following information:
>> 1) Script
>> 2) Where/when are you executing this script
>> 3) What is the database doing during that time.
>> 4) Post SP_Who2 snapshot as well.
>> Also, I would suggest run SQL Profiler and see what's going on.
>> HTH
>> Saleem@.sqlnt.com
>> "Satyaki" wrote:
>> > Hi
>> > I am receiving following error while running DBCC through scripts on my
>> > database.
>> >
>> > Server: Msg 913, Level 16, State 10, Line 1
>> > Could not find database ID 1128543719. Database may not be activated yet or
>> > may be in transition.
>> > Server: Msg 5069, Level 16, State 1, Line 1
>> > ALTER DATABASE statement failed.
>> >
>> > Microsoft KB says that it will be rectified by the installing SQL SP3, but
>> > my SQL server is already having SP3 loaded.
>> >
>> > Previously only one database was showing this message, now another databases
>> > is starting showing the same.
>> >
>> > When I run DBCC manully i am not receiving any such message.
>> > Nedd help.|||Hi
I had written a script which take one database at a time and alter it to
single user mode and perform DBCC checking.
I am using sp_dboption dbname,'single user',true command.
There is no explicit rollback option set for performing DBCC.
"Tibor Karaszi" wrote:
> I assume that you use ALTER DATABASE to set in single user mode... Can you post that command? What
> rollback options are you using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> news:7362432E-A3EA-440E-B0E7-4E6AC29B60C8@.microsoft.com...
> > Hi
> > Thanks for the response.
> >
> > The script run on schedule.
> > There is absolutely no problem with the scripts.
> > This is simple DBCC script I am running on the server every sunday. The
> > database is online and in live. I can't provide the scripts, but the script
> > uses regular DBCC checkdb, DBCC checkalloc and DBCC checkcatalog commands on
> > each database.
> > As DBCC runs on single user mode, at that time the database is only used by
> > the DBCC script.
> > I can't provide sp_who2 as it run late in night.
> >
> > I think this much information will help you.
> > "Saleem Hakani" wrote:
> >
> >> Does the procedure runs whenever SQL Server starts? if so than it's possible
> >> that the database is in recovery state.
> >>
> >> If you are still having problems than please post the following information:
> >>
> >> 1) Script
> >> 2) Where/when are you executing this script
> >> 3) What is the database doing during that time.
> >> 4) Post SP_Who2 snapshot as well.
> >>
> >> Also, I would suggest run SQL Profiler and see what's going on.
> >>
> >> HTH
> >> Saleem@.sqlnt.com
> >>
> >> "Satyaki" wrote:
> >>
> >> > Hi
> >> > I am receiving following error while running DBCC through scripts on my
> >> > database.
> >> >
> >> > Server: Msg 913, Level 16, State 10, Line 1
> >> > Could not find database ID 1128543719. Database may not be activated yet or
> >> > may be in transition.
> >> > Server: Msg 5069, Level 16, State 1, Line 1
> >> > ALTER DATABASE statement failed.
> >> >
> >> > Microsoft KB says that it will be rectified by the installing SQL SP3, but
> >> > my SQL server is already having SP3 loaded.
> >> >
> >> > Previously only one database was showing this message, now another databases
> >> > is starting showing the same.
> >> >
> >> > When I run DBCC manully i am not receiving any such message.
> >> > Nedd help.
>
>|||Hi
Sorry, i had given only half of the details you asked for, yes I am using
alter database command
that is
alter database ' + convert(varchar,@.dbname ) + ' set single_user with
ROLLBACK IMMEDIATE
Then I run the command sp_dboption dbname,'Single user',true also.
this command runs inside a cursor and DBCC checkdb commands starts after that.
"Tibor Karaszi" wrote:
> I assume that you use ALTER DATABASE to set in single user mode... Can you post that command? What
> rollback options are you using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> news:7362432E-A3EA-440E-B0E7-4E6AC29B60C8@.microsoft.com...
> > Hi
> > Thanks for the response.
> >
> > The script run on schedule.
> > There is absolutely no problem with the scripts.
> > This is simple DBCC script I am running on the server every sunday. The
> > database is online and in live. I can't provide the scripts, but the script
> > uses regular DBCC checkdb, DBCC checkalloc and DBCC checkcatalog commands on
> > each database.
> > As DBCC runs on single user mode, at that time the database is only used by
> > the DBCC script.
> > I can't provide sp_who2 as it run late in night.
> >
> > I think this much information will help you.
> > "Saleem Hakani" wrote:
> >
> >> Does the procedure runs whenever SQL Server starts? if so than it's possible
> >> that the database is in recovery state.
> >>
> >> If you are still having problems than please post the following information:
> >>
> >> 1) Script
> >> 2) Where/when are you executing this script
> >> 3) What is the database doing during that time.
> >> 4) Post SP_Who2 snapshot as well.
> >>
> >> Also, I would suggest run SQL Profiler and see what's going on.
> >>
> >> HTH
> >> Saleem@.sqlnt.com
> >>
> >> "Satyaki" wrote:
> >>
> >> > Hi
> >> > I am receiving following error while running DBCC through scripts on my
> >> > database.
> >> >
> >> > Server: Msg 913, Level 16, State 10, Line 1
> >> > Could not find database ID 1128543719. Database may not be activated yet or
> >> > may be in transition.
> >> > Server: Msg 5069, Level 16, State 1, Line 1
> >> > ALTER DATABASE statement failed.
> >> >
> >> > Microsoft KB says that it will be rectified by the installing SQL SP3, but
> >> > my SQL server is already having SP3 loaded.
> >> >
> >> > Previously only one database was showing this message, now another databases
> >> > is starting showing the same.
> >> >
> >> > When I run DBCC manully i am not receiving any such message.
> >> > Nedd help.
>
>|||Several questions:
1) why are you setting the db to single-user mode at all? CHECKDB runs
online. I can only assume you're running repair without looking at any
problems that may be found - this is a very bad idea.
2) You don't need to run DBCC CHECKALLOC as well as DBCC CHECKDB - CHECKDB
incorporates all the functionality of CHECKALLOC
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:0B799A97-4BAE-450B-82C8-6A5BD4B1D247@.microsoft.com...
> Hi
> Sorry, i had given only half of the details you asked for, yes I am using
> alter database command
> that is
> alter database ' + convert(varchar,@.dbname ) + ' set single_user with
> ROLLBACK IMMEDIATE
> Then I run the command sp_dboption dbname,'Single user',true also.
> this command runs inside a cursor and DBCC checkdb commands starts after
that.
> "Tibor Karaszi" wrote:
> > I assume that you use ALTER DATABASE to set in single user mode... Can
you post that command? What
> > rollback options are you using?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> > news:7362432E-A3EA-440E-B0E7-4E6AC29B60C8@.microsoft.com...
> > > Hi
> > > Thanks for the response.
> > >
> > > The script run on schedule.
> > > There is absolutely no problem with the scripts.
> > > This is simple DBCC script I am running on the server every sunday.
The
> > > database is online and in live. I can't provide the scripts, but the
script
> > > uses regular DBCC checkdb, DBCC checkalloc and DBCC checkcatalog
commands on
> > > each database.
> > > As DBCC runs on single user mode, at that time the database is only
used by
> > > the DBCC script.
> > > I can't provide sp_who2 as it run late in night.
> > >
> > > I think this much information will help you.
> > > "Saleem Hakani" wrote:
> > >
> > >> Does the procedure runs whenever SQL Server starts? if so than it's
possible
> > >> that the database is in recovery state.
> > >>
> > >> If you are still having problems than please post the following
information:
> > >>
> > >> 1) Script
> > >> 2) Where/when are you executing this script
> > >> 3) What is the database doing during that time.
> > >> 4) Post SP_Who2 snapshot as well.
> > >>
> > >> Also, I would suggest run SQL Profiler and see what's going on.
> > >>
> > >> HTH
> > >> Saleem@.sqlnt.com
> > >>
> > >> "Satyaki" wrote:
> > >>
> > >> > Hi
> > >> > I am receiving following error while running DBCC through scripts
on my
> > >> > database.
> > >> >
> > >> > Server: Msg 913, Level 16, State 10, Line 1
> > >> > Could not find database ID 1128543719. Database may not be
activated yet or
> > >> > may be in transition.
> > >> > Server: Msg 5069, Level 16, State 1, Line 1
> > >> > ALTER DATABASE statement failed.
> > >> >
> > >> > Microsoft KB says that it will be rectified by the installing SQL
SP3, but
> > >> > my SQL server is already having SP3 loaded.
> > >> >
> > >> > Previously only one database was showing this message, now another
databases
> > >> > is starting showing the same.
> > >> >
> > >> > When I run DBCC manully i am not receiving any such message.
> > >> > Nedd help.
> >
> >
> >|||Thank you Paul
To answer your queries:
1. Yes, I am running DBCC Checkdb with repair_rebuild option. I will
definitly try to change the same.
2. I will check that.
But why this error is comming?, is it a bug, because when i run DBCC checkdb
on the database manually it is not giving any error. Can you help me?
Regards
"Paul S Randal [MS]" wrote:
> Several questions:
> 1) why are you setting the db to single-user mode at all? CHECKDB runs
> online. I can only assume you're running repair without looking at any
> problems that may be found - this is a very bad idea.
> 2) You don't need to run DBCC CHECKALLOC as well as DBCC CHECKDB - CHECKDB
> incorporates all the functionality of CHECKALLOC
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> news:0B799A97-4BAE-450B-82C8-6A5BD4B1D247@.microsoft.com...
> > Hi
> > Sorry, i had given only half of the details you asked for, yes I am using
> > alter database command
> > that is
> > alter database ' + convert(varchar,@.dbname ) + ' set single_user with
> > ROLLBACK IMMEDIATE
> > Then I run the command sp_dboption dbname,'Single user',true also.
> >
> > this command runs inside a cursor and DBCC checkdb commands starts after
> that.
> >
> > "Tibor Karaszi" wrote:
> >
> > > I assume that you use ALTER DATABASE to set in single user mode... Can
> you post that command? What
> > > rollback options are you using?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> > > news:7362432E-A3EA-440E-B0E7-4E6AC29B60C8@.microsoft.com...
> > > > Hi
> > > > Thanks for the response.
> > > >
> > > > The script run on schedule.
> > > > There is absolutely no problem with the scripts.
> > > > This is simple DBCC script I am running on the server every sunday.
> The
> > > > database is online and in live. I can't provide the scripts, but the
> script
> > > > uses regular DBCC checkdb, DBCC checkalloc and DBCC checkcatalog
> commands on
> > > > each database.
> > > > As DBCC runs on single user mode, at that time the database is only
> used by
> > > > the DBCC script.
> > > > I can't provide sp_who2 as it run late in night.
> > > >
> > > > I think this much information will help you.
> > > > "Saleem Hakani" wrote:
> > > >
> > > >> Does the procedure runs whenever SQL Server starts? if so than it's
> possible
> > > >> that the database is in recovery state.
> > > >>
> > > >> If you are still having problems than please post the following
> information:
> > > >>
> > > >> 1) Script
> > > >> 2) Where/when are you executing this script
> > > >> 3) What is the database doing during that time.
> > > >> 4) Post SP_Who2 snapshot as well.
> > > >>
> > > >> Also, I would suggest run SQL Profiler and see what's going on.
> > > >>
> > > >> HTH
> > > >> Saleem@.sqlnt.com
> > > >>
> > > >> "Satyaki" wrote:
> > > >>
> > > >> > Hi
> > > >> > I am receiving following error while running DBCC through scripts
> on my
> > > >> > database.
> > > >> >
> > > >> > Server: Msg 913, Level 16, State 10, Line 1
> > > >> > Could not find database ID 1128543719. Database may not be
> activated yet or
> > > >> > may be in transition.
> > > >> > Server: Msg 5069, Level 16, State 1, Line 1
> > > >> > ALTER DATABASE statement failed.
> > > >> >
> > > >> > Microsoft KB says that it will be rectified by the installing SQL
> SP3, but
> > > >> > my SQL server is already having SP3 loaded.
> > > >> >
> > > >> > Previously only one database was showing this message, now another
> databases
> > > >> > is starting showing the same.
> > > >> >
> > > >> > When I run DBCC manully i am not receiving any such message.
> > > >> > Nedd help.
> > >
> > >
> > >
>
>|||Something's definitely screwed up as database IDs can't go that high. I
suggest you contact Product Support (http://support.microsoft.com)
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:BF60B134-A8E3-4CB6-9F01-E7D1F50FA79C@.microsoft.com...
> Thank you Paul
> To answer your queries:
> 1. Yes, I am running DBCC Checkdb with repair_rebuild option. I will
> definitly try to change the same.
> 2. I will check that.
> But why this error is comming?, is it a bug, because when i run DBCC
checkdb
> on the database manually it is not giving any error. Can you help me?
> Regards
>
> "Paul S Randal [MS]" wrote:
> > Several questions:
> > 1) why are you setting the db to single-user mode at all? CHECKDB runs
> > online. I can only assume you're running repair without looking at any
> > problems that may be found - this is a very bad idea.
> > 2) You don't need to run DBCC CHECKALLOC as well as DBCC CHECKDB -
CHECKDB
> > incorporates all the functionality of CHECKALLOC
> >
> > Thanks
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> > news:0B799A97-4BAE-450B-82C8-6A5BD4B1D247@.microsoft.com...
> > > Hi
> > > Sorry, i had given only half of the details you asked for, yes I am
using
> > > alter database command
> > > that is
> > > alter database ' + convert(varchar,@.dbname ) + ' set single_user with
> > > ROLLBACK IMMEDIATE
> > > Then I run the command sp_dboption dbname,'Single user',true also.
> > >
> > > this command runs inside a cursor and DBCC checkdb commands starts
after
> > that.
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > I assume that you use ALTER DATABASE to set in single user mode...
Can
> > you post that command? What
> > > > rollback options are you using?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> > > > news:7362432E-A3EA-440E-B0E7-4E6AC29B60C8@.microsoft.com...
> > > > > Hi
> > > > > Thanks for the response.
> > > > >
> > > > > The script run on schedule.
> > > > > There is absolutely no problem with the scripts.
> > > > > This is simple DBCC script I am running on the server every
sunday.
> > The
> > > > > database is online and in live. I can't provide the scripts, but
the
> > script
> > > > > uses regular DBCC checkdb, DBCC checkalloc and DBCC checkcatalog
> > commands on
> > > > > each database.
> > > > > As DBCC runs on single user mode, at that time the database is
only
> > used by
> > > > > the DBCC script.
> > > > > I can't provide sp_who2 as it run late in night.
> > > > >
> > > > > I think this much information will help you.
> > > > > "Saleem Hakani" wrote:
> > > > >
> > > > >> Does the procedure runs whenever SQL Server starts? if so than
it's
> > possible
> > > > >> that the database is in recovery state.
> > > > >>
> > > > >> If you are still having problems than please post the following
> > information:
> > > > >>
> > > > >> 1) Script
> > > > >> 2) Where/when are you executing this script
> > > > >> 3) What is the database doing during that time.
> > > > >> 4) Post SP_Who2 snapshot as well.
> > > > >>
> > > > >> Also, I would suggest run SQL Profiler and see what's going on.
> > > > >>
> > > > >> HTH
> > > > >> Saleem@.sqlnt.com
> > > > >>
> > > > >> "Satyaki" wrote:
> > > > >>
> > > > >> > Hi
> > > > >> > I am receiving following error while running DBCC through
scripts
> > on my
> > > > >> > database.
> > > > >> >
> > > > >> > Server: Msg 913, Level 16, State 10, Line 1
> > > > >> > Could not find database ID 1128543719. Database may not be
> > activated yet or
> > > > >> > may be in transition.
> > > > >> > Server: Msg 5069, Level 16, State 1, Line 1
> > > > >> > ALTER DATABASE statement failed.
> > > > >> >
> > > > >> > Microsoft KB says that it will be rectified by the installing
SQL
> > SP3, but
> > > > >> > my SQL server is already having SP3 loaded.
> > > > >> >
> > > > >> > Previously only one database was showing this message, now
another
> > databases
> > > > >> > is starting showing the same.
> > > > >> >
> > > > >> > When I run DBCC manully i am not receiving any such message.
> > > > >> > Nedd help.
> > > >
> > > >
> > > >
> >
> >
> >

Wednesday, March 7, 2012

DBCC DBREINDEX versus INDEXDEFRAG.

(SQL Server 2000, SP3a)
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John PetersonAs my understanding goes, it is better to go for DBCC DBREINDEX command, as it takes care of all the table and view indexes, and any constraints on the columns are automatically taken care of. One can use, DBCC INDEXDEFRAG, if the indexes needs to be used on-line, which allows any DML operations.
So if locking of rows during index rebuliding is not a criteria, I guess you can go with DBCC DBREINDEX.
Am not sure, if I was able to answer yur query.
Thanks
GYK
-- John Peterson wrote: --
(SQL Server 2000, SP3a)
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John Peterson|||John
BOL says:
Unlike DBCC DBREINDEX (or the index building operation in general), DBCC
INDEXDEFRAG is an online operation. It does not hold locks long term and
thus will not block running queries or updates. A relatively unfragmented
index can be defragmented faster than a new index can be built because the
time to defragment is related to the amount of fragmentation. A very
fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged,
regardless of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the
disk because INDEXDEFRAG shuffles the pages in place. To improve the
clustering of pages, rebuild the index.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Thanks, Gert!
The way I read this article, it sounds as if these two techniques *are* different, and
like many things, it depends on your system.
The article seems to suggest that rebuilding the index (DBCC DBREINDEX) should typically
yield better performance than doing a INDEXDEFRAG, but it showed cases where that might
not always be the case.
Now I'm even more confused than ever. I had hoped that the INDEXDEFRAG was essentially a
subset of the DBREINDEX operation so that my choice was simple. ;-)
"Gert E.R. Drapers" <GertD@.Online.SQLDev.Net> wrote in message
news:%23B95Rl2pDHA.976@.tk2msftngp13.phx.gbl...
> Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2003 All rights reserved.
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > For our production environment, we have a decent window which to run some
> maintenance
> > scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
> is that
> > essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
> do everything a
> > INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
> be beneficial to
> > run *both*?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>

Thursday, March 8, 2012

DBCC error 913

DBCC error 913

Wednesday, March 7, 2012

DBCC DBREINDEX versus INDEXDEFRAG.

DBfile usage

Blog Archive

About Me