SQL Server 2008 - Shrink vs Other Optimization After Removing Images

adrian78
adrian78 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Steve WalesSenior Database Administrator
Commented:
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.

Author

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.
Senior Database Administrator
Commented:
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.

Author

Commented:
Thanks for your help!  Appreciate it!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial