[dba-SQLServer] Locking Issue

Mark Rider mark at markkaren.com
Sun Jul 4 08:28:57 CDT 2004


I have a database that crunches a bunch of data and the boss wants to crunch
the data based on a totally different set of criteria.  Can't do it all on
the same DB since it takes about 6 hours to run and we need the two sets of
results as close to the same time as possible.

I have set up SQL Server on a new machine and have copied the tables I want
to use from the old SQL Server using DTS.  Permissions have been set as they
were on the old machine and the new criteria is in place.  The VB.NET
program that runs the numbers through the DB is the same as on the old one,
pointing to the local machine's database to get the data.

About halfway through the program SQL Server throws an error indicating that
there is a problem with Locking and the SQL server process has terminated.
I have tried running this from other PCs, pointing to the new SQL Server and
get the same error. Tables get created as they are supposed to, and sometime
during the Update statement the failure occurs.  The table that is getting
Updated is getting updated at first, so there does not seem to be a
permission issue there.

The main difference between the 'old' SQL Server and the 'new' one is that
the new one has 1 processor and 1 Gb RAM, while the 'old' SQL Server has 2
processors and 3 Gb RAM.   The new SQL Server runs at just about 100%
processor use for several minutes just before the error. Is the lack of
'power' causing the Lock? I have the Physical memory reserved for SQL
Server, Priority Boosted, 32 Worker threads, and dynamic memory allocation
all set up.

Is there a way to get around this locking problem?  BOL seems to indicate
that it is a memory issue, but I cannot find a way to get SQL to slow down
enough to make this work.

Any ideas will be greatly appreciated!

Mark Rider






More information about the dba-SQLServer mailing list