June 10, 2013 at 9:58 am
Hi,
Is it possible to find out when a table was last accessed i.e Table has been selected from a query.
Thanks
June 10, 2013 at 10:02 am
This script will look at sys.dm_db_index_usage_stats to determine the last reads/writes that occurred on a table; however, this DMV is reset when SQL is restarted, so be aware of that.
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,2;
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 10, 2013 at 10:24 am
Often times this request is followed up by the OP stating they are trying to determine if they can safely delete some table(s). The excellent query posted does contain the warning about that being reset when sql restarts. This is very important when trying to determine if a certain table can be deleted. There may be a process that is only run once a year that looks at a certain table. This is sometime done at the end of the year and some services are restarted around that time too on some systems "just because". I would recommend that instead of dropping these tables you instead rename them to have a datetime that is about a year after they are renamed so you know when they have not been accessed for a period of time. Another option would be to archive those tables to a backup database somewhere.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2013 at 10:49 am
What if the table does not have a index or primary key? I take it the above sql will only work if it has this?
June 10, 2013 at 10:50 am
J Good (6/10/2013)
however, this DMV is reset when SQL is restarted, so be aware of that.
When the database is closed. Hence when the SQL server service stops or any other time the database is closed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2013 at 11:42 am
SQL_Kills (6/10/2013)
What if the table does not have a index or primary key? I take it the above sql will only work if it has this?
Yes, the above query will still work - even on tables with no indexes of any kind.
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 10, 2013 at 12:13 pm
J Good (6/10/2013)
SQL_Kills (6/10/2013)
What if the table does not have a index or primary key? I take it the above sql will only work if it has this?Yes, the above query will still work - even on tables with no indexes of any kind.
Well technically speaking a table with no defined indexes has an index with a type of "HEAP" which is why your script will still work.
create table HasIndexesTest
(
SomeID int,
SomeValue varchar(10)
)
select * from sys.indexes
where object_id = object_id('HasIndexesTest')
If alter the table and add an index it will replace the HEAP index with the defined index.
create clustered index IX_HasIndexesTest on HasIndexesTest(SomeID)
select * from sys.indexes
where object_id = object_id('HasIndexesTest')
drop table HasIndexesTest
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2013 at 12:40 pm
Sean Lange (6/10/2013)
Well technically speaking a table with no defined indexes has an index with a type of "HEAP" which is why your script will still work.
You are correct. Thanks for providing some clarification.
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 10, 2013 at 9:17 pm
A table with no clustered index is a heap. A heap can have non-clustered indexes on it and it remains a heap. Just clarifying.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply