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.