Dropping and Recreating Tables

We have a job that runs every 30 minutes dropping and recreating a table with
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/or
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will this
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> > We have a job that runs every 30 minutes dropping and recreating a table
> > with
> > updated information. Besides the fact that I am trying to get rid of this
> > needless process, the question I have is:
> >
> > When this job is running and attempting to drop and recreate the table
> > will
> > it not be able to complete if someone has a connection to the database
> > and/or
> > is running a report off the table that SQL Server is attempting to drop
> > and
> > recreate? If there is a lock on this table from someone using it, is there
> > a
> > way I can stop this from happening?
>
>|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
>> Connections to the database itself will not matter however all locks
>> involving the table would need to be released before the DROP command
>> executes. In the event that someone is using the table, the drop command
>> will wait until the locks are released or your connection times out
>> (which
>> ever comes first).
>> --
>> --Brian
>> (Please reply to the newsgroups only.)
>>
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>> > We have a job that runs every 30 minutes dropping and recreating a
>> > table
>> > with
>> > updated information. Besides the fact that I am trying to get rid of
>> > this
>> > needless process, the question I have is:
>> >
>> > When this job is running and attempting to drop and recreate the table
>> > will
>> > it not be able to complete if someone has a connection to the database
>> > and/or
>> > is running a report off the table that SQL Server is attempting to drop
>> > and
>> > recreate? If there is a lock on this table from someone using it, is
>> > there
>> > a
>> > way I can stop this from happening?
>>