[dba-SQLServer] Locking Issue

Locking issues are generated due to a table or set of rows being 
reserved for a previous purpose.  If in your report you are creating 
temp databases then it is very possible that the temp DB is locked 
during the attempt to create a new temp table.

This happens mostly when you have a long query such as


SELECT * INTO #TempTable FROM SomeTable WHERE blah = @variable

To avoid this try creating the temp table first

CREATE TABLE #TempTable (Field1 Varchar(25), Field2 INT, Field3 DateTIME)

INSERT INTO #TempTable (Field1, Feild2, Field3)
   SELECT Field1, Field2, Field3 From Some Table Where blah = @variable

This should aliviate the problem w/ a locking tempdb, if you problem 
stems from some other set of code, perhaps providing some generalities 
of your code so we can disect and inspect what is happening under the hood.


BTW, you can open up EM and see which process is doing the locking too. 
look under your current activity icon in the Management folder.


Mark Rider wrote On 7/4/2004 6:28 AM:

>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
>
>  
>


-- 
-Francisco