is there a way to know when was last access to a table??

[taixut]

Hi,

I would like to make a bit of cleaning in a SQL Server database. There are many tables that are not used any more but some others that nobody knows if there are applications that use it.

Is there a way to know when was the last access to a table??

Thanks in advance.

 

[TheBugSlayer]

I don't think SQL server keeps the date the table was last accessed. I am assuming that kind of information would be in the SYSOBJECTS system table but it's not. Unless you have some triggers on the database you cannot really tell. Can you tell by looking at the data in each table?

Another option could be to run a trace on the server at a time where you know all client applications are running. Parse the column that records the statement for table names and compare it to your list of tables...

I am still thinking...

 

[SQLBill]

Look into ROWVERSION (or TIMESTAMP). If you can do it, create a column on each table and make it ROWVERSION datatype. This is an automatically generated binary value that changes every time a row is updated or inserted. Comparing ROWVERSION values can indicate if a table has been used (at least for updates and inserts - it will not tell you if the table is used for selects or deletes).

-SQLBill

Posting advice: FAQ481-4875

 

[rmnastor]

you may consider using triggers

-AKIRA

 

[taixut]

thanks everybody,

The problem is that most of them is static data, only accessed in select queries... and as you say there is nothing to do with triggers with selects.

we'll do in a hard way.

 

[hmckillop]

I am not sure what your hard way entails - but here is a suggestion for you.

Setup profiler to capture requests which access the specific tables (either if you use direct sql, include the tablenames in the filter for textdata column, or if you use stored procs you will need to filter on the ones you know access the tables).
You can then have profiler log this information to a table, which you can query at your own leisure.

This would be in preference to having to update a seperate table every time you access a read only table, or even worse perform updates on a column in the read only table to indicate access. Both these options adds in the serious potential for blocking and performance issues.

My suggestion is based on that you probably only want to run this for a set time to profile the how often certain tables are accessed, rather than having an ongoing requirement to monitor access.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[taixut]

thanks,

i'll try that way