Today I came across with an error with "Row-Overflow Data Exceeding 8 KB" when trying to update the value in a row.
Reason

MS SQL server allows only 8060 bytes of data max to be stored in a row. Hence your row size will always be <= 8060. But it is relaxed when a table contains varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type colums.

Please see below query how to figure out which row takes how much space:
Query
  1. -- Declaring variables
  2. declare @table nvarchar(128);
  3. declare @idcol nvarchar(128);
  4. declare @sql nvarchar(max);
  6. --initialize those two values
  7. set @table = '[Person].[AddressType]'
  8. set @idcol = 'AddressTypeID, Name'
  9. set @sql = 'select ' + @idcol + ' , (0'
  11. -- This select statement collects all columns of a table and calculate datalength
  12. select @sql = @sql + ' + isnull(datalength(' + name + '), 1)'
  13. from sys.columns where object_id = object_id(@table)
  14. set @sql = @sql + ') as RowSize from ' + @table + ' order by rowsize desc'
  16. -- Execute sql query
  17. exec (@sql)
Result:

Above SQL query just collects all the columns present in a table and summing up the data size using datalength().
Happy Coding !!