Blocking while creating index?

If I dont use online indexing, will there be blocking for the entire
duration of the index creation be it clustered or non clustered ? I thought
there might be an exclusive lock on the table for the entire duration where
even selects would be blocked...but it doesnt appear to be true.
Please let me know
Using SQL 2005
See BOL, Alter Index, Online OFF
"Table locks are applied for the duration of the index operation. An offline
index operation that creates, rebuilds, or drops a clustered index, or
rebuilds or drops a nonclustered index, acquires a Schema modification
(Sch-M) lock on the table. This prevents all user access to the underlying
table for the duration of the operation. An offline index operation that
creates a nonclustered index acquires a Shared (S) lock on the table. This
prevents updates to the underlying table but allows read operations, such as
SELECT statements."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> If I dont use online indexing, will there be blocking for the entire
> duration of the index creation be it clustered or non clustered ? I thought
> there might be an exclusive lock on the table for the entire duration where
> even selects would be blocked...but it doesnt appear to be true.
> Please let me know
> Using SQL 2005
>

Blocking while creating index?

If I dont use online indexing, will there be blocking for the entire
duration of the index creation be it clustered or non clustered ? I thought
there might be an exclusive lock on the table for the entire duration where
even selects would be blocked...but it doesnt appear to be true.
Please let me know
Using SQL 2005See BOL, Alter Index, Online OFF
"Table locks are applied for the duration of the index operation. An offline
index operation that creates, rebuilds, or drops a clustered index, or
rebuilds or drops a nonclustered index, acquires a Schema modification
(Sch-M) lock on the table. This prevents all user access to the underlying
table for the duration of the operation. An offline index operation that
creates a nonclustered index acquires a Shared (S) lock on the table. This
prevents updates to the underlying table but allows read operations, such as
SELECT statements."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> If I dont use online indexing, will there be blocking for the entire
> duration of the index creation be it clustered or non clustered ? I though
t
> there might be an exclusive lock on the table for the entire duration wher
e
> even selects would be blocked...but it doesnt appear to be true.
> Please let me know
> Using SQL 2005
>

Blocking while creating index?

If I dont use online indexing, will there be blocking for the entire
duration of the index creation be it clustered or non clustered ? I thought
there might be an exclusive lock on the table for the entire duration where
even selects would be blocked...but it doesnt appear to be true.
Please let me know
Using SQL 2005See BOL, Alter Index, Online OFF
"Table locks are applied for the duration of the index operation. An offline
index operation that creates, rebuilds, or drops a clustered index, or
rebuilds or drops a nonclustered index, acquires a Schema modification
(Sch-M) lock on the table. This prevents all user access to the underlying
table for the duration of the operation. An offline index operation that
creates a nonclustered index acquires a Shared (S) lock on the table. This
prevents updates to the underlying table but allows read operations, such as
SELECT statements."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> If I dont use online indexing, will there be blocking for the entire
> duration of the index creation be it clustered or non clustered ? I thought
> there might be an exclusive lock on the table for the entire duration where
> even selects would be blocked...but it doesnt appear to be true.
> Please let me know
> Using SQL 2005
>

bizarre job behavior

Over the weekend I transferred our non Clustered SQL Server to a new
Clustered environment. I moved the jobs by method of scripting. Since then,
serveral of my jobs have been failing with different problems. The one thing
all of these jobs have in common is that they all call DTS Packages that
read/ write to files. (.txt, .xls, etc.)
For the record, the account that the SQL Agent runs in has full control over
these files. If I log directly onto the console with this account, I can
create/ edit/ drop anything I want.
Another fun fact is that I can run all of these DTS Packages manually.
Now here is all of the problems I'm having with these jobs.
1. Access denied for the account that SQL runs in to read/ write to the
file.
2. I start the job manually, it never runs. RClick/ Start Job... nothing
ever happens.
3. Although the job has a schedule, it shows (Date and time are not
available.) under the Next Run Date column.
4. Jobs have a staus of "Performing completion actions" forever. I found
this one in KB, but think its really a by product of the other issues.
Something is obviously very wrong here. Any ideas are greatly appreciated.
--
SQL2K SP3
TIA, ChrisRLook in the 'sysjobs' table of the msdb database. See if
the there is an entry for the jobs. If there is, check
the 'originating_server' column. It may still have the old
server name in it......
>--Original Message--
>Over the weekend I transferred our non Clustered SQL
Server to a new
>Clustered environment. I moved the jobs by method of
scripting. Since then,
>serveral of my jobs have been failing with different
problems. The one thing
>all of these jobs have in common is that they all call
DTS Packages that
>read/ write to files. (.txt, .xls, etc.)
>For the record, the account that the SQL Agent runs in
has full control over
>these files. If I log directly onto the console with this
account, I can
>create/ edit/ drop anything I want.
>Another fun fact is that I can run all of these DTS
Packages manually.
>Now here is all of the problems I'm having with these
jobs.
>1. Access denied for the account that SQL runs in to
read/ write to the
>file.
>2. I start the job manually, it never runs. RClick/ Start
Job... nothing
>ever happens.
>3. Although the job has a schedule, it shows (Date and
time are not
>available.) under the Next Run Date column.
>4. Jobs have a staus of "Performing completion actions"
forever. I found
>this one in KB, but think its really a by product of the
other issues.
>Something is obviously very wrong here. Any ideas are
greatly appreciated.
>
>--
>SQL2K SP3
>TIA, ChrisR
>
>.
>|||No, it has the correct server name since I scripted/ created the jobs
instead of moving them.
"Coskun" <anonymous@.discussions.microsoft.com> wrote in message
news:157b01c51ab8$685e91e0$a601280a@.phx.gbl...
> Look in the 'sysjobs' table of the msdb database. See if
> the there is an entry for the jobs. If there is, check
> the 'originating_server' column. It may still have the old
> server name in it......
>
> >--Original Message--
> >Over the weekend I transferred our non Clustered SQL
> Server to a new
> >Clustered environment. I moved the jobs by method of
> scripting. Since then,
> >serveral of my jobs have been failing with different
> problems. The one thing
> >all of these jobs have in common is that they all call
> DTS Packages that
> >read/ write to files. (.txt, .xls, etc.)
> >
> >For the record, the account that the SQL Agent runs in
> has full control over
> >these files. If I log directly onto the console with this
> account, I can
> >create/ edit/ drop anything I want.
> >
> >Another fun fact is that I can run all of these DTS
> Packages manually.
> >
> >Now here is all of the problems I'm having with these
> jobs.
> >
> >1. Access denied for the account that SQL runs in to
> read/ write to the
> >file.
> >2. I start the job manually, it never runs. RClick/ Start
> Job... nothing
> >ever happens.
> >3. Although the job has a schedule, it shows (Date and
> time are not
> >available.) under the Next Run Date column.
> >4. Jobs have a staus of "Performing completion actions"
> forever. I found
> >this one in KB, but think its really a by product of the
> other issues.
> >
> >Something is obviously very wrong here. Any ideas are
> greatly appreciated.
> >
> >
> >
> >--
> >SQL2K SP3
> >
> >TIA, ChrisR
> >
> >
> >.
> >