Table Last Access Date

• tlim-994124

  SSC Enthusiast

  Points: 192

  More actions

  January 21, 2009 at 1:25 am

  #200314

  Hi everyone,

  Can anyone tell me how do i find out the last access date of the tables in my database?

  E.g Script, SQL query, via Enterprise manager?

  This is to find out the obsolete tables, so that we can do a proper migration work.

  Thank you,

  Tommy

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  January 21, 2009 at 7:55 am

  #930355

  I don't know of anyway to query this data. You would need to setup a server-side trace and let it run to see what tables have been accessed in a database.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738217

  More actions

  January 21, 2009 at 7:59 am

  #930362

  In 2005 there's a default trace (http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/), but in 2000 you would have to have a server side trace running.

• tlim-994124

  SSC Enthusiast

  Points: 192

  More actions

  January 21, 2009 at 7:18 pm

  #930841

  Thank you everyone.

• tlim-994124

  SSC Enthusiast

  Points: 192

  More actions

  January 21, 2009 at 7:59 pm

  #930856

  Hi again,

  Just to check will the trace be resource hungry?

  And also is it possible to check the last access date of objects (e.g stored procedures)?

  Thanks,

  Tommy

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738217

  More actions

  January 22, 2009 at 2:25 am

  #930933

  It will consume a bit, but you can filter down the events and columns pretty low, store the data on a file on a separate drive, etc. Use a server side trace and it will grab the SQL as well.

  I might set it to just grab date/time and object ID, not much overhead with just those.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  January 22, 2009 at 6:20 am

  #931085

  As Steve said, if you limit the events and columns traced and filter on the appropriate database(s) it should be very lightweight.

  You do want to make sure that you are saving to a different drive than your data and log drives if possible and make sure it is a local drive.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• tlim-994124

  SSC Enthusiast

  Points: 192

  More actions

  January 27, 2009 at 7:46 pm

  #933589

  Thanks, i will give it a shot.

  Tommy