Post reply

Transaction Log Shipping

  • January 27, 2016 at 8:05 am

    #308092

    Accidental DBA....

    Have set up Transaction Log Shipping on 2 SQL Server 2012 boxes both version 11.0.5343 OS both boxes Windows 2012R2 fully service packed etc.

    This runs normally for most of the time, schedule every 20 minutes secondary database in Standby Mode: (Extract from SQL Server log)

    01/26/2016 22:05:04,spid52,Unknown,Setting database option MULTI_USER to ON for database 'isB1'.

    01/26/2016 22:05:04,Backup,Unknown,Log was restored. Database: isB1<c/> creation date(time): 2015/11/26(23:39:57)<c/> first LSN: 497439:160:1<c/> last LSN: 497439:407:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'\\AMLSQL02\Transaction Log Shipping\isB1\isB1_20160126220000.trn'}). This is an informational message. No user action is required.

    01/26/2016 22:05:04,spid52,Unknown,CHECKDB for database 'isB1' finished without errors on 2016-01-26 00:30:40.413 (local time). This is an informational message only; no user action is required.

    01/26/2016 22:05:03,spid52,Unknown,Starting up database 'isB1'.

    01/26/2016 22:05:03,spid52,Unknown,CHECKDB for database 'isB1' finished without errors on 2016-01-26 00:30:40.413 (local time). This is an informational message only; no user action is required.

    01/26/2016 22:05:03,spid52,Unknown,Recovery is writing a checkpoint in database 'isB1' (7). This is an informational message only. No user action is required.

    01/26/2016 22:05:03,spid52,Unknown,The tail of the log for database isB1 is being rewritten to match the new sector size of 4096 bytes. 512 bytes at offset 470528 in file C:\MSSQL\Data\isB1_log.ldf will be written.

    01/26/2016 22:05:03,spid52,Unknown,Starting up database 'isB1'.

    01/26/2016 22:05:01,spid52,Unknown,Setting database option SINGLE_USER to ON for database 'isB1'.

    However sporadically the log shipping fails with the following errors in the Secondary server log

    01/26/2016 22:45:05,spid55,Unknown,An error occurred during recovery<c/> preventing the database 'isB1' (7:0) from restarting. Diagnose the recovery errors and fix them<c/> or restore from a known good backup. If errors are not corrected or expected<c/> contact Technical Support.

    01/26/2016 22:45:05,spid55,Unknown,Error: 3414<c/> Severity: 21<c/> State: 1.

    01/26/2016 22:45:05,spid55,Unknown,An error occurred while processing the log for database 'isB1'. If possible<c/> restore from backup. If a backup is not available<c/> it might be necessary to rebuild the log.

    01/26/2016 22:45:05,spid55,Unknown,Error: 9004<c/> Severity: 16<c/> State: 6.

    01/26/2016 22:45:03,spid55,Unknown,Starting up database 'isB1'.

    01/26/2016 22:45:01,spid55,Unknown,Setting database option SINGLE_USER to ON for database 'isB1'.

    Can anybody give me any guidance as to why this is happing.

    On the primary server there is a maintenance plan which does a integrity check, re-index and Full backup plus the usual clean up jobs but this always occurs after the crash.

  • January 27, 2016 at 8:37 am

    #1854737

    It seems pretty obvious - someone or some thing is setting the database to single user mode. Find and prevent that and you should be good. Not sure if a server-level trigger can prevent that or not, but maybe it can.

    But I would THINK that you could simply reapply the failed tlog after getting out of single user mode and carry on like nothing happened...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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