Manage the Size of the SSISDB (Catalog Database)

If VERSION_CLEANUP_ENABLED is set to FALSE, enable it. Update the RETENTION_WINDOW to the number best suited for business. For example, if the business requirement is to retain the operation maintenance records for 100 days, update the RETENTION_WINDOW property to 100. Note: Changing the RETENTION_WINDOW setting will cause background purging which will may fill up the... » read more

Unable to clear transaction logs for Always On Database

Error: Getting unable to clear transaction log error when trying to run a transaction log backup. Also unable to expand log size limits. Resolution: Make sure Always On databases are working and synchronizing on all secondary database servers. You will not be able to clear the transaction log if there is a problem with Always... » read more

SQL Jobs takes longer to run via SQL Agent

Issue: SQL Job takes longer to run via SQL Agent. Very fast when run manually but takes a very long time to run via scheduled. Stored Procedure times out when called from app. Stored Procedure runs very fast when ran manually. Resolution: Restart Windows and database server.

Always On Replica Synchronization Status

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

Always On Availability Groups transport has detected a missing log block for availability database

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

Purge Sql Job History

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

SQL Server Trace Flag

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

Install Latest Service Pack for SQL Server 2019

SQL Server® 2019 for Microsoft® Windows Latest Cumulative Update Cumulative Update Package 16 for SQL Server 2019 – KB5011644 Note: Restart server before and after service pack. For AlwaysOn setup, should be able to leave AlwaysOn in place and update each node server.