Sql-server – Transaction log is full changing column type in a large table

Best practice is to REORGANIZE below around 30% fragmentation and REBUILD above this. Simply, REBUILD makes a clean copy, REORGANIZE does it in-situ.

Check what you're actually doing: you don't have a maintenance plan doing both do you?

On larger tables (50GB table is getting there) I've seen REORGANIZE consume all transaction log space if you follow this rule. Not often: only one system with a certain load pattern. The REORGANIZE just ran until the log expanded and consumed all disk space.

We switched to REBUILD instead with no more problems, but ignored fragmentation below 25%. This worked better for us: you'll have to see if it works for you.

REBUILD may affect the performance more than REORGANIZE in production, but this can sometimes be mitigated with the ONLINE option (Enterprise Edition required).

Points, in no particular order:

• You do need the transaction log for a whole bunch of reasons I'm not going to go into. That said, if you're running out of transaction log space while you're in simple recovery mode, that probably means you have long-running transactions. Make your transactions as short as possible (gather all required user input before you start a transaction). (When SQL Server is trying to free log space, it can only free up to the start of the oldest current transaction.)
• Making NVARCHAR(MAX) columns nullable will not necessarily save you much space (off the top of my head it'll be 2 bytes per row, per column).
• Deleting/archiving old data will help you - with a caveat that it has to pass through the transaction log first.
• Shrinking your database will not help you, as a matter of fact it will kill performance (both while it's running and afterward, since DBCC SHRINKDATABASE uses a naive algorithm that almost perfectly fragments your indexes) and won't regain you much if any space.