Solved

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

Posted on 2014-12-12
6
106 Views
Last Modified: 2014-12-22
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
Comment
Question by:adrian78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 18

Assisted Solution

by:Simon
Simon earned 166 total points
ID: 40497299
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
 

Author Comment

by:adrian78
ID: 40497320
Thanks for the reply!  Any guesstimate how long a database of this size would take?
0
 
LVL 18

Expert Comment

by:Simon
ID: 40497323
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 167 total points
ID: 40499912
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 167 total points
ID: 40500937
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
 

Author Comment

by:adrian78
ID: 40505607
Thanks guys - I will give it a go and get back to you all.  Appreciate the replies.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
monitoring configuration for SQL server DB 32 46
Conditions in Where clause 9 46
Can I skip a node in XML? 9 31
Want an individual results display div 8 40
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question