Query the last access data/time

Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JB
Hi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB
|||Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:

>Hi,
>SQL Server will not store these information. But for new object creation you
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>
>
|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
[vbcol=seagreen]
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote: