Database Last Modified

Is it possible to find out when a database was last modified, or logged into?
Will looking at the data and log file to see when they where last modified
at OS level do this?
Thanks
Adam
You can write something on sysprocesses. that eyes the DBID.
or Run a Profiler all the time.
Create a Trigger for Login attempts or object changed and save these
values in a table.
Maninder
MCDBA
|||Adam Sankey wrote:
> Is it possible to find out when a database was last modified, or logged into?
> Will looking at the data and log file to see when they where last modified
> at OS level do this?
> Thanks
> Adam
SQL 2000 or 2005? There's nothing built in to SQL 2000, you would have
to write some triggers or capture some traces. The file date/time
usually indicates the last time the file grew or was "recovered". Data
modifications don't update those.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Adam --
I have been searching for this answer for nearly a year now. The official
consensus is "NO", but I have reason to believe that this is not so. If work
didn't require 10+ hours a day and I didn't have a family who is ready to put
me on probation for still being at work when normal people are enjoying
relaxing evenings at home, I might have time to drill down to an answer.
I am also playing catch up since Oracle metadata tables were quite familiar
to me before my focus was switched to SQLServer and I had trouble finding
similar objects in SQLServer. However, since I like challenges and am
persistent, I have discovered stuff from people like --
Kalen Delaney (a legend in her own time) has published two books relevant to
this discussion: "Inside SQLServer" (v2000 and v2005) dealing with the
database storage engine internals. Very comprehensive and well written, it
details structures within the 8k pages that SQLServer is made of and extents
(8 logically contiguous pages) and how these are allocated using GAM (Global
Allocation Map), SGAM (Shared Global Allocation Map), and IAM (Index
Allocation Map) pages.
You can view this stuff using DBCC PAGE (yadda yadda). You need to set trace
flags, specify dbase, file, page, and output options and you DO get LOTS of
output. But I think the key to last modification date of a database might be
found by excavating through this stuff.
My purpose in identifying the last modified date of a database is to
determine my risk level for data loss. The time between last backup and last
modified is that risk. Without knowing the last modified date, I could get my
shorts in a knot because I don't have a backup on the PatientTracking
database since 2004.08.01. But if I know that it has not been modified since
2004.07.06 -- I can ignore it since it is probably an archive or discontinued
remnant.
Well, if you do find an answer, I sure would like to know. I need something
that would work with SQLServer 2000 and 2005 mix -- I use scheduled tasks
composed of DOS/vbs/WMI to scan all my SQLServers daily, gathering
information about event log errors, failed jobs, db file sizes (allocated and
used), etc. since I don't have time to go to each of my 70 some servers
hosting SQLServer databases and look this up manually.
But knowing all this stuff is secondary to knowing that I am covered on
backups.
Thanks,
-- Glenn Wiens
"Adam Sankey" wrote:

> Is it possible to find out when a database was last modified, or logged into?
> Will looking at the data and log file to see when they where last modified
> at OS level do this?
> Thanks
> Adam