Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Unanswered: When was each database last accessed?

    Hi!

    We host a lot of clients' databases and would like to avoid backing up those, that have not changed since the last backup.

    Does the server keep track of when each database was last accessed? Does it distinguish between merely accessing and modifying?

    Can this information be obtained via an SQL query (on some master..sys* tables, I presume)? Thank you!

  2. #2
    Join Date
    Sep 2005
    Location
    France, Paris
    Posts
    34
    Hello,

    You can implement audit database, and check if any operation (delete, update, insert etc...) were performed into your customers databases since the last backup.

    But I strongly recommend to make regular backup, even if nothing was modified.
    Last edited by jflebon; 11-16-05 at 18:38.
    Regards.

  3. #3
    Join Date
    Apr 2003
    Posts
    64
    Quote Originally Posted by jflebon
    You can implement audit database, and check if any operation (delete, update, insert etc...) were performed into your customers databases since the last backup.
    This may be too much of a performance hit during user's normal operation... Is not the last login recorded somewhere?

    Quote Originally Posted by jflebon
    But I strongly recommend to make regular backup, even if nothing was modified.
    Why? What's the point? We keep 3 most recent backups anyway -- we just don't want to waste time re-doing them...

    Thank you for your response.

  4. #4
    Join Date
    Sep 2005
    Location
    France, Paris
    Posts
    34
    Why not to create a login trigger, feeding a table each time a login connect to a database ?
    This way you can check if there was a connection since the last backup.


    I don't know about the way you handle your backup (transaction log, on tape, disk, number of days keeping etc...), integrity check of databases, so it was a recommandation nothing more.
    It's just my own experience...
    Regards.

  5. #5
    Join Date
    Apr 2003
    Posts
    64
    Quote Originally Posted by jflebon
    Why not to create a login trigger, feeding a table each time a login connect to a database?
    Yes, that's what we, probably, will do. Is it safe to keep this table in the master database? In fact, I'm surprised, this is not done automatically by Sybase -- sysdatabases has the crdate and the dumptrdate, why not also the lastlogindate, or some such?

    This will not distinguish the read-only access from the modifications, but that's, probably, enough for us...

    Would you have an example of such a trigger handy? Thanks!

  6. #6
    Join Date
    Sep 2005
    Location
    France, Paris
    Posts
    34
    Is it safe to keep this table in the master database? In fact, I'm surprised, this is not done automatically by Sybase -- sysdatabases has the crdate and the dumptrdate, why not also the lastlogindate, or some such?
    I do not recommend to keep it in master database or other system databases. You can still create a little database for your administration purposes only, with this table you can purge when you need.

    If there was such "lastlogindate" it could be nice of course, from v12.5 (correct me if I'm wrong) you have loggedindatetime column in master..sysprocesses, but only usefull unless the connection is ended.

    Quote Originally Posted by teterin
    This will not distinguish the read-only access from the modifications
    That's why I suggested audit database first. But depending the number of events you're auditing and the number of databases to audit, you have to be ready to handle it.
    Quote Originally Posted by teterin
    Would you have an example of such a trigger handy? Thanks !
    I think you can find an example here. You have to know that login trigger are supported from ASE v12.5
    Regards.

  7. #7
    Join Date
    Nov 2005
    Location
    Gex - France
    Posts
    15
    Hi,



    A login trigger feeding a table each time a login connect to a database don't tell you if there was some data modification.

    To solve that, you may also watch the number of lines in the SYSLOGS tables. But this will work only if the "trunc. log on check point" is not active.

  8. #8
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    You might also consider using the MDA tables for this if you are on 12.5.0.3 or later.

    In particular the monOpenObjectActivity records writes on a per object basis. You could create a small database that sores the state of this table at various intervals and checks activity by DBID to determine which databases have had any activity.

    For a description of the MDA tables I'd suggest visiting http://www.sypron.nl/mda.html

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •