SQL Server Performance

backups not being applied to warm standby

Discussion in 'SQL Server Log Shipping' started by grangep, Feb 27, 2004.

  1. grangep New Member

    I am using the log shipping method that is detaild on this site to maintain a warm standby server. The problem I am having is the full database backup that I run at 10:00 PM and the log files that I create every 15 minutes between 6:00 AM and 9:45 PM during the day don't consistently get applied to the warm standby server. The files all appear to get copied over to the the warm standby fine because I see them and they have the correct date timestamps. But, if I examine the SQL Server log on the warm standby sometimes I do not see any entries indicated any logs have been restored or sometimes I see them but then the messages stop at a certain time. Typically one would see the following entries in the log when a restore happens:

    2004-02-26 22:47:56.37 backup Database restored: Database: CWS_PROD, creation date and time: 2002/04/30(14:11:20), first LSN: 14933:353:1, last LSN: 14933:489:1, striped: 0, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:ackupscws_prod_backup_device.bak'}).

    2004-02-27 06:00:19.56 backup Log restored: Database: CWS_PROD, creation date and time: 2002/04/30(14:11:20), first LSN: 14933:489:1, last LSN: 14933:12252:1, striped: 0, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:ackupscws_prod_log_backup_device.bak'}).

    But like today, the restores just stopped at 1:30 PM. I see log files on my warm standby server that were copied to it after 1:30 PM but there is just no indication of anything happening.

    Has anyone else encountered this?

    One other thing, it has been suggested that the warm standby could be used for reporting purposes if set to read only. But, how can you allow users to run queries against the warm standby server because when you apply the logs won't that either block the users from logging in during a restore or won't a restore be blocked if users are querying the database?
  2. satya Moderator

    Check SQL error log on standby server for any information about log restore failure.

    After you have configured log shipping, use the monitor server to view information about the status of all the log shipping servers. ANd also from EM goto Management and click on LogSHippingMonitor and click on STATUS tab for details.

    Go thru thishttp://www.sql-server-performance.com/sql_server_log_shipping.asp link about overview of log shipping and use standby server for reporting purposes.

    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