Is there a way to find out when a database was last accessed on SQL Server 2000? By this I mean, had a table read, a stored procedure called etc...
I have a list of databases and I am trying to find out which databases are still needed or not.
I'm not sure sp_who2.LastBatch will give me the result I need.
There is no way in SQL 2000 - as it does not support any DMV's introduced in sql server 2005.
Your best bet is to use an AUDIT trace or run server side trace.
Note: depending on what you are capturing, it will be resource intensive and might end up filling up your disk space as well.
EDIT:
@rhughes
Refer to this para in the link :
To achieve a fine-grained approach to these types of auditing, you'll need to turn to Server-Side Traces. SQL Server has long provided the ability to trace activity for debugging and performance-monitoring purposes. You can also use Server-Side Traces to monitor security-related activity. Unlike C2 auditing, traces aren't persistent. If you restart SQL Server, the trace is lost and you must recreate it. Another difference between C2 auditing and traces is that you can start and stop a trace without restarting the database service. Traces are extremely configurable; you can choose exactly which events to audit and what information about each event to record.
I've successfully set up a trace on SQL 2000 where I use a job that every hour starts a new trace, then ends the old trace (started by the job that ran an hour ago). Once the old trace is ended it then processes the data into a summarized table. Using that methodology I've got traces that tell me when the last time any given object was touched, how many times a given login logged in or out in a given hour etc.
I will say it is somewhat space intensive, and somewhat resource intensive at times, but it does work.
I think I will tell you something that's probably you heard before: do yourself a favour and upgrade from sql server 2000 to a newer version.
this solution below assumes you did the upgrade:
I found this good script and explanation, plus a detailed addition and pros and cons and you can find it here.
I have this query to show me when the database was last accessed. As per the explanation above it is not perfect.
Also Be aware sys.dm_db_index_usage_stats does not return information about memory-optimized indexes. For information about memory-optimized index use, see sys.dm_db_xtp_index_stats
The result of this query should be saved to a table ACCORDINGLY, so that when you restart the server, or the sql server services, you don't lose track of where you are.
--==============================================================================
-- The last time a database was accessed
-- marcelo miorelli
-- 30-Apr-2020
--==============================================================================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select @@SERVERNAME AS [Server],
sd.[name] AS [Database Name],
sd.create_date as [Created On],
last_access_dt = MAX(coalesce(DDIUS.last_user_seek,
DDIUS.last_user_scan,
DDIUS.last_user_lookup,
'1/1/1970')),
[LAST ACCESS] =CASE WHEN MAX(coalesce(DDIUS.last_user_seek,
DDIUS.last_user_scan,
DDIUS.last_user_lookup,
'1/1/1970'))
= '1970-01-01 00:00:00.000'
THEN 'No Access since ' +
CONVERT (VARCHAR(50),
(SELECT create_date
FROM sys.databases
WHERE database_id = 2),109)
ELSE 'Last Access ' +
CONVERT (VARCHAR(50),
MAX(coalesce(DDIUS.last_user_seek,
DDIUS.last_user_scan,
DDIUS.last_user_lookup,
'1/1/1970')),109)
END,
the_time_now = SYSDATETIME(),
number_of_days= DATEDIFF(day,MAX(coalesce(DDIUS.last_user_seek,
DDIUS.last_user_scan,
DDIUS.last_user_lookup,
'1/1/1970')),SYSDATETIME())
from sys.databases SD
LEFT OUTER JOIN sys.dm_db_index_usage_stats DDIUS
ON DDIUS.database_id = SD.database_id
WHERE SD.database_id > 4
AND SD.database_id <> 32767
GROUP BY sd.[name],sd.create_date
ORDER BY 4 DESC
See the last access takes into consideration the last time the sql server service started.
example of the outcome:
