select doing sort in tempdb is preventing tempdb log truncation

372 Views

Hi ,

ASE version: 15.7.0/EBF 21206 SMP SP50 /P/x86_64/Enterprise Linux

User Tempdb Data: 30GB , Log: 20GB

A select statement (murex application) that does distinct, inner joins etc which has been running for over 2hrs and created an entry in syslogshold and was preventing the user tempdb transaction log from getting truncated. I always see these select processes that does sorting creates entry in syslogshold for that user tempdb. I think it was other processes that were filling up the tempdb log and SPID 819 was preventing the truncation of log. user Tempdb log size is 20GB and since this was almost full I didn't dare to run select on syslogs (not sure when the select will return and if it had to read from the disk....) to check which process had most entries .

My question is, even if select was creating worktables etc... why does it have an entry in syslogshold . there were no entries in systransactions so why an entry in syslogshold ? Also in MDA table the size of these worktables are mostly NULL.

a) below entry in syslogshold (dbid 7 is user tempdb.)

select * from syslogshold;

dbid reserved spid page xactid masterxactid starttime name xloid

------ ----------- ------ ------------- ---------------- ---------------- ------------------- -------------------------------------------- ----------------------- -----------

7 0 819 9938908 0xdca797001a00 0x000000000000 Jun 17 2014 05:50AM $sort_local 1638

b) sp_transactions doesn't display spid 819

c) MDA table is showing the correct worktables (verified from showplan) for spid 819 , but its size is NULL.

select SPID, DBName, OwnerUserID, ObjectName, PartitionSize

from master..monProcessObject

where DBID = tempdb_id(SPID)

order by PartitionSize

SPID DBName OwnerUserID ObjectName PartitionSize

----------- ------------------------------ ----------- ------------------------------ -------------

819 tempdb1 0 Worktable1 NULL

819 tempdb1 0 Worktable2 NULL

819 tempdb1 0 Worktable3 NULL

Thanks

dinesh