All Forums Database
ab186024 16 posts Joined 11/11
18 Sep 2015
Identification of data that is not being used/Cold data using DBQL or other approach

Hi All,
We need to identify data that is not being used/Cold data. What's the best way of identifying data that is not being used/Cold data using DBQL or other approach? Thanking you in advance.

ab186024 16 posts Joined 11/11
21 Sep 2015

I'm thinking of getting the SQL run againt the table, analyse the where predicate and identify the date accessed to identify the cold data:
sel S.username,S.starttime,S.firstresptime , S.elapsedtime, L.SqlTextInfo,
Q.ObjectDatabaseName, Q.ObjectTableName
from    dbc.qrylogV S  
inner join dbc.qryLogSqlV L  
on S.ProcID=L.ProcID 
and  S.QueryID=L.QueryID 
inner join DBC.QryLogObjects Q
on S.ProcID = Q.ProcID
where starttime>='2015-08-30 04:00:00.00' and starttime<='2015-09-22 09:24:00.00' 
and  username = ‘User_Name’ — This can be updated for specific users
and Q.ObjectDatabaseName = ‘Test_DB’ — The database where the transaction tables exist
and Q.ObjectTableName = ‘Tbl_Bal_Single’ — The transaction tables
and L.SqlTextInfo like  ‘%Where Run_Date = Run_Date-1%’ —This can be updated to SELECT, UPDATE… and ignore statements like COLLECT STATS….
order by elapsedtime desc;
Anyone has a better approach? Thank you


gskaushik 56 posts Joined 09/10
26 Sep 2015

To find thetable last access timestamp combine tablesize with tables, solution provided alrady by Dieter and Adarsh

To get only the accesscount use the accescount from tables view

sel DatabaseName, TableName, AccessCount, LastAccessTimeStamp
from DBC.TablesVX where TableName IN ('tab1', 'tab2', 'tab3', 'tab4');


Subramanian kaushik Gurumoorthy

You must sign in to leave a comment.