none
Transaction Log Backup on the secondary Replica RRS feed

  • Obecná diskuse

  • Hi Guys,

    I have a situation that I've been trying to figure it out for last couple of days now.

    I do have AlwaysOn setup with 2 Secondary Replica. 

    I am using Ola Haegreen scripts to automate my backups jobs.

    I have full bak once a every day at 12am

    And Tlog bak hourly.

    on one of my secondary Tlog backup are taking too long to finish to the point where i am cancelling it. the job is not finishing to run. but on other secondary replica it only takes 15 to 20 seconds to complete.

    what might be the problem?

    I have tried to change, re-create the job but it's the same situation. 

    can you guys help?

    Thank you,

    Omar

    pondělí 8. července 2019 16:09

Všechny reakce

  • Have you checked what the backup session is waiting on? Are both secondaries synchronized with the primary? 

    Is there a difference in the number of concurrent users/processes connected to the slower secondary replica v/s the faster secondary? 

    Are the two secondaries in the same Data Center as primary? Maybe there's I/O latency.

    I'd start by looking at the waits (for the t-log backup session) and dig further. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    pondělí 8. července 2019 17:44
  • Hello Mohsin,

    Thank you for getting back to me, 

    the user are connecting to the faster secondary.

    on the two secondary, the slower one is not in the same data center with Primary. Primary is in canada and the slower socondary is in the USA.

    I will now check the wait time as you recommended.

    Thank you again,

    pondělí 8. července 2019 17:50
  • From what I can see i do have a Cumulative wait time of 88403 Network I/O and 

    367166 Backup

    Other 693791

    pondělí 8. července 2019 17:54
  • what is the version of your SQL server? if it's 2016+ you can monitor session-level waits with sys.dm_exec_session_wait_stats. Anyway, are you sure it was simply "Backup" that showed up as a wait type and not "BACKUPIO" or something? Also, is the faster secondary in USA as well?

    You can also monitor the waits using Dmitri's script below. 

    /****************************************************************************/
    /*                         SQL Server Internals v3                          */
    /*                           Training Materials                             */
    /*                                                                          */
    /*                  Written by Dmitri V. Korotkevitch                       */
    /*                      http://aboutsqlserver.com                           */
    /*                        dk@aboutsqlserver.com                             */
    /****************************************************************************/
    /*                         Diagnostics Scripts                              */
    /*                           Wait Statistics                                */
    /****************************************************************************/
    
    
    /*** Clearing Waits ***/
    -- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) 
    
    /*** Checking Top Waits in the System ***/
    ;with Waits
    as
    (
    	select 
    		wait_type, wait_time_ms, waiting_tasks_count,signal_wait_time_ms
    		,wait_time_ms - signal_wait_time_ms as resource_wait_time_ms
    		,100. * wait_time_ms / SUM(wait_time_ms) over() as Pct
    		,row_number() over(order by wait_time_ms desc) AS RowNum
    	from sys.dm_os_wait_stats with (nolock)
    	where 
    		wait_type not in /* Filtering out non-essential system waits */
    		(N'BROKER_EVENTHANDLER',N'BROKER_RECEIVE_WAITFOR',N'BROKER_TASK_STOP'
    		,N'BROKER_TO_FLUSH',N'BROKER_TRANSMITTER',N'CHECKPOINT_QUEUE',N'CHKPT'
    		,N'CLR_SEMAPHORE',N'CLR_AUTO_EVENT',N'CLR_MANUAL_EVENT'
    		,N'DBMIRROR_DBM_EVENT',N'DBMIRROR_EVENTS_QUEUE',N'DBMIRROR_WORKER_QUEUE'
    		,N'DBMIRRORING_CMD',N'DIRTY_PAGE_POLL',N'DISPATCHER_QUEUE_SEMAPHORE'
    		,N'EXECSYNC',N'FSAGENT',N'FT_IFTS_SCHEDULER_IDLE_WAIT',N'FT_IFTSHC_MUTEX'
    		,N'HADR_CLUSAPI_CALL',N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',N'HADR_LOGCAPTURE_WAIT'
    		,N'HADR_NOTIFICATION_DEQUEUE',N'HADR_TIMER_TASK',N'HADR_WORK_QUEUE'
    		,N'KSOURCE_WAKEUP',N'LAZYWRITER_SLEEP',N'LOGMGR_QUEUE' 
    		,N'MEMORY_ALLOCATION_EXT',N'ONDEMAND_TASK_QUEUE',N'PARALLEL_REDO_WORKER_WAIT_WORK'
    		,N'PREEMPTIVE_HADR_LEASE_MECHANISM',N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS'
    		,N'PREEMPTIVE_OS_LIBRARYOPS',N'PREEMPTIVE_OS_COMOPS',N'PREEMPTIVE_OS_CRYPTOPS'
    		,N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS'
    		,N'PREEMPTIVE_OS_GENERICOPS',N'PREEMPTIVE_OS_VERIFYTRUST',N'PREEMPTIVE_OS_FILEOPS'
    		,N'PREEMPTIVE_OS_DEVICEOPS',N'PREEMPTIVE_OS_QUERYREGISTRY'
    		,N'PREEMPTIVE_OS_WRITEFILE',N'PREEMPTIVE_XE_CALLBACKEXECUTE'
    		,N'PREEMPTIVE_XE_DISPATCHER',N'PREEMPTIVE_XE_GETTARGETSTATE'
    		,N'PREEMPTIVE_XE_SESSIONCOMMIT',N'PREEMPTIVE_XE_TARGETINIT'
    		,N'PREEMPTIVE_XE_TARGETFINALIZE',N'PWAIT_ALL_COMPONENTS_INITIALIZED'
    		,N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
    		,N'QDS_ASYNC_QUEUE',N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
    		,N'REQUEST_FOR_DEADLOCK_SEARCH',N'RESOURCE_QUEUE',N'SERVER_IDLE_CHECK'
    		,N'SLEEP_BPOOL_FLUSH',N'SLEEP_DBSTARTUP',N'SLEEP_DCOMSTARTUP'
    		,N'SLEEP_MASTERDBREADY',N'SLEEP_MASTERMDREADY',N'SLEEP_MASTERUPGRADED'
    		,N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',N'SLEEP_TEMPDBSTARTUP'
    		,N'SNI_HTTP_ACCEPT',N'SP_SERVER_DIAGNOSTICS_SLEEP',N'SQLTRACE_BUFFER_FLUSH'
    		,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'SQLTRACE_WAIT_ENTRIES',N'WAIT_FOR_RESULTS'
    		,N'WAITFOR',N'WAITFOR_TASKSHUTDOWN',N'WAIT_XTP_HOST_WAIT'
    		,N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',N'WAIT_XTP_CKPT_CLOSE',N'WAIT_XTP_RECOVERY'
    		,N'XE_BUFFERMGR_ALLPROCESSED_EVENT', N'XE_DISPATCHER_JOIN',N'XE_DISPATCHER_WAIT'
    		,N'XE_LIVE_TARGET_TVF',N'XE_TIMER_EVENT')
    	--and wait_time_ms > 0
    )
    select
    	w1.wait_type as [Wait Type]
    	,w1.waiting_tasks_count as [Wait Count]
    	,convert(decimal(12,3), w1.wait_time_ms / 1000.0) as [Wait Time]
    	,convert(decimal(12,1), w1.wait_time_ms / w1.waiting_tasks_count) as [Avg Wait Time]
    	,convert(decimal(12,3), w1.signal_wait_time_ms / 1000.0) as [Signal Wait Time]
    	,convert(decimal(12,1), w1.signal_wait_time_ms / w1.waiting_tasks_count) as [Avg Signal Wait Time]
    	,convert(decimal(12,3), w1.resource_wait_time_ms / 1000.0) as [Resource Wait Time]
    	,convert(decimal(12,1), w1.resource_wait_time_ms / w1.waiting_tasks_count) as [Avg Resource Wait Time]
    	,convert(decimal(6,3), w1.Pct) as [Percent]
    	,convert(decimal(6,3), w1.Pct + IsNull(w2.Pct,0)) as [Running Percent]
    from
    	Waits w1 cross apply
    	(
    		select sum(w2.Pct) as Pct
    		from Waits w2
    		where w2.RowNum < w1.RowNum
    	) w2
    where
    	w1.RowNum = 1 or w2.Pct <= 99
    order by
    	w1.RowNum 
    option (recompile);
    go
    
    /*** Comparing Signal and Resource Waits ***/
    select 
    	sum(signal_wait_time_ms) as [Signal Wait Time (ms)]
    	,convert(decimal(7,4), 100.0 * sum(signal_wait_time_ms) / 
    		sum (wait_time_ms)) as [% Signal waits]
    	,sum(wait_time_ms - signal_wait_time_ms) as [Resource Wait Time (ms)]
    	,convert(decimal(7,4), 100.0 * sum(wait_time_ms - signal_wait_time_ms) / 
    		sum (wait_time_ms)) as [% Resource waits]
    from
    	sys.dm_os_wait_stats with (nolock)
    option (recompile);
    go
    
    
    /*** List of currently waiting tasks ***/
    select
    	wt.session_id
    	,wt.wait_type
    	,wt.wait_duration_ms
    	,wt.blocking_session_id
    	,wt.resource_description
    from 
    	sys.dm_os_waiting_tasks wt with (nolock)
    order by 
    	wt.wait_duration_ms desc
    option (recompile);
    
    -- SQL Server 2016+. Session-Level waits
    select * 
    from sys.dm_exec_session_wait_stats 
    where session_id = 147; 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    pondělí 8. července 2019 22:31