Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008 - Shrink vs Other Optimization After Removing Images

Posted on 2014-02-26
4
Medium Priority
?
666 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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 2000 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 23

Accepted Solution

by:
Steve Wales earned 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

721 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