3

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.

5
  • Just curious, how often do you plan to archive the data?
    – J.D.
    Jul 26 at 19:58
  • Archiving would happen annually - part of the year end processing in January.
    – JordanT
    Jul 28 at 13:12
  • Gotcha. I've always been a big pusher against using SHRINK but that's mainly because it's generally a wasted operation when done routinely that people commonly try to use to squeeze the last drop out of their disks on. In your case, I don't think you'd be running it frequently enough, and you have a valid use case for archiving lots of data over a long enough period of time, that if it's easier it might not be the end of the world to use SHRINK - I'd actually be ok with it here. But Charlieface gave you a great solution as well.
    – J.D.
    Jul 28 at 13:49
  • 1
    Ok. What I c/should have added is that over the past 1-2 years we've moved away from storing most documents in the database and over to Sharepoint (they're out of my DB ...). There will still be documents added but they will be fewer and smaller so the bloat will be much less after another 2-3 years
    – JordanT
    Jul 29 at 16:21
  • That's actually pretty relevant information. In that case (when your use cases in the database change such that your projected data growth is significantly less than before), I believe it's even more fair game if you wanted to use the SHRINK command, if it's the easiest solution. One-time, sparse, highly infrequent uses of it is not necessarily a bad thing. It makes less sense to use when it becomes a routine or reactive thing instead, like most people usually want to use it for.
    – J.D.
    Jul 29 at 17:03

1 Answer 1

5

The downsides of your options seem to be:

  1. Copy and Renaming tables can mess up stored procedures and views, requiring recompilation. It also requires double the disk space.
  2. Exporting using bcp and reimporting it is messy, and you still need all that space, albeit not in SQL Server itself. If the tables are small there is no benefit over option 1 anyway.
  3. REORGANIZE WITH (LOB_COMPACTION = ON) can be pretty slow, and may also require extra space. It will also bloat the transaction log due to heavy page and row movement.

A better option than any of the ones you have presented is a table switch.

Similar to the drop/rename idea, you create a new temporary table that is exactly the same as the original. Insert the data to keep into the new table. Then truncate the old one and switch the new one in. Switching is a fast metadata operation, that requires just swapping the table headers.

So let's say you have an existing table

CREATE TABLE MainTable (
    id int PRIMARY KEY IDENTITY,
    document text
);

You need to copy the schema exactly, this includes primary/unique/foreign/check constraints and indexes. IDENTITY does not need to be there, and is probably easier if you leave it out. You can create a copy of this schema quickly by right-clicking the table in SSMS and selecting Script Table As -> CREATE.

CREATE TABLE CopyTable (
    id int PRIMARY KEY,
    document text
);

Then you can copy all the data in

BEGIN TRAN;

INSERT CopyTable WITH (TABLOCKX)
  (id, document)
SELECT id, document
FROM MainTable WITH (TABLOCK);

And truncate the old table and switch

TRUNCATE TABLE MainTable;

ALTER TABLE CopyTable
  SWITCH TO MainTable;

-- if you have an IDENTITY column then:
DBCC CHECKIDENT ('MainTable');

COMMIT;

Finally drop the copy table, which is now empty:

DROP TABLE CopyTable;

You can move around or remove the BEGIN TRAN and COMMIT depending on what else is running concurrently.


I recommend while you are at it, to change the column data type from text (which is deprecated) to varchar(max). As far as I am aware they implicitly convert to each other, and therefore this should not affect the application at all. varchar(max) has the benefit of storing <8kb values within the row, rather than on LOB pages.

I suggest you do this before copying the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.