Determining when database was last accessed?

Question

Determining when database was last accessed?

MarvinTheAndriod

SSCarpal Tunnel

Points: 4972
More actions
June 30, 2009 at 5:01 am

#206112

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 ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

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?)

Pradeep Singh

MarvinTheAndriod SSCarpal Tunnel Points: 4972 More actions · Answer 2

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

ps. One Orange Chip Points: 29423 More actions · Answer 3

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.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Pradeep Singh

MarvinTheAndriod SSCarpal Tunnel Points: 4972 More actions · Answer 4

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 ?

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 5

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.

RPSQL Hall of Fame Points: 3229 More actions · Answer 6

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.

Lowell SSC Guru Points: 323518 More actions · Answer 7

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MarvinTheAndriod SSCarpal Tunnel Points: 4972 More actions · Answer 8

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?

Lowell SSC Guru Points: 323518 More actions · Answer 9

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DNA_DBA SSCrazy Eights Points: 9132 More actions · Answer 10

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