Solved

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

Posted on 2014-12-12
6
104 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 47

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

808 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