Modified_date is not changing in sys.tables!!!!!!

Question

Modified_date is not changing in sys.tables!!!!!!

Tac11

SSCertifiable

Points: 6980
More actions
March 11, 2015 at 5:55 am

#315641

I ran:
Update HumanResources.Department set Name = 'Doctor' where DepartmentID = 1
and then ran:
SELECT * FROM sys.tables order by modify_date desc
But modify_date didn't change to today's date!!!! am I missing something here?

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

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

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

Modified date tracks schema changes. Not data changes.

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

Tac11 SSCertifiable Points: 6980 More actions · Answer 2

Thanks for quick reply. How do I find which tables (data) were changed by date?

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

Short answer: You don't.

Custom auditing if you want accurate, persisted data. Or, you can look at sys.dm_db_index_usage_stats, however that only retains information until SQL Server or the database is restarted.

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

Lowell SSC Guru Points: 323518 More actions · Answer 4

you'd have to add something to the table ; either a new column of type datetime, plus a trigger to always populate it on update, or a column of type rowversion, so that you could tell the last row that was modified, but not WHEN it was modified.

if you need to actually track those things, ChangeTracking or Change Data Capture, or maybe SQL Audit are all possibilities.

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!

Tac11 SSCertifiable Points: 6980 More actions · Answer 5

Thanks Lowell. What I am trying to find out on my Prod Database is which tables haven't been accessed, modified or updated for long time, so I can make a case to remove it. Is there best way to do this? Thanks.

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

Add one of the things that Lowell mentioned and monitor for a complete business cycle, be sure to include month and and year end in the time that you monitor for.

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 7

You can also get some idea of what's been used by querying sys.dm_db_index_usage_stats. That includes information for the last scan, last seek, last lookup, and last update for each index and heap.

Just keep in mind that in some situations those statistics will get cleared for a database or particular index (http://blogs.msdn.com/b/ialonso/archive/2012/10/08/faq-around-sys-dm-db-index-usage-stats.aspx has some good information). Also keep in mind that any indexes or heaps not accessed since the last restart will not have any rows here, so that's easy to exploit to see what hasn't been accessed.

That's a decent method for getting an idea of what's been used and how recently it was used, but for long term auditing or the like, you'd want to use one of the options Lowell mentioned.

Cheers!