on 06-18-2014 8:34 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.