Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / March 2008

Tip: Looking for answers? Try searching our database.

How to get details of when Database was last used

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Aishu - 28 Mar 2008 16:31 GMT
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
Aaron Bertrand [SQL Server MVP] - 28 Mar 2008 16:39 GMT
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.

> HI there,
> I want to find out on when the database and the objects were last used so
[quoted text clipped - 4 lines]
> Aishu
> MCSE,MCDBA,MCSA
Aishu - 28 Mar 2008 16:52 GMT
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

> Do you have any idea which version of SQL Server you are using?  If so, why
> not mention that?
[quoted text clipped - 14 lines]
> > Aishu
> > MCSE,MCDBA,MCSA
Aaron Bertrand [SQL Server MVP] - 28 Mar 2008 20:31 GMT
> 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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.