SQL Server Performance

Secondary Server copies same file

Discussion in 'SQL Server Log Shipping' started by Jack, Jan 7, 2004.

  1. Jack New Member

    Hi All,

    We have Sql 2000 Enterprise Edition on Win 2K.

    Yesterday we had to reboot our primary at 9 AM for a problem. The primary was down for approximately 15 minutes. When we got the primary up again, 4 out of 5 databases were out of sync. For these 4 databases, the same transaction log file ( say testdb_tlog_200401060900.TRN ) was being copied over and over again. I tried

    A. updating the system tables ( log_shipping_secondaries, log_shipping_plan_databases ) but that didnt work.

    B. Restarting the actual jobs that do the copy and restore

    C. Change the time interval ( it was 5 minutes to copy and restore ). Since the server was brought up at 9:16 AM, I assumed that Sql Server was trying to pick up testdb_tlog_200401060905.TRN , testdb_tlog_200401060910.TRN and so on and would not find testdb_tlog_200401060916.TRN

    Nothing worked. Finally we had to dump the databases and copy them over the WAN which is painful and time consuming.

    Any idea why this thing happenns ?

    TIA,
    Jack
  2. Luis Martin Moderator

    What was the razon to reboot primary?.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. Jack New Member


    That was an entirely different issue. There is a bug with Sql Server Enterprise edition where it starts spinning in an infinite loop waiting for latches. As a result, we could not do backups and restores and had to restart the primary box.

  4. satya Moderator

    FYI, Latches are not held for the duration of a transaction.

    "Plain" latches are generally unrelated to I/O. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages (PAGELATCH_x is used for that).

    Possibly the most common case is contention on internal caches (not the buffer pool pages), especially when using heaps, text, or both.

    What are resources available on the system in terms of memory, physical disk space and any other applications sharing these resources.

    Above all what is the level of service pack on SQL & OS and do you lightweight pooling option.

    For the log shipping, if for any reason the transaction log restore chain in broken you must re-apply full backup from source to the destination in order to continuation of corresponding transaction log backups.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page