Results 1 to 9 of 9

Thread: last reload

  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: last reload

    Hi, I have a rather basic question.

    I am tryign to find out when a database was last reloaded.

    sp_helpdb

    only gives me create date, not reload.

    Thanks for any help.

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    When you load database, the entire target database is completely overwritten (at the page level), in place, from the dump file. The load date or the date of the dump database is not available. That and other relevant info exists in the dump file.

    The entire detail of every dump or load database is written to the BackupServer errorlog. Unless you prune or delete it, it is still there. Just grep "LOAD is complete" <BS_errorlog>.

    In production environments, where the load/dump is scripted, I write a single-line status to a history log file, which is visible to the world. This is required for simple audit purposes, but more importantly, anyone [without access to the secured $SYBASE directory tree] can quickly find (a) when a dump was executed [datetime, image names and sizes, etc] and (b) when a load was executed.

    If you tell us what you are trying to do, rather than just that specific question, there may be some other method to provide what you need.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  3. #3
    Join Date
    Oct 2009
    Posts
    4

    Thanks for the input

    Thanks a lot for the help.

    I am debugging some perl scripts, updating the training/test dbs from recent live dumps.

    I was also able to use on master:
    select dumptrdate from sysdatabases where name = 'mydb'

    As I knew that no dumps were being taken.

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by batthink
    Hi, I have a rather basic question.

    I am tryign to find out when a database was last reloaded.

    sp_helpdb

    only gives me create date, not reload.

    Thanks for any help.
    One trick is to check the device create date. That gets updated whenever theres a refresh. But if you add allocate extra device to a db after the load, then it will show that date.

    In the below example, the db was loaded on Sep 9 2009, but a device log04 was added to the db on Sep 15.

    sp_helpdb mydb

    name db_size owner dbid created status
    ------------------------ ------------- ------------------------ ------ ------------------ ------------------------------------------------------------------------------------------------------
    mydb 38400.0 MB sa 11 Mar 03, 2008 select into/bulkcopy/pllsort, trunc log on chkpt

    device_fragments size usage created free kbytes
    ------------------------------ ------------- -------------------- ------------------------- ----------------
    data10 2000.0 MB data only Sep 9 2009 11:34AM 768
    log04 2000.0 MB log only Sep 9 2009 11:34AM not applicable
    data10 8000.0 MB data only Sep 9 2009 11:34AM 737990
    log04 2500.0 MB log only Sep 9 2009 11:34AM not applicable
    data10 3300.0 MB data only Sep 9 2009 11:34AM 228978
    log04 100.0 MB log only Sep 15 2009 11:34AM not applicabl

  5. #5
    Join Date
    Oct 2009
    Posts
    4

    On my set up of Ase15

    Hi - Thanks a lot, but it doesn't seem to work for me.

    The device created date, remains as the date the device was created.

    The log file, and select dumptrdate from sysdatabases where name = 'mydb', both show the expected date of last load (today as it happens).
    Whilst sp_helpdb just shows the date device was created way back when.

  6. #6
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    master..sysdatabases.dumptrdate identifies the datetime of the last TRANSACTION LOG dump, not the db dump, not the db load. If you have truncate_log_on_checkpoint set on a particular database, then the log is being dumped approx every minute, so that column will "keep getting updated".

    The date that either a device was created, or a database allocation was added (ALTER DATABASE ON...) has no bearing on your question.
    Last edited by Derek Asirvadem; 10-05-09 at 11:19.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  7. #7
    Join Date
    Oct 2009
    Posts
    4

    Thanks

    we don't have truncate_log_on_checkpoint set on.

    For my puposes, dumptrdate works fine.

  8. #8
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by batthink
    Hi - Thanks a lot, but it doesn't seem to work for me.

    The device created date, remains as the date the device was created.

    The log file, and select dumptrdate from sysdatabases where name = 'mydb', both show the expected date of last load (today as it happens).
    Whilst sp_helpdb just shows the date device was created way back when.

    Odd. Just to confirm you are talking about load database and not dump database or tran?

    If thats the case, I guess there seems to be some difference in o/s or filesystem vs raw. The method I said, works for me in 12.5 on Solaris with raw devices.

  9. #9
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    For my puposes, dumptrdate works fine.
    Excellent. But then your purpose is evidently different from that which was stated initially "I am tryign to find out when a database was last reloaded."
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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