Query to find out when a SQL 2000 database last accessed

DeAndra, Hayes 1 Reputation point
2020-10-23T22:02:53.583+00:00

Is there a query that I can use that will tell me when a database was last accessed in SQL 2000-2003, something other than a trace. Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,288 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Erland Sommarskog 99,071 Reputation points MVP
    2020-10-24T09:03:05.853+00:00

    Sorry, there is not. With one possible exception: the database(s) are in autoclose. In such case the dates on the files in the file system can help you.

    There is not even a straightforward completely reliable way to determine this in SQL 2019 without setting up some form of tracing.

    2 people found this answer helpful.
    0 comments No comments

  2. Shashank Singh 6,246 Reputation points
    2020-10-24T13:24:57.253+00:00

    Like Erland said it is not possible but if you want to track in future and do not want to use a trace a method could be to create a SQL Server agent job which run

    select count(*) from sysprocesses where dbid=xx where XX is ID of current database.
    

    Dump this information in table which has structure like

    create table table_name
    ( Count_value smallint,
    Currentdate datetime default getdate() 
    )
    Insert into Table_name (Count_value, date)
    select count(*) from sysprocesses where spid=xx
    

    Please check the syntax before proceeding.

    Do this for a month and if someone accesses it you would see a count of value and time when it was accessed.

    1 person found this answer helpful.
    0 comments No comments

  3. Erland Sommarskog 99,071 Reputation points MVP
    2020-10-24T17:00:38.237+00:00

    When I suggested that autoclose could be an option, I was more thinking of that you want the results now, but Shanky's response makes me think that you may want to monitor the server for a while.

    Autoclose can still be an option for this, but in such case, you need to turn off all maintenance jobs, backups etc, so that the database is not opened for that reason. But there could be an application server that polls something every once in a while on its own initiative.

    Shanky's idea has the problem that it may not capture occasional queries. That is, it could be that this is a legacy database that someone has reason to peek into, once or twice per week. And the peeking application server is a problem here too.

    In that light, a trace which captures login, RPC:Starting and SQL:Starting will give you more information, as you can analyse the submitted queries to see if there is a real user behind them.

    Or just take the brutal approach: put the database(s) offline and wait for a support ticket to come in...

    1 person found this answer helpful.
    0 comments No comments

  4. Kate hooper 1 Reputation point
    2020-10-24T17:03:34.277+00:00

    This Info Can Be Useful

    Dump this information in table which has structure like

    create table table_name
    ( Count_value smallint,
    Currentdate datetime default getdate()
    )
    Insert into Table_name (Count_value, date)
    select count(*) from sysprocesses where spid=xx

    0 comments No comments