September 6, 2005 at 5:50 am
HI everyone
Can anyone tell me how I can detect the date a database was last used or accessed ?
Is it a table in the master database I should be looking into ?
SQL 2000 SP4.
Cheers
September 6, 2005 at 6:04 am
Nope, not there and not anywhere else either. SQL Server does not maintain that kind of information.
If you absolutely need such info, I'm afraid you must gather it yourself with either some homegrown code that audits the server and logs user accesses, or by some 3rd party tool, or profiler (still, they also have to actively monitor the server, though).
/Kenneth
September 6, 2005 at 7:38 am
you could try something like this in the master db:
select sd.name, login_time, last_batch, sp.status, program_name
from sysprocesses sp
inner join sysdatabases sd
on sp.dbid = sd.dbid
**ASCII stupid question, get a stupid ANSI !!!**
September 7, 2005 at 1:36 am
sysprocesses is a good place to do 'ligthweight' tracking (do it myself in a similar way to gather usage stats in a simple quick & dirty fashion)
However, it's only good for taking snapshots of the current activity - you can't see what has happened in the past, since there is no history there. In fact, sysprocesses doesn't even really exist. It's just a 'view' over 'now' gathered from current connections, so to speak.
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply