Post reply

Checking date of last DB change...

  • January 27, 2004 at 4:20 am

    #162990

    We have several databases that are read only on a server.  We also have an integrity management script that checks various settings including backup history.  Our standard backup policy (on non r/o databases) is to full backup weekly and diff backup daily.  For read-only we only want to backup after a change (and reset of read-only flag).

     

    Is there any way to detect when the database was last changed?

    I thought I'd found the solution in sysfiles looking at the status column.  According to BOL a status of 0x80 indicates that the "File has been written to since last backup".  But this flag is not set for any of the databases - even those I know have changed.

    BTW I'm using something like

    EXEC sp_MSforeachdb @command1="select db_name() Name,* from sysfiles where status & 128 = 128"

    to look for this test case - 128 is decimal of 0x80 hex

     

    Any ideas why the status flags are not being set correctly?  Alternatively is there any way that we can get the last changed date for a database?

     

    Thanks in advance

     

    Mark

  • January 28, 2004 at 8:18 am

    #491975

    Ah, I found someone else who fell for the "File has been written to since last backup" flag ... Wouldn't that be nice?

    There may be a compromise...

    "SELECT * FROM ::fn_virtualfilestats(dbid,dbfile_id)" shows number of writes to a database file. If you capture these readings for each database file, then you can monitor them every few minutes to detect when #of writes changes. 

    Of course, you'll have to save previous settings, etc, & do some scripting but this is the closest you'll come to detecting when a db has changed.

    If you're dealing with Accountants who, for some reason, often need to know things down to the cent/nanosecond/molecule, this may not work.

    I've only used the ::fn_virtualfilestats to locate high i/o on database files. Those numbers seem accurate.

    hope this helps.


    TONYMARKS

Viewing 2 posts - 1 through 1 (of 1 total)

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