AlwaysOn Maintenance Jobs

Rebuild Index-


Rebuild index is resource intensive task and if we are rebuilding index in always on databases we need  to be extra careful as it will replicate all this changes to secondary replicas 
 
To minimize this impact we can follow some best practices 

1.Rather then running rebuild index on all the index at a time,We can check for index which are fragmented more than 30% and rebuild, If fragmentation is less then 30% we can reorganize the index 

2.Log file size may grow big during this activity we should monitor log file growth to understand the behavior

3. We can switch from synchronous to asynchronous mode to  improve the performance in primary replica during the rebuild to avoid  wait till the time it receives acknowledgement from secondary replica to commit the changes 




Comments

Post a Comment

Popular posts from this blog

How to read MSDTC Trace logs

I have recently come across situation where i need to read the logs of MSDTC  and it is not straight forward approach after researching little bit i am able to figure out the method ,Please find detail information on how to read MSDTC logs and let me know if you have questions in comments Steps to read MSDTC logs tracefmt.exe  will play important role reading MSDTC logs 1.Please check if you have tracefmt.exe in  C:\WINDOWS\system32\MsDtc location and  copy to C:\WINDOWS\system32\MsDtc\trace ,  if you don’t have tracefmt.exe in the location please download from https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11800 and need to install complete package from below link (I have attached tacefmt.exe in zip folder) 2.Once you install go to C:\WinDDK\7600.16385.1\tools\tracing\amd64 and you will find tracefmt.exe  and copy tracefmt.exe   to C:\WINDOWS\system32\MsDtc\Trace where you have trace files 3.If you al...
Read more

CDC Monitoring

Change data capture(CDC) is feature where we can track the changes on table if we enable CDC on that table and CDC should be enabled from DB level first and we can enable it for tables  As i see so many articles about CDC in the internet i don't  want to go through CDC this topic will discuss only about system tables in CDC and how to do basic monitoring , tracking the errors for CDC enabled tables  To Monitor the empty scans we can use this select statement and empty_scan_count column in sys.dm_cdc_log_scan_sessions is set to 1 whenever log scan result is empty and it will increment when ever it got empty result from scan SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0 And for latency between actuall table and CDC we can use below select SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 We can store data from the views     sys.dm_cdc_log_scan_sessions view and the sys.dm_cdc_er...
Read more

Error - Exclusive access could not be obtained because the database is in use. Msg 3101, Level 16, State 1, Line 3 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.

We need to alter database and change to single user mode before restoring the DB and to avoid connection issues if application still connecting to DB we can include in restore command ,So it will automatically make DB to single user mode before restoring and it will change to multi user mode after restoring automatically Use master Go Alter database set single_user with rollback immediate Go Restore database command Go Alter database set multi_user with rollback immediate Go
Read more