How to get details of when Database was last used

HI there,
I want to find out on when the database and the objects were last used so
that we can do some cleaning up and sent it for archiving...
Thank You
Rgds,
Aishu
MCSE,MCDBA,MCSA
Do you have any idea which version of SQL Server you are using? If so, why
not mention that?
If you are using SQL Server 2000, you will need to have a column that
captures that information and update it yourself, since this information is
not tracked.
In SQL Server 2005, certain DMVs will give you information about when
indexes are updated, which may be enough information for this purpose.
"Aishu" <Aishu@.discussions.microsoft.com> wrote in message
news:BDD339D4-95D0-4589-BD7C-840B565A6934@.microsoft.com...
> HI there,
> I want to find out on when the database and the objects were last used so
> that we can do some cleaning up and sent it for archiving...
> Thank You
> Rgds,
> Aishu
> MCSE,MCDBA,MCSA
|||Well u dont have to be so harsh........Well its SQL 2000 & 2005 ,
moreinterested in SQL 2000 what column to add and how to find out
Thanks Aishu
"Aaron Bertrand [SQL Server MVP]" wrote:

> Do you have any idea which version of SQL Server you are using? If so, why
> not mention that?
> If you are using SQL Server 2000, you will need to have a column that
> captures that information and update it yourself, since this information is
> not tracked.
> In SQL Server 2005, certain DMVs will give you information about when
> indexes are updated, which may be enough information for this purpose.
>
> "Aishu" <Aishu@.discussions.microsoft.com> wrote in message
> news:BDD339D4-95D0-4589-BD7C-840B565A6934@.microsoft.com...
>
|||> Well u dont have to be so harsh...
Wow, you call that harsh? I don't think it's all that outrageous to expect
you to identify which version(s) you are using. Better than us wasting time
writing a 2005-only or 2000-only solution, and have you later say, oh, sorry
about all that time you wasted, but I should have mentioned, it needs to run
on version [x].
Anyway, like I said, for 2000, you will need to add a column to each table
you care about, e.g. CreatedDate, ModifiedDate. Whenever data is modified,
you update that, either directly through the DML operation, or with a
trigger. For deletes you could have a table, like
CREATE TABLE dbo.DeleteLog
(
object_id INT,
lastDeleteDate SMALLDATETIME
)
And update or insert a row to this table for each delete operation. (You
could also rename it and use this table for inserts, deletes and selects.)
Select is a bit trickier, depending on whether or not you allow ad hoc DML
into your database, because there is no such thing as a trigger for select.
So, if you control most or all select activity through stored procedures,
then you could perform logging into the table(s) via the stored procedure.
However, if people / apps etc. can run selects from wherever directly into
tables/views, you are going to have a much harder time (without a
server-side trace) to pick up accurate read activity.
For SQL Server 2005, look at sys.dm_db_index_usage_stats ... it will tell
you all about reads and writes to every index on your system. So, to find
the last read and write in each database, one way would be:
SELECT DB_NAME(database_id), LastRead = MAX(CASE
WHEN last_user_seek > last_user_scan AND last_user_seek > last_user_lookup
THEN last_user_seek
WHEN last_user_scan > last_user_seek AND last_user_scan > last_user_lookup
THEN last_user_scan
ELSE last_user_lookup
END
), LastWrite = MAX(last_user_update) FROM
(
SELECT
database_id,
last_user_seek = COALESCE(last_user_seek, '19000101'),
last_user_scan = COALESCE(last_user_scan, '19000101'),
last_user_lookup = COALESCE(last_user_lookup, '19000101'),
last_user_update = COALESCE(last_user_update, '19000101')
FROM sys.dm_db_index_usage_stats
) x
GROUP BY DB_NAME(database_id)
ORDER BY 1;
I'm sure there is a more efficient way to do it, but the above is the first
crack, no coffee, 2-minute version.
A