Database Synchronization Status Synchronized = Secondary Replicas has caught up. No Data Loss. Synchronizing = Secondary Replicas is still catching up. Data Loss. Note: For Always On setup using “Asynchronous commit” Availability Mode, it is normal for the database to be in “Synchronizing” status. For Always On setup using “Synchronous commit” Availability Mode, the database... » read more
Error: DESCRIPTION: Always On Availability Groups transport has detected a missing log block for availability database “xxxxx”. LSN of last applied log block is (85862:908824:0). Log scan will be restarted to fix the issue. This is an informational message only. No user action is required. Low transaction throughput on Always On Availability Group primary replica... » read more
Problem: Job history window takes a long time or timeout. Fix: Make sure you purge the sql job history log. SQL Server Agent Properties -> History -> Check the 2 options. Note: “Remove agent history” is an one time event. Have to set the “Limit size of job history log” option to make it a... » read more
Trace flags are switches that adminstrators or developers can use to change the behavior of SQL Server. These are added by the development team, often to help debug or diagnose performance issues, according to BOL, but they are increasingly being used as feature flags to enable behavior that some customers may want, but Microsoft doesn’t want... » read more
Last Commit Redo Possible Fixes for Slow Sync Performance between Primary and Secondary Server Make sure Alwlays On is running without errors. Reboot the Always On secondary database server. Sources:
SQL Server® 2019 for Microsoft® Windows Latest Cumulative Update Cumulative Update Package 16 for SQL Server 2019 – KB5011644 https://www.microsoft.com/en-us/download/details.aspx?id=100809 Note: Restart server before and after service pack. For AlwaysOn setup, should be able to leave AlwaysOn in place and update each node server.
Setup to listen for events in “Errors and Warnings” Setup to listen for specific stored procedure name (TextData) and duration (greater than certain value) for SQL:BatchCompleted using Column Filters.
A hash warning event means that part of the data processed for a hash operation was written to tempdb. This means that a hash join or hash aggregate has run out of memory and been forced to spill information to disk (tempdb) during query execution, which can degrade the SQL Server performance. While spills are something... » read more
https://www.w3schools.com/sql/sql_join_full.asp The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in “Customers” that do not have matches in “Orders”, or if there are rows in “Orders” that do not have matches in “Customers”, those rows will be listed as well.