Solved

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

Posted on 2014-12-12
6
103 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
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 46

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:ScottPletcher
ScottPletcher 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now