Sql-server – Best way to defrag/compact a database for archival purposes

sql serversql-server-2012

We've got an SQL Server instance that's used for email archiving (courtesy of a 3rd party archiving package). Every so often, the software is rolled over to a new empty database. We've done this quarterly in the past, but we're looking to do it monthly now. The amount of data being archived is about 15 – 20 GB per month, and the bulk of the data resides in only a handful of tables (usually 2 – 4).

Once we roll over to a new database, the old one becomes used on a strictly read-only basis. What I'd like to do is optimize it into a nice, tight data file, with all the tables/indexes contiguous and having a very high fill factor, and not much empty space at the end of the data file. Also, we're using Standard Edition on this server, with all the limitations that implies (otherwise I'd be using data compression already).

A few possibilities I can think of:

  1. REBUILD/REORGANIZE indexes, DBCC SHRINKFILE (Okay, this isn't a sensible option, since DBCC SHRINKFILE will fragment the piss out of anything it touches, but I'm including it for completeness.)
  2. Create a new database with auto-stats off. Script and recreate all tables from the source database. Use bcp to export/import the data into the new database, in cluster-key order. Script and recreate all indexes. Recalculate all statistics with full scan.
  3. Create a new database with auto-stats off. Script and recreate all tables from the source database. Use SSIS or T-SQL to transfer data to the new database. Script and recreate all indexes. Recalculate all statistics with full scan.

The final step in every case would be setting the database to read-only mode.

What other good/better options are there for doing this? My concern is moving the data over in such a way to preserve a high fill factor, and in a logically contiguous fashion.

Edit:

I should mention that about 75% of the data seems to be stored in image (LOB) columns.

Best Answer

To eliminate the physical fragmentation in the files you could as move the clustered index with drop existing to a new filegroup. As they are going to be RO make them all fillfactor 100% as no space needed for inserts, page splits caused by updates.

This would also allow you to perform a piecemeal restore and bring the database online very quickly if you decided to ever go to Enterprise. Enterprise also allows columnstore indexes in addition to massively reduce query time for this Read Only data, which is massive fillet.

You can use the shrinkfile option once before switching to read only without any serious issues with fragmentation to remove the space at the end of the file as you desired.

On a side note, just checking that you are using the latest Datatypes for your LOBS. i.e. nvarchar(max) or varchar(max) instead of ntext or text, varbinary(max) instead of image?

Related Question