4

Our Production database is on SQL Server 2008 R2. One of our tables, Document_Details, stores documents that users upload via our application (VB). They are stored in varbinary(max) format. There are over 20k files in pdf format and many of these are large in size (some are 50mb each). So overall this table is 90GB. We then ran an exe that compressed these pdf files down to 10GB.

However here lies the problem - the table is still 90GB in size. The unalloacted space hasn't been released. How do I unallocate this space so that the table is 10GB?

I tried moving the table to a new filegroup and then back to original filegroup but in either case it didn't release any space.

I also tried rebuilding the index on the table but that didn't work either.

What did work (but I heard it isn't recommended) was - change the recovery type from Simple, Shrink the filegroup, set recovery to Full.

Could I move this table to a new filegroup and then shrink that filegroup (i.e. just the Document_Details table)? I know the shrink command affects performance but if it's just 1 table would it still be a problem? Or is there anything else I can try?

Thanks.

Paul S
  • 113
  • 1
  • 1
  • 13
  • 1
    What exe did you run to compress the data? – Prime03 Jun 16 '15 at 12:09
  • Have you tried DBCC SHRINKDATABASE – frlan Jun 16 '15 at 12:11
  • @Prime - one our developers created a command line .net exe that compressed the pdfs. – Paul S Jun 16 '15 at 12:14
  • @frlan - I did and it worked but I thought this affects performance and isn't recommended? – Paul S Jun 16 '15 at 12:15
  • It's not a problem to shrink a database occasionally. But it's not that useful. In your case, why do you want to save 80gb space? Eventually you still need 90gb or more for the table. – Tim3880 Jun 16 '15 at 12:15
  • @Tim3880 - Oh, okay. It's just we have a lot of activity on our database with 50-100 users a day and hundreds of tables so was wary of shrinking it. Maybe I'll test my last option - _move this table to a new filegroup and then shrink that filegroup_. At least if performance _is_ affected, it will only affect that table. Am I right in saying that? – Paul S Jun 16 '15 at 12:23
  • @Tim3880 - As for space, the drive is nearly maxed out at 100Gb. The database is going to constantly grow until the fix is in place (not sure when they will be getting the fix!). Also, between daily backups and getting copies of DBs transferred, it's a lot of unneeded space. – Paul S Jun 16 '15 at 12:25

1 Answers1

0

Moving a table to a filegroup has one problem: By default the TEXTIMAGE data (the blobs) are not moved! A table's rows can reside on one filegroup and the blobs and on another. This is a crazy defect in SQL Server. Maybe by rebuilding the table the blobs were simply not touched.

Use one of the well-known methods to move lob data as well. That would rebuild the lobs and shrink them.

Community
  • 1
  • 1
usr
  • 168,620
  • 35
  • 240
  • 369
  • Thanks, I'll test this. The column that holds the pdfs is a varbinary, does this act in the same or similar way to LOB or BLOB data? – Paul S Jun 16 '15 at 13:16