Last Time the DB has been Used

Question

Post reply

Last Time the DB has been Used

Nita Reddy

SSCarpal Tunnel

Points: 4177
More actions
January 15, 2008 at 9:05 am

#378020

I have 20 database in one of our server, How can I track which database is used everyday or not.
Basically, I want to get read of some of the non-use database.
How can I track it. Is there any way which tells me when did the last time if anyone login to that database or used?
Nita

Viewing 9 posts - 1 through 8 (of 8 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

I would use a server trace (Profiler trace):

http://msdn2.microsoft.com/en-us/library/ms188662.aspx

How to: Create a Trace (Transact-SQL)

Regards,Yelena Varsha

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 2

You cannot say from DB when was the last time it was been used?

Nita

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 3

Hi Nita,

It is a dilemna.

I have had that need too, and am concerned that running a trace against our server would be too resource intensive. We have over 200 databases on a server. What if one of these databases was used only at month end, quarter end, or year end?

For a specific database, I will frequently query information_schema.columns for datatypes of datetime or a column name like '%date%'. I build a cursor around that set and return tablename and max (whatever column has a datetime). Certainly not foolproof. On one occasion the latest records were from 1998, but it was a read only database. Other times, the datatype on the column is character (ick!) and not datetime. There is no way to tell if someone has connected to query the database without a profiler trace.

It is a constant battle for us, as I suspect we are hosting databases that are not in use. If I have a database that I suspect is not in use, I think starting a profile trace from another server, filtering on that specific database, and saving to a .trc file on the remote server is how to do it. The question still remains though... what if it is only accessed periodically.

We've even gone so far as to take a database offline or remove all user access to it and wait to see if someone calls the Service Desk!

I wonder if anyone else has any ideas?

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 4

Thanks, and yes I agree your point of view.

But just a curiosity is there any way to know when the user last logged IN?

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 5

Have you investigated login triggers and if they will work for your needs? In SQL 2005 you can audit logon events and store that data in a table and query it. Of course, this would require implementing first and then analyzing, no way to look to the past if this wasn't being collected.

http://www.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/

Similarily, in the Security tab of Event Viewer... if you are recording Logon/Logoff events, your username will be there. Perhaps bulk loading the Security log into an SQL table and querying?

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

kgunnarsson Ten Centuries Points: 1218 More actions · Answer 6

Hi.

I am facing the same problems as you have been describing. Now what I Did was to create a database with a "user audit"

table DB_Usage(db_id int , active_dttm datetime)

now then i created an stored procedure that simply uses sp_who , execute that once every hour if there are active connections to the db i login in my db_usage or update the active_dttm field.

I do believe this can be accomplished with triggers in 2005, but I’m in a multi server environment using all versions of SQL so I’m sticking with this solution.

kgunnarsson
Mcitp Database Developer.

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 7

That seems like another good way to skin the cat, and very lightweight. I think I like that solution better than a continuously running Profiler Trace. However, the drawback is not knowing if the user is connected at the times other than the firing of sp_who.

I suppose over longer periods of time, your confidence would increase that a database is out of use, and one could increase the frequency it is fired to gain more confidence.

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

kgunnarsson Ten Centuries Points: 1218 More actions · Answer 8

yes it's far from a perfect solution, but i would check out the new triggers browsed them a little and they look good.

kgunnarsson
Mcitp Database Developer.