Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘Log Shipping broken’

Between 4096 and 512 sector size issue.

Posted by Simon Cho on 11/16/2015

There are 2 issues in case of primary server has 512 sector size, and secondary server has 4096 sector size

  • You may see below message in the SQL server error log

There have been 170613760 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file

  • If you are using Log Shipping with standby mode on secondary server, standby mode may fail frequently.

Msg 9004, Level 16, State 6, Line 7
An error occurred while processing the log for database ‘MessageExchange_Sanpedro’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

 

Please check 4k and 512 basic concept in below url.

http://blogs.msdn.com/b/saponsqlserver/archive/2014/10/02/message-misaligned-log-ios-which-required-falling-back-to-synchronous-io-in-sql-server-error-log.aspx

http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

http://blogs.msdn.com/b/psssql/archive/2013/05/15/sql-server-storage-spaces-vhdx-and-4k-sector-size.aspx

 

Solution

https://support.microsoft.com/en-us/kb/3009974 : I don’t know how it work internally. It might force the primary to 4K alignment. Please fully test it out and apply it.

Note After you apply this hotfix, you have to turn on the trace flag 1800 to make this hotfix work correctly.

https://support.microsoft.com/en-us/kb/2987585 : This one is great. Need to turn on trace flag 3057

 

Thanks Bob ward and Robert Dorr for investigation.

Posted in Common | Tagged: , , , , | 2 Comments »

Backup chain is broken due to Veeam Backup configuration improperly.

Posted by Simon Cho on 11/29/2012

http://www.veeam.com/vmware-backup/help-center/v6_1/hyperv/index.html?vmware_replica_guest_processing.htm

Please select this one, “Do not truncate logs”.

——————————————————————————————————

Use the Truncation logs section to define the scenario of transaction log handing:

•Select Truncate logs on successful backup only if you want Veeam Backup & Replication to trigger truncation of logs only after the job is finished successfully. In this case, Veeam agent will wait for the replication to complete, and then will trigger truncation of transaction logs. If truncation of transaction logs is not possible for some reason, the logs will remain untouched in the VM guest OS till the next start of the Veeam agent.

•Select Truncate logs immediately if you want Veeam Backup & Replication to trigger truncation of logs in any case, no matter whether the job finishes successfully or fails.

•Select Do not truncate logs if you do not want Veeam Backup & Replication to truncate logs at all. This option is recommended if you are using another tool to perform guest-level replication and this tool maintains consistency of the database state. In such scenario, truncation of logs with Veeam Backup & Replication will break the guest-level replication chain and cause it to fall out of sync.

———————————————————————————————————————–

When I checked backup list, it’s truncate log like this.

BACKUP LOG xxxDB TO DISK = ‘NUL’

So, physical_device_name is presented as ‘NUL’.

select bs.*
	 , bf.*
  from msdb.dbo.dbo.backupset bs
  join msdb.dbo.[backupmediafamily] bf
    on bs.media_set_id = bf.media_set_id
 where 1=1
   and database_name='MyDB' and type='L'
 order by backup_start_date desc

Posted in Common | Tagged: , , | Leave a Comment »