June 30, 2009 at 5:01 am
I am currently trying to determine where some of our databases were last accessed as part of a cleanup operation.
I am currently using the sys.dm_db_index_usage_stats, in particular the last_user_update, last_user_scan or last_user_lookup (whichever is greater) to retrieve this information.
Can anyone offer an opinion to how reliable this information is? It seems to indicate that the ReportServer database was accessed last night, but we have never run any reports on this server through reporting services. Could this be just the reporting services service accessing the database ?
June 30, 2009 at 5:22 am
jabba (6/30/2009)
I am currently trying to determine where some of our databases were last accessed as part of a cleanup operation.I am currently using the sys.dm_db_index_usage_stats, in particular the last_user_update, last_user_scan or last_user_lookup (whichever is greater) to retrieve this information.
Can anyone offer an opinion to how reliable this information is? It seems to indicate that the ReportServer database was accessed last night, but we have never run any reports on this server through reporting services. Could this be just the reporting services service accessing the database ?
sys.dm_db_index_usage_stats tells information about usage of indexes. Can you clarify more on your question?
Do you want to know who accessed your database? in this case the log captures successful/failed login in the log file (if thats enabled?)
June 30, 2009 at 6:03 am
sys.dm_db_index_usage_stats tells information about usage of indexes.
Yes it does, but it also tells when an index was last scanned, in the columns i mentioned. Hence at the moment I am trying to write a query based on this information.
Do you want to know who accessed your database?
I don't want to know who accessed my database but when it was last accessed.
In this case the log captures successful/failed login in the log file (if thats enabled?)
OK is there an easy way to query this, and capture the information in a report. It must be retrospective
June 30, 2009 at 6:10 am
If you need to know which table was accessed by whom and when, you can write a server side trace (keep in mind that it will be resource intensive) and might not be the best advice for what you want to achieve.
The following articles written by Gail will tell you which procedures are being run. You can twick the code to include tables as well. Though the articles are meant for perf tuning, you can use them as well.
June 30, 2009 at 6:24 am
My understanding is that the server side teace will not be retrospective and so will not achieve what I am looking for. I go back to my original question.
I am currently using the sys.dm_db_index_usage_stats, in particular the last_user_update, last_user_scan or last_user_lookup (whichever is greater) to retrieve this information.
Can anyone offer an opinion to how reliable this information is? It seems to indicate that the ReportServer database was accessed last night, but we have never run any reports on this server through reporting services. Could this be just the reporting services service accessing the database ?
June 30, 2009 at 6:47 am
From BOL...
The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.
Dependening on when your services were last recycled this may not give you the right information.
June 30, 2009 at 7:05 am
What if the database does not have any indexes and I want to know when it was last accessed.....We are having many unused database which we are planning to decommission but before that we want to know that when they were last accessed.
June 30, 2009 at 7:11 am
jabba (6/30/2009)
My understanding is that the server side teace will not be retrospective and so will not achieve what I am looking for. I go back to my original question.
not sure what you mean by retrospective;
a server side trace, based on two events 12, --SQL:BatchCompleted, and --RPC:Completed
would give you every SELECT statement or Procedure/function that was called on your server, so with all the info in the trace, you would easily see who called it, performance info, etc.
That would certainly be one way to get that info.
Lowell
June 30, 2009 at 7:35 am
not sure what you mean by retrospective;
If I set up the server side trace, my understanding is that it would only collect the information going forward. I wouldn't tell me for instance that it was last year a database was accessed, unless I had set it up a year ago?
June 30, 2009 at 9:54 am
ahh i see what you are after...i think you'll end up finding that all the DMV's get set to zero whenever the server is stopped and started, though right?
so you might not be able to see when something was last accessed if your server stopped for maintenance, updates, etc...
but you are right, you can at least look at some of the stuff back until that last stop/start, compared to a trace.
Lowell
June 30, 2009 at 10:02 am
We've got similar issues. You might be able to see when the database was last used by looking into the tables - there might be a column containing a date in there that records updates to the data and infer the last usage date from that
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply