Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

SQL Server 2008 - Shrink vs Other Optimization After Removing Images

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
adrian78
Asked:
adrian78
  • 2
  • 2
2 Solutions
 
Steve WalesSenior Database AdministratorCommented:
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
 
adrian78Author Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
adrian78Author Commented:
Thanks for your help!  Appreciate it!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now