Query to find out when a SQL 2000 database last accessed

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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...

Answer 4

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

Answer 5

Hi @DeAndra, Hayes ,

Here are some posts also discussing this issue.
https://www.sqlservercentral.com/forums/topic/how-to-find-last-access-date-of-database-in-sql-server-2000
https://stackoverflow.com/questions/3925213/how-to-determine-which-databases-are-being-used-on-sql-server-2000
https://sites.google.com/site/sqlservermsdn/how-to-find-a-database-s-last-used-date-in-sql-server