• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

Shrink / Compact SQL Server 2008 DB after removing photos that were stored

I have a SQL Server database that had a binary field that stored photos used in profiles and articles.  We've moved this to the filesystem rather than storing them in the DB and now the database queries are faster.  The size of the database didn't change (it's roughly 30GB).  I think almost all of that space isn't needed anymore and the DB may be 10GB without the photos.  Should I compact / shrink the db?  I heard this isn't good to do but in this case, maybe it is.  I won't be storing the photos in the DB going forward either so not sure if it will ever reach that size again.
0
adrian78
Asked:
adrian78
3 Solutions
 
SimonCommented:
It's OK to shrink the database in this scenario (at a quiet or out of hours time and not while it is running a backup) but would suggest that you rebuild indexes afterwards.
0
 
adrian78Author Commented:
Thanks for the reply!  Any guesstimate how long a database of this size would take?
0
 
SimonCommented:
It can vary a lot depending on your hardware, the degree of fragmentation, the number of other dbs on the instance, contention for IO on the storage.


I think I recall a similar shrink (30GB to 10GB) taking about 20-30 minutes last time I did it, but it really does depend on a lot of external factors.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Delete that column from you table and then schedule a rebuild index to run during the night or a period that no one is using the database. After rebuild finished run the following command that will only truncate the datafile:
USE DatabaseName
GO
DBCC SHRINKFILE (N'DataFileLogicalName' , 0, TRUNCATEONLY)

Open in new window

The rebuild will compact the data (actually reorganize the data pages) and truncate will shrink only from the end of the file (crop the unused space).
0
 
Scott PletcherSenior DBACommented:
First be sure to reorganize the relevant index explicitly specifying:
WITH ( LOB_COMPACTION = ON )

You want to make sure SQL has released its own allocation of the space before doing any file shrinks.
0
 
adrian78Author Commented:
Thanks guys - I will give it a go and get back to you all.  Appreciate the replies.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now