DBREINDEX takes database offline?

HI All,
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
SuchiFirst, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
(Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
See inline below for comments on your questions:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> HI All,
>
> Environment: SQL server2000, SP4, on win2003.
> I have few quetions regarding DBCC DBREINDEX..
> 1. Reindex is offline operation means..takes the db offline?
No, it isn't offline in that sense.
> we have optimzation job through maintenace plan. When it started, will
> it takes the database offline by issuing the below command? I heard one DBA
> saying this.
> ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
DATABASE would be executed, then there's no way you can access the database and execute the DBCC
DBREINDEx commands.
> What is my understanding till now is, it will take the table offline by
> issuing xcluve lock.. is that correct?
Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
lock (from memory).
> 2. Database is avaiable for use when reondex is happening?
Yes, except for the table currently being reindexes and having the lock.
>
> 3. When users are already connected, this optimation jobs starts, it will
> kill the existing users?
No
> If no, if the user is having a lock on table, reindex
> is also for the same table, reindex will wait for that user to release the
> lock on the same object or it will kill the user?
Wait. Just as any type lf locking/blocking.
>
> 4. It will allow new users while it is running?
Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
same resource.
>
> Any one can throw some light on above points.
>
> Thanks,
> Suchi|||Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:
> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> > HI All,
> >
> >
> > Environment: SQL server2000, SP4, on win2003.
> >
> > I have few quetions regarding DBCC DBREINDEX..
> >
> > 1. Reindex is offline operation means..takes the db offline?
> No, it isn't offline in that sense.
>
> > we have optimzation job through maintenace plan. When it started, will
> > it takes the database offline by issuing the below command? I heard one DBA
> > saying this.
> >
> > ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> >
> > What is my understanding till now is, it will take the table offline by
> > issuing xcluve lock.. is that correct?
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> >
> > 2. Database is avaiable for use when reondex is happening?
> Yes, except for the table currently being reindexes and having the lock.
>
> >
> >
> > 3. When users are already connected, this optimation jobs starts, it will
> > kill the existing users?
> No
>
> > If no, if the user is having a lock on table, reindex
> > is also for the same table, reindex will wait for that user to release the
> > lock on the same object or it will kill the user?
> Wait. Just as any type lf locking/blocking.
>
> >
> >
> > 4. It will allow new users while it is running?
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
> >
> >
> > Any one can throw some light on above points.
> >
> >
> > Thanks,
> > Suchi
>