| Author |
Topic |
|
carumuga
Posting Yak Master
174 Posts |
 Posted - 2010-01-29 : 07:17:18
|
| Hi,Is there a way to identify when the table was last accessed for any DML operations. Thanks in advance. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 09:12:20
|
| We have triggers that store the "before" data into an Audit table.We also have columns for LastEditedOn and LastEditedBy in all our tables - which we find helpful when debugging problemsYou could do a one-off analysis by using SQL Profiler |
 |
|
|
carumuga
Posting Yak Master
174 Posts |
Posted - 2010-01-30 : 10:23:14
|
| There was no trigger or lastaffected column defined in the table level.In generic, is there a way to identify using query.Moreover, what u mean by one-off analysis by using SQL Profiler. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 10:30:48
|
"There was no trigger or lastaffected column defined in the table level."You could add that, going forwards ..."In generic, is there a way to identify using query."No"Moreover, what u mean by one-off analysis by using SQL Profiler."You could turn on SQL Profiler, log all activity, and from that work out when the table was accessed. However, that is only as good as the period of time you run it over, and it will be like looking-for-a-needle-in-a-haystack  |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-30 : 10:33:27
|
| Are you trying analyse a security problem? is it an application issue? What are you trying to establish in analysing the last access? This would define waht approach you takeJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
carumuga
Posting Yak Master
174 Posts |
Posted - 2010-01-30 : 10:49:44
|
| I need to know if any of the job run was affecting the tables. Just say for instance if more than one job affecting the tables at different scheduled time. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 10:53:43
|
| Triggers are the easiest / most comprehensive route for that, IMO.Example: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215 |
|
|
|
carumuga
Posting Yak Master
174 Posts |
Posted - 2010-01-30 : 11:07:03
|
| Thats ok and can be used in the future tracking but how to track the existing update with the table. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-30 : 11:12:09
|
| There is no way you can track unless you track it with Log Explorer for transactions that happened. You need to have Columns like Lastupdateid and LastUpdatedate to track the person and when. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-30 : 11:13:18
|
| And if you are talking about schema change, then you can run schema change report available in SQL 2005 to track it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 11:13:37
|
| as I have explained above.Or upgrade to SQL 2008 which has a tracking system. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 11:14:21
|
| You might also be able to use a utility that processes the transaction logs to find instances "after the fact". |
|
|
|
carumuga
Posting Yak Master
174 Posts |
Posted - 2010-01-31 : 22:20:41
|
| What about using the DMV, sys.dm_db_index_usage_stats, Will that help? I think this will be get used only in case of index defined in the table. |
|
|
|
|
Last access to the table.