select doing sort in tempdb is preventing tempdb log truncation

[Former Member]

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

[Former Member · Accepted solution]

Select is not a logged operation but when you do a sort it needs a worktable and that worktable is created in tempdb. Thus it needs to log the creation of temp table as well as logging of extents assigned to the worktable. This is causing your temp db is full as your log is not cleared because log entry related to this spid are not inactive yet.You will not see it in transactions table because it doesn't start a new transactions as such.

I would suggest that try to reduce the time taken by the select query so that you do not have a log full issue.

Thanks

[Former Member · Accepted solution]

Thanks Darshan. I doubt, I can do much with some of these queires , but will check if these selects can move to another tempdb .

Any ideas about partitionsize as mostly NULL  for worktables ?

[bart_van_kuijk]

Are you in a position to test a more recent version than SP50 ? Recent changes went in under CR 750937 :
Sometimes a long running transaction can be seen in tempdb for $sort_local which results in tempdb filling up if a large data set needs to be sorted.

The changes are internal to ASE and the aim is to shorten the time ASE needs to hold the $sort_local transaction.

For your codeline, CR 750937 is in from 15.7 SP61 or higher. For the other codelines it is in :

ASE 15.7 SP121
ASE 16.0 GA PL01

If you can test this version, please post back your observations on the effeect this had (if any).