May 13, 2020 at 1:41 pm
Has it become any easier in recent releases to see when a table, stored procedure, database was last used ?
I have been working on the IMBi recently, and each object has a Last Used date in the description, so it's just a couple of clicks to get the information.
We have SQL 2019 on our newest server, but still running 2008, 2012 & 2014 on various others.
May 14, 2020 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 14, 2020 at 2:47 pm
Proc usage is tricky as it all depends on how volatile your procedure cache is.
If your constantly flushing plans then the procedure stats wont stay around for long.
But it is certainly possible with some investigating work.
sys.dm_db_index_usage_stats and sys.dm_exec_procedure_stats would be the ones to look at, you will need to grab the latter at regular intervals if your purging proc plans.
Index Usage will record the last time a table was accessed, if no tables accessed then rule of thumb would say the DB is not used.
Proc stats will show the last exec time
Remember though these DMV's are reset on an instance restart so you can't fully rely on them, you would need to capture and monitor the details they store over a long period of time to make any analytical use, especially for those rouge month, quarter, year end etc processes that happen.
May 14, 2020 at 3:29 pm
I came up with an idea to run a trace file capturing events.
Then list all database objects to a table
Then search the trace file where TEXTDATA LIKE the object name.
A bit clunky, and would need to run for a while.
Here's an idea to capture Database access.
https://www.techmixing.com/2018/03/sql-script-how-to-find-last-access-date.html
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply