Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.
COURSE of the MONTH
9 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Shrink / Compact SQL Server 2008 DB after removing photos that were stored
Posted on 2014-12-12
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.
[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
2-
2
- +1
6 Comments
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.
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.
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.
Active today
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)
Featured Post
Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses
Course of the Month9 days, 12 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.