Left over Spool space

General
Enthusiast

Left over Spool space

Is there a way to find out how long spool space is not released for a particular user.

Iam using the below query to find out how much spool space is not relased after the session is over .

But i want to find how long the spool space is held by the user so that I can update the spool space if it is more than 30 min.

 

SELECT DATABASENAME  ,   cast (sum ( CURRENTSPOOL ) /1024/1024/1024 as decimal (5,2)) as "TotalLeftoverSpool in GB"
FROM    DBC.DISKSPACE
WHERE DATABASENAME NOT IN (SEL USERNAME FROM DBC.SESSIONINFO)
AND CURRENTSPOOL > 0
group by 1
ORDER BY 2 desc;