Post reply

is database being used?

  • 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

  • June 7, 2006 at 7:58 am

    #642271

    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

  • June 7, 2006 at 8:07 am

    #642273

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

  • June 7, 2006 at 8:18 am

    #642280

    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

  • June 7, 2006 at 9:07 am

    #642302

    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

    Greg

  • June 7, 2006 at 9:15 am

    #642308

    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

  • June 7, 2006 at 11:59 am

    #642363

    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

  • June 8, 2006 at 8:01 am

    #642546

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

  • June 8, 2006 at 8:03 am

    #642547

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

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

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