All Forums Database
rbearley 2 posts Joined 09/13
12 Sep 2013
Find table size and last access date

Hello,
I am still learning TD so I apologize if this is an 'easy' one. I need the following information so I can see if there are any old tables I can drop to reclaim space. I think I need to join DBC.Tables and DBC.TableSize but having issues doing so. I would like to see tables from multiple databases, but if I have to do it one-by-one then that is fine. Any help is appreciated!
DatabaseName
TableName
CreatorName
LastAccessTimeStamp
CurrentPerm(mb)
 
Thanks

dnoeth 4628 posts Joined 11/04
12 Sep 2013

What do you mean by "issues"?
You should simply join on (DatabaseName, TableName).
 
Dieter

Dieter

Adharssh 36 posts Joined 08/13
13 Sep 2013

Hi,
We have tablename & Databasename in both dbc.tables & dbc.tablesize. Please find the Code, that will give you the Tablename,Databasenaem,Creatorname,Lastaccesstimestamp & Currentperm.

sel b.databasename,a.tablename,a.CreatorName,a.CreateTimeStamp,a.LastAccessTimeStamp,sum(currentperm)/(1024*1024)  from dbc.tables a
inner join
dbc.tablesize b
on a.tablename=b.tablename
and a.databasename=b.databasename
group by 1,2,3,4,5
;

Thanks & Regards,
Adharssh.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

rbearley 2 posts Joined 09/13
13 Sep 2013

Thanks for the replies, this helped.

13 Sep 2013

Hi Dieter/Adharssh,
I checked earlier too but all the  entries of lastaccesstimestamp in dbc.tables comes as null in my application..
Is there any way to activate it or is it automatic ?
 
Cheers!
Nishant 

Qaisar Kiani 337 posts Joined 11/05
13 Sep 2013

I think you need to activate ObjectUseCountCollectRate in DBS control utility...

terateig 1 post Joined 05/14
08 Apr 2015

Excellent - this is exactly what I was looking for.
 
- Joshua

varanasianup 14 posts Joined 09/14
24 Feb 2016

All,
ObjectUseCountCollectRate is enabled(>0), Usecount is also enabled, but still i could find LastAccesstimestamp= null for few tables in a DB.
I observed if a table is defined on a NOPI, then LastAccesstimestamp=NULL, Somebody correct me here please.

ToddAWalter 316 posts Joined 10/11
24 Feb 2016

If an object existed prior to turning on usecount, and it has not been accessed since usecount was turned on, then the last access will still be null.

You must sign in to leave a comment.