[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-12-12
6
Medium Priority
?
111 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 664 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 668 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 668 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

649 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