Shrink Database (DbccFilesCompact)
Shrink Database (DbccFilesCompact) Database -> Right Click -> Tasks -> Shrink -> Database Example: 3 hrs to Shrink 1,100 GB data file to 296 GB.
SQL Server Upgrade from 2014 to 2022
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2022?view=sql-server-ver16 https://sqlserverbuilds.blogspot.com/2014/01/sql-server-2014-versions.html Upgrades from earlier versions to SQL Server 2022 SQL Server 2022 (16.x) supports upgrade from the following versions of SQL Server: SQL Server 2012 (11.x) SP4 or later SQL Server 2014 (12.x) SP3 or later SQL Server 2016 (13.x) SP3 or later SQL Server 2017 (14.x) SQL Server 2019 (15.x) Note: Need to... » read more
login failed for user ‘server name\sql server instance$’. reason: could not find a login matching the name provided. [client: ]
Error: Login failed for user ‘DomainName\ServerName$’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] Possible Causes: User application that is accessing this database is running under the default Windows service account. Check the user application in Services and make sure it is running under a specific service account and not... » read more
Slow DELETEs
Issue: Single row delete should not take 2 minutes to complete Cause: Looking at execution plan, a lot of processing on a different table with foreign key link to updating table. Foreign keys are constraints in the database to ensure that data values used in child table entry are present in a parent, reference table.... » read more
How to bring reverting/in recovery secondary db back online after failover
Issue: Secondary Database stuck in “Reverting/In Recovery” after Failover. Resolution: The only way to fix is by restarting the secondary machine, it then will kill the system process which was locking the master db. Sources: https://learn.microsoft.com/en-us/answers/questions/138501/how-to-bring-reverting-in-recovery-secondary-db-ba
Database stuck “In Recovery” mode and database is not in a state that allows access checks
Error: Getting the following error when trying to bring the database back online. User does not have permission to alter database 'xxxxxx', the database does not exist, or the database is not in a state that allows access checks. -- Database is "RECOVERING" user_access_desc is_read_only state_desc is_in_standby MULTI_USER 0 RECOVERING 0 Resolution: Wait for database... » read more
SSMS takes a long time to open up connection for each new query window
Issue: SSMS takes a long time to open up connection for each new query window. This applies to all other applications that connects to the database as well. Fix: Disable The Publisher Certificate Check Part of the SSMS start-up routine involves connecting to the Microsoft website to check a certificate list. You can disable this... » read more
Active Directory Setup for SQL Server Always On Setup using Failover Cluster
Active Directory Need to create 2 AD objects. Cluster Host Listener Host Object needs to be… In the same AD folder. Add self, cluster, all customer hosts to the security tab with full control. Set the object to “Inactive” state. Inactive account. IP address will be enterer in at setup time. No need to add... » read more
With Index
Usually the DB engine should automatically select the index to use based on query execution plans it builds. However, there are some pretty rare cases when you want to force the DB to use a specific index.