General database mail troubleshooting steps

Note: If no errors seen on the database side, it could be some problem with the outgoing STMP email server. Sources: https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-general-troubleshooting?view=sql-server-ver16

Restore Database From Full/Diff/Log Backup Files

1 One2 Two3 Three4 Before full backup <FULL BACKUP>5 After full backup6 Before LOG backup 1 <LOG BACKUP 1>7 After LOG backup 18 Before DIFF backup <DIFF BACKUP>9 After DIFF backup10 Before LOG backup 2 <LOG BACKUP 2>11 After LOG backup 212 Before LOG backup 3 <LOG BACKUP 3>13 After LOG backup 3 Restore Options... » read more

The certificate chain was issued by an authority that is not trusted

Issue: Database Connection String: Encrypt=True;TrustServerCertificate=False If you recently upgraded your SQL Server Native Client 11.0 (Provider=SQLNCLI11) application to use Microsoft OLE DB Driver 19 for SQL Server (Provider=MSOLEDBSQL19), you might receive error messages that resemble the following messages: [Microsoft OLE DB Driver 19 for SQL Server]: Client unable to establish connection [Microsoft OLE DB Driver... » read more

In-Place Upgrade from SQL Server 2016 to 2019

Upgrade from SQL Server 2016 (13) to SQL Server 2019 (15) How to Upgrade SQL 2016 to SQL 2019 Mount the SQL 2019 ISO or media on your current SQL 2016 server. Select “Upgrade from a previous version of SQL Server” Note Make sure the current Windows versions can support the new SQL Server 2019.

MAXDOP Settings to Limit Query to Run on Specific CPU

Query Hint MAXDOP – Maximum Degree Of Parallelism can be set to restrict query to run on a certain CPU. Please note that this query cannot restrict or dictate which CPU to be used, but for sure, it restricts the usage of number of CPUs in a single batch. Sources:

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.