[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

How to find when database was last accessed or modified?

For the purpose of backup audits, I can find when the last backup was done on our numerous distributed SQLServer databases. The missing piece is whether the database has been modified since last backup. We have archive databases which do not change, so I wouldn't spend a lot of worry if it hadn't been backed up in months as long as that date was after last modify date/time.

I have tried using the "xp_getfiledetails", but this does not correlate with the last time the database had a row updated or inserted, etc.

sysdatabases doesn't contain a date that correlates with such updates either.

All of our SQLServers are running at version 8 sp3a. SQL2005 is still a ways off for the majority of our current databases.

Any help would be appreciated.

Thanks.
0
gwiensx
Asked:
gwiensx
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SQL Server does NOT automatically maintain a last accessed or last modified date for databases, tables, or columns.


0
 
ptjcbCommented:
You would have to set something up with code to do that. SQL Server does not maintain that metadata.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Eugene ZCommented:
you need to use audit 3rd party SW such by
Lumigent http://www.lumigent.com/products/auditdb.html
or by Idera
http://www.idera.com/Products/SQLcm/Updates.aspx
or
Create Audit Trigger
http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk


or Red-gate compare data
http://www.red-gate.com/products/sql_tools_overview.htm
---
or just compare sizes of Data and log files - before and after
--
or if you can restore old database as -Old
and run select * from sysfiles
or exec sp_spaceused
you can see used and preallocated space
0
 
Eugene ZCommented:
one more
sql profiler
or/and
Profiler's Blackbox Feature
http://www.sqlmag.com/Article/ArticleID/16078/sql_server_16078.html
0
 
gwiensxRetiredAuthor Commented:
Perhaps luck gave aneeshattingal the advantage of being first, but he said it all -- it isn't part of SQLServer's metata (a desert by comparison to some other databases).

But I also thank Sirees for pointing me to an alternate thread with some 'possibilities' (although the thread is over 5 years old).

Unfortunately, I could not read the article sent by EugeneZ, but it implied continuously running Profiler which would not be acceptable.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now