Solved

SQL Server 2008 - Shrink vs Other Optimization After Removing Images

Posted on 2014-02-26
4
642 Views
Last Modified: 2014-02-26
Hello,

I have a SQL Server 2008 database that had images stored as binary data.  The server started max'ing out it's 8GB of memory and I think it was largely due to the fact that it was storing images within the database (1MB size ones).

We've removed the images from the database and now store them in the filesystem and reference them by an ID in the database.  I think this has helped a lot… it has also reduced the size of the database significantly as we've deleted the tables that stored the images.

My question - I was wondering if this is when using the "Shrink" Task (ie. Right Click DB in SQL Server Management Studio > Tasks > Shrink) would help or are there other issues that using Shrink can cause?  Is there something else I can do to re-optimize the DB after this major change in structure?  Also, what's the difference between Shrink DB and Shrink Files as far as performance.

Thanks!
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
  • 2
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
ID: 39890646
You really should never shrink your data files - it can/will cause internal database fragmentation.

If you get a bloated log file it could be shrunk as a one time thing kinda deal but shrinking of data files is generally considered a bad thing.

There are several noted bloggers who've made posts about this (I don't want to rehash what they have already covered):

Paul Randall: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Mike Walsh: http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/
Brent Ozar: http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

Are you short on disk space?  Do you really need to recover it?

Paul Randall talks about one option of creating a new file group and moving your data objects there.
Mike Walsh talks about just leaving it there unless your desperate for the space.

In the event  you do decide to proceed with the shrink, you'll fragment your indexes all over the place so make sure you rebuild all of them when you're done.

However, the general consensus appear to be unless you're desperate for the disk back, just leave it be.
0
 

Author Comment

by:adrian78
ID: 39890660
Oh ok - thanks for the info!  So this all really is related to disk space and shouldn't affect RAM, correct?  We are fine on disk space so all is probably good.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39890687
As far as I'm aware it shouldn't affect memory.

The entire database isn't read into memory.  Those pages that are needed to satisfy queries are read into memory and stored in the buffer cache until aged out by something else coming in - hopefully to be used by other queries - because pages in memory are accessed faster than pages on disk.

You have large data files with empty space - but the empty space isn't going to be read into memory - because there's nothing there in that space.

I did a little searching and found a couple of posts that talk about SQL Server Memory Usage which may shed some extra light on this for you:

http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/
http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

The first talks about how SQL Server uses memory and the second shows you some queries to see what's being used.

Hope that helps.
0
 

Author Comment

by:adrian78
ID: 39890761
Thanks for your help!  Appreciate it!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
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.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

751 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