date when changes made to the object?

Question

Post reply

date when changes made to the object?

Tara-1044200

SSCoach

Points: 15951
More actions
December 7, 2011 at 10:15 am

#247337

Is there a way in SQL Server to find when was the last date any one accessed the any object in the database or may be any one made any changes to the object.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

if object = something in sys.objects, then yes, you can get teh default trace to see who last created/altered/dropped

if object = data, then no, unless you have an auditing trace of some sort already in place.

you can kind of find out some of the last accessed data for tables like this, but not who selected it....

this is limited to a DMV that is reset to zero on SQL Service stop/start:

--based on the ideas from

--http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

;WITH ServerStarted AS

(

SELECT

MIN(last_user_seek) AS first_seek,

MIN(last_user_scan) AS first_scan,

MIN(last_user_lookup) AS first_lookup

FROM sys.dm_db_index_usage_stats

),

ServerFirst AS

(

SELECT

CASE

WHEN first_seek < first_scan AND first_seek < first_lookup

THEN first_seek

WHEN first_scan < first_seek AND first_scan < first_lookup

THEN first_scan

ELSE first_lookup

END AS usage_start_date

FROM ServerStarted

),

myCTE AS

(

SELECT

DB_NAME(database_id) AS TheDatabase,

OBJECT_NAME(object_id,database_id) As TheTableName,

last_user_seek,

last_user_scan,

last_user_lookup,

last_user_update

FROM sys.dm_db_index_usage_stats

)

SELECT

MIN(ServerFirst.usage_start_date) AS usage_start_date,

x.TheDatabase,

x.TheTableName,

MAX(x.last_read) AS last_read,

MAX(x.last_write) AS last_write

FROM

(

SELECT TheDatabase,TheTableName,last_user_seek AS last_read, NULL AS last_write FROM myCTE

UNION ALL

SELECT TheDatabase,TheTableName,last_user_scan, NULL FROM myCTE

UNION ALL

SELECT TheDatabase,TheTableName,last_user_lookup, NULL FROM myCTE

UNION ALL

SELECT TheDatabase,TheTableName,NULL, last_user_update FROM myCTE

) AS x

CROSS JOIN ServerFirst

GROUP BY TheDatabase,TheTableName

ORDER BY TheDatabase,TheTableName

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

Deos the query you provided give me the last date when some one read any database?

and if i am doing a daily backup, does that show wrong dates?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Tara-1044200 (12/7/2011)
Deos the query you provided give me the last date when some one read any database?
and if i am doing a daily backup, does that show wrong dates?

Back to square 1, why do you want that info?

Backups won't reset those dmvs.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

I did run the query and interestingly for all the databases i am getting the date with in the last 2 days where in i am pretty sure there are many databases that was not read from the past 6 months.

I want to get the list of databases that was not READ from the past 6 months which can be considered as un used databases and probably do a safe backup and drop them from the server.

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

Lowell's code shows the last time that a table or index was accessed in some way. So if it's showing 2 days ago, someone ran a query or some form 2 days ago. Could be a scheduled job, could be an admin, no way to tell without running traces.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Close the dbs (still accessible, it'll just have to be auto-opened).

check the default trace for if / when they open. Keep in mind that this trace only keeps 125MB of data so depending on your usage that may be a very short time (only days).