Solved

SQL Server 2008 - Shrink vs Other Optimization After Removing Images

Posted on 2014-02-26
4
627 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
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error when creating a table from a function 6 20
SQLCMD Syntax 2 15
Getting invalid Syntax SQL. 3 21
Casting is giving error in sql server 3 12
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

830 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