5

I've been reading all the articles (and comments) linked from Stop Shrinking Your Database Files. Seriously. Now. I understand that shrinking is bad.

What I have a hard time figuring out is how to do it right when its actually required.

The backstory is that I have inherited this archive DB with a handful of tables containing PDF's that should have been expired after 5 years, but the procedures to do this on a schedule was never set up. I don't have the row counts at the moment but it's a few million, and about 3TB. I guesstimate about half of this will be deleted and never come back.

I've been talking to our infrastructure providers DBA, but I find the advice they give (delete, reindex then shrink) to be highly dubious. I'm a system consultant not a DBA, but it seems I get to play one this time :-/

Paul Randal recommends two approaches

1

  • Create a new filegroup
  • Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
  • Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

Unfortunately I don't have access to the actual server just SSMS, and this is quite a bit outside my comfort zone anyway. I'm not actually a DBA..

2

DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.

Yes but how.. there are quite a few parameters to choose from, and this seems optimized to get the size absolutely minimal, and not what I need which is to get rid of the bloat while keeping the DB as healthy as possible.

In other words I don't care if the DB grows some, it needs space to operate and it will grow some as document production increases year by year, but the nightly, weekly or monthly purging will now keep this in check.

I should also add that I can take this DB offline at night without much trouble.

3

The recommendation from your Providers DBA is sufficient for your needs. I would add the additional step of checking to make sure you don't have any heaps, however. When you delete records from Heaps the space is not always made available and you won't have a good way of reorganizing it. If you can, add a clustered index to the heap before deleting (or after, dealers choice). If you cannot, I would delete, add a clustered index and then remove it.

SELECT T.name AS TableName
    , I.* FROM sys.indexes AS I
    INNER JOIN sys.tables AS T ON T.object_id = I.object_id
WHERE I.type_desc = 'HEAP'

So, my steps would be:

  • Check for Heaps and decide on course of action.
  • purge data (preferably in chunks), check-pointing or backing up the log as applicable.
  • defragment indexes (Ola Hallengren's scripts will help here if you don't have your own). Do this first so that pages can be readjusted to the correct fill factor. Otherwise, space used estimate will be off since a partially used page is counted as a full page and you will probably have lots of partially full pages that can be consolidated without impact.
  • Look at current database file size used. Shrink to appropriate size (I like to leave at least 20% empty space in the file.
  • Defragment your indexes again since the shrink moved everything around.
  • Take the time to evaluate file growth settings to make sure they make sense for your environment. Usually growing in fixed size rather than as a percentage and make sure you have instant file initialization turned on.

This will tell you how much space is actually being used in the file.

SELECT DB_NAME() AS DatabaseName
, F.name
, F.type_desc 
, F.state_desc 
, F.file_id 
, F.growth 
, F.max_size
, F.is_percent_growth
, F.physical_name
, F.size/128.0 AS CurrentSize_MB
, FILEPROPERTY(F.name, 'SpaceUsed')/128.0 AS SpaceUsed_MB
 FROM sys.database_files F
  • I would add additional step of rebuilding all indexes after shrink. Shrink will fragment all indexes. – Bob Klimes May 9 '17 at 16:59
  • Step 3 where I mention defragmenting the indexes and reference Ola Hallengren's scripts. – Jonathan Fite May 10 '17 at 2:52
  • I know, but then step 4 you say to shrink the database, which will then fragment all your indexes. i just think steps 3 and 4 should be flipped. – Bob Klimes May 10 '17 at 13:49
  • That was my impression too, that you should rebuild after shrink, but if I read BradC's post correctly there might be reason to rebuild both before and after depending on the scenario. – Alias_Knagg May 10 '17 at 13:57
  • Good note about the heaps btw. In my case we have some, but none of them will have data deleted. That should mean I can ignore those, right? – Alias_Knagg May 10 '17 at 14:16
3

I've been talking to our infrastructure providers DBA, but I find the advice they give (delete, reindex then shrink) to be highly dubious.

Here is why they're likely recommending that sequence: a shrink operation can only reorganize and release completely empty 8k pages back to the OS. Before you can do that, you have to make sure that the tables and indexes have released their internal free space back to the database. That's what the reindex (or defrag) does.

Think of a storage unit, where each "unit" is an 8k database page:

enter image description here

A shrink operation will only move entire units around, but a reindex operation can combine contents from partially empty units (from the same table or index, of course).

Now this depends a lot on the kind of deletes you are doing. If you are dropping or truncating entire tables or deleting vast swaths of contiguous rows, it is pretty likely that those pages are being completely emptied, and you can do the shrink right after the delete. (Followed, ideally, by a reindex to fix the fragmentation the shrink helped create.)

In other situations, that won't be the case. Let's say you are deleting every other row of a billion-row table. Chances are that each 8k data page still contains some rows for that table, and you won't see any difference in the amount of reported free space in the data file until you reindex.

Another relevant example: I recently dropped a very large column in an existing table, and since each data page still had all the other columns, I didn't see any difference in free space until I did a reindex.

In your case, I can't know for sure, but since you are deleting huge binary values, it seems likely that many of those pages will be completely emptied. Just take a close look at your total internal free space after you do your delete.

  • Excellent explanation. But does that mean that there is no point in doing a reindex after a shrink if you did it before? Everyone seems to agree that shrink introduces fragmentation, but perhaps there are several kinds, and several effects of a reindex? ( In my case there are "vast swaths" of invoices and no updates, so probably few half empty pages after a delete. ) – Alias_Knagg May 10 '17 at 14:08
  • Yes, you might need to reindex after a shrink as well, although that is likely to re-claim some of the space you've just recovered. One issue you might face, if I'm understanding your schema: if most of the database size is in a single table, then there are some unique challenges with reindexing (massive log use during rebuild, lots of blank space at the beginning of the data file when the reindex is complete, etc.) This is why Paul recommends rebuilding into a brand new data file, so you are guaranteed it starts at the beginning of the file. – BradC May 10 '17 at 14:50
-2

I think you need to start facing the real problem here:

archive DB with a handful of tables containing PDF's

While compression won't do much for you, is there any way to get rid of storing the PDF's in the DB and replace the blob with a link to an on-disk location?

  • Whilst this approach has its merits, it has some downsides too. First off, database security features are lost. In addition, consider integrity issues (files can be deleted from the disk, but the db still contains paths to those) and recovery issues (you need the db and files). – vonPryz May 10 '17 at 5:47
  • 1
    There is nothing inherently "wrong" with storing BLOBs in a database. In some circumstances it is the right answer. Additionally, if this is a 3rd party product, that may not be an option. – Phil Sumner May 10 '17 at 13:33
  • Been there, done that, yes. Ensuring integrity of those paths can be a pain, and yes I don't believe that is an option in this case anyway. – Alias_Knagg May 10 '17 at 14:20

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.