How to Manage Big tables in SQL server? 

Managing or Maintenances of Big tables need proper attention. Below are few of the scenarios when it required proper attention, other it can bring down the DB server itself. 

Case-1 : Alter table to modify the column data type
Case-2 : Data Purge
Case-3 : Adding Index

1. Check the Size of the Table, No of Rows exe SP_SpaceUsed 'Tablename' 

2. Check the Free space of Data and Log Files of the database of the Table

3. Check the Free space of Data and Log Files of TempDB database

4. Check the Data\Log and TempDB drives free space and ensure they have sufficient free space. 

5. Make sure Index creation is ONLINE=ON 

Practical experience : 
I had to change the data type of the column in a table but Primary Key was defined on that column. 
In that case, I had to drop the Primary Key, change the column data type and create the Primary back on the same table. 
I tried all three steps in single transaction but Creating of Primary Key took very long time. Table was around 34GB in size. During the Index creation process, Log file started growing rapidly. There was a point where Log drive had zero free space. Immediately, disabled the growth of existing Log file and created a new Log file on other drive. In this scenario, it would have affected the other databases in the server. Once the Index creation completed, shrunk the Log files and released some space on the drive. Enabled the growth on the old log file and removed the new Log file which was created. 

 Change the Default Collation of SQL server, Database 

I had installed a SQL server with default collation SQL_Latin1_General_CP1_CI_AS but later realized it should be SQL_Latin1_General_CP850_CI_AS. I followed the below steps. 

1. Change SQL Server (Instance) Collation

⚠️ You cannot change the server collation without rebuilding the system databases, which means reinstalling SQL Server components.

Steps:

• Backup all user databases and settings

• Use setup.exe from installation media with REBUILDDATABASE flag

Example Command:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=domain\user /SAPWD=StrongPassword /SQLCOLLATION=New_Collation_Name

🔁 Example collation: SQL_Latin1_General_CP1_CI_AS

🧷 This will remove all system data, including logins and jobs. Use with caution.

INSTANCENAME= MSSQLSERVER for Default Instance

              ServerName\Instance for named instance

SQLSYSADMINACCOUNTS = Must provide windows account

SAPWD = Its going to be new password for 'SA' account.

SQLCOLLATION = New collation to be changed

2. Change Database Collation

You can change a database's default collation only if no tables exist, or you'll have to recreate all objects manually.

Example (for empty or new DB):

ALTER DATABASE YourDatabaseName COLLATE New_Collation_Name;


3. Change Column Collation
To change collation for specific columns:
ALTER TABLE YourTable
ALTER COLUMN YourColumn VARCHAR(100) COLLATE New_Collation_Name;
⚠️ This can fail if the column is part of an index or constraint.

4. Specify Collation in Queries
For joining or comparing across different collations:

SELECT * FROM TableA JOIN TableB ON TableA.Name 
COLLATE Latin1_General_CI_AS = TableB.Name COLLATE Latin1_General_CI_AS; 

Resolving Disconnected Secondary Replica or Database in Always On Availability Groups

It’s not uncommon to encounter situations where either the secondary database or the secondary replica enters a disconnected state in an Always On Availability Group (AG). This typically occurs during initial AG configurations or after events like OS/SQL patching, network issues, or failovers.

Recently, I faced a scenario where not only was the secondary database in a disconnected state, but the entire secondary replica was also marked as disconnected in the AG dashboard. As a result, the transaction log (T-log) started growing due to unsynchronized data movement.

Below are the steps I followed to troubleshoot and resolve the issue:

Steps to Resolve:

1. Connected to both Primary and Secondary replicas via SSMS.

2. On the Secondary node, navigated to:
   Always On High Availability > Availability Groups (AVG) > [Your AG Name]

3. Under Availability Databases, right-clicked the affected database, then:
   Suspend Data Movement > Resume Data Movement

   Note: This did not resolve the issue.

4. Restarted the HADR Endpoint on both replicas:

   
   SELECT * FROM sys.endpoints;
   
   ALTER ENDPOINT [Hadr_endpoint] STATE = STOPPED;
   ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
   

   This also did not resolve the issue initially.

5. Granted CONNECT permissions to the SQL Server service account (used by the secondary replica) on both nodes:

   
   GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\SQLServiceAccount];
   

6. After applying the endpoint permission, I re-executed Step 4 (restarting endpoints).
   This time, the secondary replica successfully reconnected, and data synchronization resumed.