How to find last login\activiy in database

Question

How to find last login\activiy in database

MonCircle2006

Old Hand

Points: 374
More actions
October 29, 2009 at 9:09 am

#206089

Is there a system stored procedure that I can use to find the last time a database was used?
Thanks,
Mon

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Do you mean the last time someone "USE"d the database? Or ran a query?

There's a default trace in 2005. If you think it's been awhile, set AUTO CLOSE for that db. If someone uses it, then a message will get written into the log.

MonCircle2006 Old Hand Points: 374 More actions · Answer 2

I mean, ran a query or logged into the database. The trace will show me any future usage, is there anyway I can trace what happened in the last couple of months.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

No, when SQL starts it opens the db and that's it. It doesn't log the activity.

I'd set auto close and check the log periodically. After xx days, detach the db and then after yy days, remove it. Keep a backup.

MonCircle2006 Old Hand Points: 374 More actions · Answer 4

Thanks Steve, I set the database to Auto-close. Could you tell me what events to capture in the trace? I was looking at Security Audit and then Audit Login/Logout?

Thanks,

Mon

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

Autoclose will actually write this in the error log for Windows. I don't see a trace event. Maybe capture any t-SQL batch in that db?

George M Parker SSCertifiable Points: 5412 More actions · Answer 6

One other thing you can do is run a scheduled job to capture the output of sp_lock or it's equivalent dynamic management view. If the database is in use, a shared lock will be placed on the database to prevent another user from dropping it. I've had to do something similar in the past when trying to drop a database that someone had a lock on, but it wasn't readily available when looking at the databas connections.