Eric,

We are using SQL Server 7.0 SP3 on Windows NT 4.0 There is an application which imports the data from Sybase and also acts as a reporting server. Most of the reports creates objects in tempdb and manipulate the data.

For the past 3 weeks, our users are complaining that they cannot access the server. Extract of the sp_lock at one point is below:

spid dbid ObjId IndId Type Resource Mode STATUS ------ ------ ----------- ------ ---- ---------------- -------- ------ 31 2 0 0 EXT 1:89496 U GRANT 31 2 0 0 EXT 1:89464 U GRANT 31 2 0 0 EXT 1:89408 U GRANT 31 2 0 0 EXT 1:89424 U GRANT 31 2 0 0 EXT 1:89432 U GRANT 31 2 0 0 EXT 1:89384 U GRANT 31 2 0 0 EXT 1:89392 U GRANT 31 2 0 0 EXT 1:89400 U GRANT 31 2 0 0 EXT 1:89344 U GRANT 31 2 0 0 EXT 1:89352 U GRANT 31 2 0 0 EXT 1:89360 U GRANT

When I run the SQL Profiler, it shows me plenty of LOCK Timeouts at regular intervals. It also shows DML activity at that time.

I have tried various things like, optimizing queries, removing fragmentation, but still failed to achieve anything remarkable.

I have also ensured that there are no select intos / tables created within a transaction.

Could you please give me some idea, as to how to go about it.

Thanks in advance

Regards

Jaya Sudhakar Garladinne

-- Anonymous, June 06, 2003

Answers

Jaya,

The sp_lock report does not show any processes waiting for a resource. So it seems that you did not catch the the waiting process in the act of waiting. You may have better luck troubleshooting if you increase the lock timeout period (using SET LOCK_TIMEOUT).

Here is an interpretation of the sp_lock report:

spid column - 31 is the SQL Server process ID number from master.dbo.sysprocesses.

dbid column - is the database identification number requesting a lock. 2 is tempdb.

Type column - EXT is Extent, which is a contiguous group of eight data pages or index pages.

Resource column - First page number in the extent being locked. The page is identified by a fileid:page combination.

Mode column - U is Update, which is used on resources that can be updated.

Status column - GRANT shows that the lock has been obtained.

In short, process 31 is locking extents in tempdb.

If you were using SQL Server 2000 (which supposedly is the only version that I am still supporting on this forum) you could turn on Trace Flag 1204 to get a report of the deadlocks.

Hope this helps,

Eric

-- Anonymous, June 06, 2003