I have 3rd part app with a table that's 450GB which contains file attachments in a TEXT column (yeah, I know). With a document retention angle, I've exported about 35% of the older attachments to disk using a portable drive. The TEXT column now has a note that attachment was archived (beneficial to the app's UI) - from multiple MB/GB down to less than 1KB.
I know the argument against shrinking the database. Options I've gathered from this forum include (1) copy table, drop original table, rename the copy, (2) export table using xp_cmdshell 'bcp...', truncate table, import data with BULK INSERT from 'c:...' WITH ...; and (3) run ALTER INDEX ALL ON dbo.Table REORGANIZE WITH (LOB_COMPACTION = ON)
My biggest concerns are the resources (disk space, CPU, memory...) SQL Server will use for any option, especially given the size of the table. As for time, I can block off weekend time if needed but what about log files, tempdb files, etc. What settings should I set/change before embarking down any one of these options?
The server has 160GB of ram with 128GB for SQL Server (2016), 2 nodes w/ 20 processors each rated at 2.2 GHz on MS Windows Server 2012 R2 Standard.