9

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.

Improve this question
9
  • 3
    Do you (or the application) care if the tables physically end up in a filegroup other than PRIMARY?
    – Jon Seigel
    Commented Feb 5, 2013 at 17:47
  • @JonSeigel I suppose not, and actually that's a pretty good idea, as it would save me the trouble of having to create a template database, and move all the data.
    – db2
    Commented Feb 5, 2013 at 17:53
  • Are you considering only solutions you code yourself or you can also review some application to help you with that? You could use RedGate's SQL Storage Compress to compress live data. Or you could try Virtual Restore to make compressed backups available as online dbs (without actually having the complete space needed). They are all based on the older Hyperbac windows file driver which are very good at compressing live data and backups.
    – Marian
    Commented Feb 5, 2013 at 21:27
  • @Marian Sounds interesting, but I'd like to stick to native SQL Server capabilities for now. I just need to very effectively defragment the databases, without a whole lot of unused space left in the file(s). If it's a third-party tool that performs the work instead of scripting manually, that's fine.
    – db2
    Commented Feb 5, 2013 at 21:51
  • It's just a thought, but why not create a new filegroup, add a file, set a reasonable growth (say 500MB) and then rebuild your tables onto that new filegroup. Then shrink the primary file down to almost nothing. You won't care a lick about fragmentation on the system tables.
    – Nic
    Commented Feb 5, 2013 at 23:58

2 Answers 2

Reset to default
1

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?

Improve this answer
5
  • It mostly uses text and image, unfortunately. It's a 3rd party application, so I don't have the ability to change that.
    – db2
    Commented Mar 7, 2013 at 21:42
  • @its transparent to the application really, with SQL server storing the information in row if < 8k. If the vendor says it is unsupported, i'd ask them why they are still using datatypes originally deprecated in SQL Server 2005 !
    – DamagedGoods
    Commented Mar 7, 2013 at 22:12
  • I can't be totally certain that the application doesn't do text/image-specific stuff like WRITETEXT that would fail after changing the data type. But back to the main point, it looks like recreating the clustered index won't actually move the LOB data with it.
    – db2
    Commented Mar 8, 2013 at 13:18
  • you can do this but you have to go into designer in the GUI, then expand the properties, then you have a 'regular data space' but also a TEXTIMAGE filegroup, changing this will, but be careful this will recreate the table ! you can obviously script this and run in a maintenance window if possible
    – DamagedGoods
    Commented Apr 4, 2013 at 16:26
  • Got it, that might be a useful way to generate the appropriate rebuild scripts, at the very least.
    – db2
    Commented Apr 8, 2013 at 15:48
0

I faced a similar issue with a third party tool that was also using an image datatype to store unstructured data, and I solved it by converting the column to use filestream. You will need to do some testing to make sure the app still functions as you expect, but this will give you the ability to write your own archiving process that moves your data to an archive db in an efficient manner.

Improve this answer
3
  • I suspect filestream wouldn't scale well in this case. We've got over 14-million rows across 17 databases, and we're gaining messages at around 15,000 per day. A substantial portion of the message bodies are below 4 KB, so NTFS cluster waste would probably be brutal (and that's even if we add a new disk volume with a block size smaller than 64KB).
    – db2
    Commented Feb 19, 2013 at 14:48
  • In that case, can you convert the datatype to something like nvarchar(max) and use the TEXTIMAGE_ON clause to specify a different filegroup for these large objects? That will allow you store the data out of row and allow to create your own process to manage archiving.
    – Liam Confrey
    Commented Feb 20, 2013 at 5:32
  • the use of filestream really depends on how big each of the LOBS are. I think > 1MB per record to be considered. So i'd agree in this case its not an option
    – DamagedGoods
    Commented Mar 7, 2013 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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