How to find when database was last accessed or modified?

Posted on 2006-06-07
Last Modified: 2008-03-03
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.

Question by:gwiensx
    LVL 75

    Accepted Solution

    SQL Server does NOT automatically maintain a last accessed or last modified date for databases, tables, or columns.

    LVL 20

    Assisted Solution

    LVL 27

    Expert Comment

    You would have to set something up with code to do that. SQL Server does not maintain that metadata.
    LVL 42

    Expert Comment

    you need to use audit 3rd party SW such by
    or by Idera
    Create Audit Trigger

    or Red-gate compare data
    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
    LVL 42

    Expert Comment

    one more
    sql profiler
    Profiler's Blackbox Feature

    Author Comment

    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.


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now