Any comments on how to load a busy database? I have a table in a SQL Server
database that is read-only (SELECT only) to all applications. The only
application that has any more permissions is the loading application. This
is done via SQL Server stored procedure. When the job runs at night the SP
runs in about 15 minutes. If it fails for any reason and I have to run it
during the day it takes hours to complete. I assume this is due more to a
busy server than table locks as nothing but my SP should be able to lock the
table.

How does one accomplish loading during the day, assuming the system is busy?
Do you kick users out? Limit their ability to use up resources (somehow?)
Thoughts?

You are still going to take out Shared Locks on the tables/keys/rows though
right so this will restrict the loading app to some degree.

Have a look here for the matrix

http://msdn2.microsoft.com/en-us/lib...6(SQL.90).aspx

You could try having the SELECTs use WITH (NOLOCK)



--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"Tom P." <TomP (AT) discussions (DOT) microsoft.com> wrote

OK, more for me to learn there. Given that the other apps can hold some sort
of lock on the table, how does one load the table? Wait until the lock is
released?

"Allan Mitchell" wrote:

The load should wait for the lock to be released. By default the "waiting"
process will just wait.

Have a look here

http://www.google.com/search?hl=en&l...UT&btnG=Search



--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"Tom P." <TomP (AT) discussions (DOT) microsoft.com> wrote

Thanks.

"Allan Mitchell" wrote: