is database being used?

Question

is database being used?

sqlstar2011

SSCertifiable

Points: 6000
More actions
June 7, 2006 at 7:38 am

#61782

is there way to tell when was the last time someone logged onto a specific database? i have a few database which i dont know if are being used. is there anyway to find out other than just looking at the current activity under management?

thanks

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

The only way I can think of is to set up a profiler session, and monitor the databases in question. You can use the Audit event to check connections and such

ram4tech Hall of Fame Points: 3140 More actions · Answer 2

Is there a simple tutorial to setup a profiler session to do this?

Mubeen Mohammed-203067 SSC Eights! Points: 877 More actions · Answer 3

Shahab,

You can use any 3rd party tool to get the Audit Info or you can use the simple query like this.... from master database..

SELECT spid, login_time, loginame, status FROM SYSPROCESSES

WHERE DBID = (SELECT dbid FROM sysdatabases WHERE NAME = 'your_database')

ORDER BY login_time DESC

It will give you just idea about the database when last used with login info. I don't know whether you are looking for the same info which I am posting the Query.

Thanks - Mubeen Mohammed

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

Look up SQL Profiler in BOL. It has instructions for creating a trace.

Mubeen, your query only shows who is currently connected to a database.

Greg

Mubeen Mohammed-203067 SSC Eights! Points: 877 More actions · Answer 5

Thanks Greg, I didn't realized that the query is only for current transactions. Eventhough I have a question about Trace. How can we get Past Info? like who has logged previously?

If he start SQL Profiler, he would get the Trace from that point of instance and only and he should wait for some days to get trace and then take the decision depending upon trace output. The Idera Tool (SQLCompliance) does this activity very clean. But I don't know is there anything in SQL2005 to trace past transactions.

Thanks - Mubeen Mohammed

SQLBill SSC Guru Points: 51440 More actions · Answer 6

You can't trace past connections. SQL Server doesn't have any way to tell the time that a database was last accessed. It would take too many resources to keep that data.

Let's say I have a table that gets 1 million rows a day. That's 1 million times the database is accessed. Even if you only count the number of times a connection is made......you could have hundreds of connections in a day.

-SQLBill

Patrick Folan Mr or Mrs. 500 Points: 512 More actions · Answer 7

Why dont you create a table of your own, and insert the sysprocesses data into it adding a getdate() field, and create a job in Sql Server Agent to run the SQL code every 5 minutes. Then let it run for 24 hours and check the table to see if anyone connected.

e.g

Insert into 'Your_Table'

select getdate(), * from master..sysprocesses

where dbid in

(select dbid from master..sysdatabases where name = 'specific database')

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 8

I think using SQL Profiler is probably lot better as I can setup my trace with a specific criteria. Works pretty well.