Creating nonclustered index without locking table

Is rebuilding a nonclustered index an online operation?
We have an OLTP system and we need to rebuild a nonclustered index that
is not in use, but I am unsure if this will lock out the whole table or
just the specific column. The table in question is the largest table in
the database and has several hundred records written to it every minute.
TIA,
-BenIf the index is on the table it is in use as it will be updated each time
the row is updated. DBCC DBREINDEX is a totally offline operation but DBCC
INDEXDEFRAG is online.
--
Andrew J. Kelly SQL MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:uFEyeocaEHA.384@.TK2MSFTNGP10.phx.gbl...
> Is rebuilding a nonclustered index an online operation?
> We have an OLTP system and we need to rebuild a nonclustered index that
> is not in use, but I am unsure if this will lock out the whole table or
> just the specific column. The table in question is the largest table in
> the database and has several hundred records written to it every minute.
> TIA,
> -Ben|||I was afraid of that. We have an index that is corrupt, and it is
harder for us to stop the SQL agent, and do maintenance.
Thanks for the help.
-Ben
Andrew J. Kelly wrote:
> If the index is on the table it is in use as it will be updated each time
> the row is updated. DBCC DBREINDEX is a totally offline operation but DBCC
> INDEXDEFRAG is online.
>|||Why would you need to stop Agent? If it is corrupt then you may be better
off using DBREINDEX juston that one index or CREATE INDEX with the DROP
Existing option. How large is the table?
--
Andrew J. Kelly SQL MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...
> I was afraid of that. We have an index that is corrupt, and it is
> harder for us to stop the SQL agent, and do maintenance.
> Thanks for the help.
> -Ben
> Andrew J. Kelly wrote:
> > If the index is on the table it is in use as it will be updated each
time
> > the row is updated. DBCC DBREINDEX is a totally offline operation but
DBCC
> > INDEXDEFRAG is online.
> >|||Don't forget to find out why it got corrupt in the first place - checkout
the SQL Server errorlog and the NT event logs for hardware issues.
--
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:uv0ukDeaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Why would you need to stop Agent? If it is corrupt then you may be better
> off using DBREINDEX juston that one index or CREATE INDEX with the DROP
> Existing option. How large is the table?
> --
> Andrew J. Kelly SQL MVP
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...
> > I was afraid of that. We have an index that is corrupt, and it is
> > harder for us to stop the SQL agent, and do maintenance.
> >
> > Thanks for the help.
> >
> > -Ben
> >
> > Andrew J. Kelly wrote:
> > > If the index is on the table it is in use as it will be updated each
> time
> > > the row is updated. DBCC DBREINDEX is a totally offline operation but
> DBCC
> > > INDEXDEFRAG is online.
> > >
>|||Very good point.
--
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231Xb7MfaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Don't forget to find out why it got corrupt in the first place - checkout
> the SQL Server errorlog and the NT event logs for hardware issues.
> --
> 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:uv0ukDeaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > Why would you need to stop Agent? If it is corrupt then you may be
better
> > off using DBREINDEX juston that one index or CREATE INDEX with the DROP
> > Existing option. How large is the table?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Ben" <bmilliron@.gmail.com> wrote in message
> > news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...
> > > I was afraid of that. We have an index that is corrupt, and it is
> > > harder for us to stop the SQL agent, and do maintenance.
> > >
> > > Thanks for the help.
> > >
> > > -Ben
> > >
> > > Andrew J. Kelly wrote:
> > > > If the index is on the table it is in use as it will be updated each
> > time
> > > > the row is updated. DBCC DBREINDEX is a totally offline operation
but
> > DBCC
> > > > INDEXDEFRAG is online.
> > > >
> >
> >
>|||Not sure exactly how the corruption occurred, SQL logs only showed the
following error, as did the Event Logs.
2004-07-15 11:44:36.29 spid111 Error: 8646, Severity: 21, State: 1
2004-07-15 11:44:36.29 spid111 The index entry for row ID was not
found in index ID 3, of table 1285579618, in database 'dbname'..
I have run DBCC CHECKDB and found the errors.
I have dropped and recreated the above mentioned index, and ran DBCC
CHECKDB again and no errors were reported.
I also ran DBCC CHECKTABLE on the table where the index was located and
that returned no errors.
This is the only article we could find regarding this error.
http://support.microsoft.com/default.aspx?scid=kb;en-us;822747
Andrew J. Kelly wrote:
> Very good point.
>|||Did you follow the instructions in the KB article to see if the scenario
described applies to you?
BTW, you don't need to run DBCC CHECKTABLE separately from DBCC CHECKDB.
DBCC CHECKDB runs the equivalent of DBCC CHECKALLOC and then the equivalent
of DBCC CHECKTABLE for all tables in the database.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <bmilliron@.gmail.com> wrote in message
news:#MsT0OoaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Not sure exactly how the corruption occurred, SQL logs only showed the
> following error, as did the Event Logs.
> 2004-07-15 11:44:36.29 spid111 Error: 8646, Severity: 21, State: 1
> 2004-07-15 11:44:36.29 spid111 The index entry for row ID was not
> found in index ID 3, of table 1285579618, in database 'dbname'..
> I have run DBCC CHECKDB and found the errors.
> I have dropped and recreated the above mentioned index, and ran DBCC
> CHECKDB again and no errors were reported.
> I also ran DBCC CHECKTABLE on the table where the index was located and
> that returned no errors.
> This is the only article we could find regarding this error.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;822747
>
> Andrew J. Kelly wrote:
> > Very good point.
> >