Troubleshooting SQL Server
Log Files Job History Logs Database Server -> SQL Server Agent -> Job Activity Monitor -> Jobs -> View History SQL Server Logs Database Server -> Management -> SQL Server Logs Windows Event Viewer Windows -> Event Viewer
SQL Server Audits
Database Server -> Security -> Audits Database Server -> Security -> Server Audit Specifications Note: Make sure the audit file does not reach limit on 1) Number of Files 2) File Size Error 33206 – SQL Server Audit Failed to Create the Audit File SQL Server Audit failed to create the audit file ‘L:\ServerAudit\ServerAudit_EEFFB046-2AA7-4ACA-B91A-9F1FF5EF00EC.sqlaudit’. Make... » read more
Error 33206 – SQL Server Audit Failed to Create the Audit File
Error From Windows Event Viewer: SQL Server Audit failed to create the audit file ‘L:\ServerAudit\ServerAudit_EEFFB046-2AA7-4ACA-B91A-9F1FF5EF00EC.sqlaudit’. Make sure that the disk is not full and that the SQL Server service account has the required permissions to create and write to the file. Resolution: Change setting in the SQL Server Audit configuration or clear out files in... » read more
Database Fragmentation
The SQL Server Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does... » read more
Index Rebuild vs Reorganize
The SQL Server Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does... » read more
GETDATE() vs SYSDATETIME()
SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. Sources: https://docs.microsoft.com/en-us/sql/t-sql/functions/sysdatetime-transact-sql?view=sql-server-ver15
Add Column with Collate
Sources: https://dba.stackexchange.com/questions/110911/latin1-general-bin-performance-impact-when-changing-the-database-default-collati
Setting Up Full Text Index
Make sure database is setup for full text index. 2. Create Catalog Database -> Storage -> Full Text Catalogs -> New Full-Text Catalog 3. Define Full-Text Index Database -> Table -> Full-Text Index -> Define Full-Text Index Note: Make sure the table has at least one unique index. 4. Populate Catalog Database -> Table ->... » read more
Full Text Index Search
CONTAINS FREETEXT Sources: https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver15
Populate Full Text Index
Sources: https://docs.microsoft.com/en-us/sql/relational-databases/search/create-and-manage-full-text-indexes?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/search/populate-full-text-indexes?view=sql-server-ver15 https://stackoverflow.com/questions/2727911/how-can-i-know-when-sql-full-text-index-population-is-finished