DB Last Used Date

Question

DB Last Used Date

PavanKK

Old Hand

Points: 355
More actions
September 20, 2010 at 2:53 am

#240654

Hi Forum,
As we are on the way to cleaning Production server, we need to find out the last usage date of each database. sothat we can clean the databases which are not used for a long time. Is there any way to find the last usage date of the database.
thanks for the help.
KK

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19252 More actions · Answer 1

Not exactly what you are looking for, but the following code can be used as a starting point.

I came across this code in SSC some time ago - it shows the date each table of a database was last accessed, since the server/database was restarted:

WITH LastActivity (ObjectID, LastAction) AS

(

SELECT OBJECT_ID AS TableName,

last_user_seek AS LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = DB_ID(DB_NAME())

UNION

SELECT OBJECT_ID AS TableName,

last_user_scan AS LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = DB_ID(DB_NAME())

UNION

SELECT OBJECT_ID AS TableName,

last_user_lookup AS LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = DB_ID(DB_NAME())

)

SELECT OBJECT_NAME(so.OBJECT_ID) AS TableName,

MAX(la.LastAction) AS LastSelect

FROM sys.objects so

LEFT

JOIN LastActivity la

ON so.OBJECT_ID = la.ObjectID

WHERE so.TYPE = 'U'

AND so.OBJECT_ID > 100

GROUP BY OBJECT_NAME(so.OBJECT_ID)

ORDER BY OBJECT_NAME(so.OBJECT_ID)

Edit: since server/database restart.

Steve Hall
Linkedin
Blog Site

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

PavanKK (9/20/2010)
Hi Forum,
As we are on the way to cleaning Production server, we need to find out the last usage date of each database. sothat we can clean the databases which are not used for a long time. Is there any way to find the last usage date of the database.
thanks for the help.
KK

You need to be careful, what if there is a a database that is only used for reporting on a yearly basis? I have not tested BrainDonor's code, but I'm going to guess that if you have any kind of index maintenance going on then it will show up in that query.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

bob74 Newbie Points: 9 More actions · Answer 3

Hello ,

What happen if some tables don't have any index?

Will this code works in that case?

I have some doubt.

Can you confirm this code will work in that case?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It will.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alfgomled SSC Journeyman Points: 77 More actions · Answer 5

BrainDonor,

Do you have a similar code for SQL 2000 ?

Thank you.