Don’t Touch that Shrink Database Button!

Update [1-31-09] I wrote a follow-up to this regarding some of the questions that I see Google Search bringing folks to this blog. Check out my my additional post for some of the more common questions in the post, “Shrinking Databases is a Popular Topic“.

Updated [11/16/13] This post continues to be a popular destination for searches like “How do I shrink a database?” and “Shrink SQL Server Database” – I wanted to edit the flow a bit, add answers to those questions and reiterate a default position – It isn’t typically a good idea to shrink your database. There are exceptions, but they are exceptionsIf you’ve come here looking to just shrink stuff and think that will solve all potential problems in your SQL Server database, please read the post and reconsider.

 

This post is part 1 in a series on shrinking databases in SQL Server and transaction log management. You can see them all in the Shrinking & Transactions category where I have a few links about SQL.

This topic has indeed been done to death. Yet I still often encounter unnecessary database autoshrinks, scheduled shrink jobs and at times a seeming lack of knowledge on just what one should be doing as far as their database sizes. In fact, as a SQL Server consultant with Linchpin People, this is still one of the common findings when we do a Database Wellness exam – even in 2013 – either shrinks happening, or poor transaction log management. In fact if you find yourself wondering what is best here, you might consider a WellDBA™ Exam with us. You can request info on those from us.

 

What Happens when you Shrink a Database?

When you click that shrink database button (or leave a DB in autoshrink, or schedule a job to perform shrinks), you are asking SQL Server to remove the unused space from your database’s files.The process SQL uses is ugly and results in Index fragmentation that affects performance in the long run. You’ve deallocated that space and are letting the O/S do what it needs to with it. If you have a growing database (as the majority of production databases tend to be), this means that that database will grow again. Depending on your autogrowth settings (another pet peeve for another post) this growth will probably be more than necessary and you will end up shrinking again… At best this is just extra work (shrink grow/shrink grow) and the resulting file fragmentation is handled alright by your I/O subsystem. At worse this is causing that index fragmentation I mentioned, file fragmentation, interrupting what would have otherwise been contiguous files and potentially causing I/O related performance problems. Really though, you are wasting time and introducing index fragmentation.

What should we do then, leave free space in a SQL Server data file?!

Yes! In fact I find it best to plan for where your data size needs will be, not where they are at the time of initial go live. Set your initial database size to what you expect it to be in the future. If you can, look a year or more into the future. That’s your initial size. Create the database that size and set the autogrowth to a reasonable number in bytes rather than percent. (I hate the default of 10%.. if you have a 1TB DB that means you will be growing 100GB anytime you need to grow a file.. Better in 2005 with Instant File Initialization but still not “responsible”). Monitor your free space and look at size trending over time so you can plan for a large allocation of more space should your planning have been off. That free space in the file is “just sitting there doing nothing” as your SAN team may say, but would you rather have what you expect to need and grow into it or scramble to allocate space at the last minute? I vote for the former and if the budget allows (if you can justify it with the growth expectations, the budget better allow it because it will need to in a year anyway!) it makes sense.

Yeah, but my transaction log is always running out of space!

Yet another pet peeve I talk about this more in other posts in this category and in a Question and Answer I did on log growth for DBA.StackExchange. For here though suffice it to say:

If you are in Full Recovery Mode on a database that means you intend to be able to recover to a point in time in the event of a failure. This means you plan on using a combination of Full Backups and Transaction Log Backups (and possibly differentials). SQL Server understands your intent, and it will not truncate (free up space within the file.. notice the file stays the same, I didn’t say shrink I said truncate.. Truncate frees space within a file, shrink removes that “free” space to make the physical file smaller) the log file(s) of your database (the .LDF files).

Instead, they will continue to grow until you take a transaction log backup. 90% of the time when I am helping someone with an out of control Transaction Log growth problem, it is because they are incurring the “cost” of Full Recovery mode (growing log file, the full logging of qualified events, etc.) but none of the benefit (being able to restore to a point in time from your transaction log backups if your log chain is unbroken and your log files survive)…

Simple solution here.. Look at your backup/recovery plan!! Why aren’t you taking Log backups? Is it because you don’t understand them? There are plenty of resources to help. It is relatively simple to begin working on a proper backup/recovery strategy and the face you save may be your own :) Is it because you don’t need point in time recovery? Well then consider Simple Recovery mode which will truncate the log at certain events. Don’t go right to simple recovery mode though, analyze your backup and recovery needs.

Yeah, that means you shouldn’t be doing backup log with truncate_only all the time… :)

If this is all new to you.. Check out a couple of resources about these topics:

  • Paul Randal has plenty to say about Shrinking Files (and says it much better than I). Check out this blog entry and look at the rest of his blog.
  • Recovery Models overview – Based on SQL Server 2005 but concepts apply to it and future versions of SQL Server up to 2012, plus you can see the version you need.

As always, feel free to e-mail me with any questions specific to your environment. I am happy to exchange quick e-mails with tips/tricks especially when it comes to my “pet peeve” topics.

Related Posts

Share

Tags: , , ,

25 Comments

Leave a comment
  1. Barry Cohen July 20, 2009 at 16:35 #

    I know this comment comes late in the game, but I have a question that you may be able to answer:

    I was under the impression that one of the reasons to shrink a database was to defragment the database files, something that can have a positive effect on transaction heavy databases (or so Oracle would like me to believe).You seem to be saying this is true:

    Deallocate that space and let the O/S do what it needs with it. If you have a growing database (as the majority of non-static databases tend to be), this means that that database will grow again. Depending on your autogrowth settings (another pet peeve for another post) this growth will probably be more than necessary and you will end up shrinking again… At best this is just extra work (shrink grow/shrink grow) and the resulting file fragmentation is handled alright by your I/O subsystem. At worse this is causing file fragmentation, interrupting what would have otherwise been contigous files and potentially causing I/O related performacne problems."

    I’m not clear what the difference is. Do you mean that the OS (or a commercial disk defragger for that matter) will handle defragmentation adequately without worrying about the internal shrink function? Another way of saying this: If file level fragmentation is an issue for an organization, simply running Windows deframentation tools will address this issue regardless of what’s done in SQL Server and you don’t need to run a separate routine for this purpose, unlike, say Oracle database?

    Thanks

  2. Mike Walsh July 22, 2009 at 01:24 #

    Hey Barry

    I apologize for any confusion I may have caused with my stream of thought style and wording. I can’t speak towards Oracle as I am not as familiar with the physical file organization or the indexing structures in Oracle databases.

    For SQL Server the major point I was trying to make, however, was that physical file fragmentation is bad. The Best case is that this fragmentation is handled somewhat alright by the O/S and its defragmentation processes and doesn’t end up a big deal. That was slightly tongue-in-cheek. The more likely case is closer to the worst case: the physical fragmentation will cause pain, the cost of the growths will cause pain and it is sort of all for not when you could have right sized the database from the beginning for a value that you can grow into over time.

    Your first thought about reorganizing the database may be induced by some of the wording in SQL Server 2000 maintenance plans which sort of lump an index reorganization and shrink into what seemed like one operation (IIRC).

    There are really two main kinds of fragmentation to think about here: Physical File Fragmentation (you want to prevent this by trying to minimize growths and don’t shrink your production database) and index fragmentation (which comes in two forms.. One form being your index pages no longer physically in the logical order of the doubly linked list and the other form being 8KB data pages, due to page splits from inserts/updates and due to delete activities, no longer being as full as they should be). The best way to handle this type of fragmentation is through rebuilding or reorganizing indexes, not shrinking a database.

    To be honest, I can’t find any positive benefit from shrinking a production database. All that is designed to do is reduce the free space within your database files. Yes there is an option during a shrink that can reorganize data as best as possible to determine where that free space comes from but this doesn’t mean deal with index fragmentation.

  3. ASP.NET programmer September 29, 2009 at 11:54 #

    What would webmasters do if database size is limited in hosting services? For example: 120MB before shrinking and 18MB after.

  4. Mike Walsh September 29, 2009 at 15:35 #

    I think Brent’s answer on his blog for this same question is the right one: Properly size your database in the first place with the amount you need, room for growth and monitor it closely over time. Shouldn’t have a lot of growth and shrink if you set it up right from the start.

  5. Chris March 29, 2010 at 22:17 #

    Thanks for the article Mike.

    I have a client who’s asked a question about shrinking a database. I’d be interested to get yours Mike or other people’s thoughts. Here’s the background;

    The client has moved a large table to a new database and the result is that the original database file is now 75% free space (presumably they were intending to free the space up and possibly going for performance perks).

    They asked my opinion of shrinking. I started writing basically that while auto or scheduled-manual shrinking is typically a bad idea, a one off will be ok, but before sending it Googled “don’t shrink sql server database” to find arguments against doing it – I came across this article.

    Lets assume the growth is predictable and auto-growth settings are sensible. Do you think there would be any problem with performing a one-off shrink in this case? How about a shrink, manually setting the shrunk file size to be a little greater than 1 auto-growth unit so that an auto-grow will not immediately occur?

    Index defrag is regularly performed and I’m also recommending performing a full backup first and doing this in a change window in case a restore is required.

    Chris
    DBA
    Perth, Australia

    • Mike Walsh March 29, 2010 at 22:27 #

      Hey Chris -

      Thanks for the comment. I’ll give a quick response now before hitting bed. Feel free to shoot me an e-mail mike at the domain of this blog works, your e-mail address looks like a fake one to prevent spam.

      I would say in this case a shrink sounds like an alright idea. I would say set the final size to the size you expect the file to be in a couple/few years if possible. This will prevent the file from having to autogrow in small chunks. While at it, I would also suggest to change the auto growth from the default 1MB, something more in line with the size of the DB. Paul and Kimberly blog about this over at their site and that link from Paul at the end of the above article is a good place to go look for more.

      The goal is to avoid physical file fragmentation from lots of growths. Growths were more expensive before Instant File Initialization starting in 2005 but the effect on physical files (not index fragmentation but on disk file fragmentation) still exists.

      So I would say a shrink of the file to an appropriate size is not a horrible solution. I would caution you to not shrink the database but just the data files and consider the impact of VLF fragmentation

  6. Natasha July 29, 2010 at 13:02 #

    I would like your opinion please.

    My database was about 300 MB a few days back. Due to an error it grew to 17GB overnight. I have fixed the problem to an extent by deleting the swollen records and database backups are in the region of 300MB again. However the mdf file still remains 17GB. Is it a good idea to shrink the database?

    My concern is that this database is very small and so is the server where it stands; in relative terms 17GB (x2, production and test) is a lot of space to block and could be used more effectively otherwise.

    Thanks in advance
    Natasha

    • Mike Walsh July 29, 2010 at 15:12 #

      Hi Natasha -

      That is some error to make the DB grow to 17GB from 300! :-) This was your .mdf not your .ldf (log file) right?

      While it is not good to be in a repeated cycle of shrink-grow-shrink-grow or even do frequent shrink databases on a production system, a one off isn’t bad in a situation like this. What I suggest you do is shrink the database FILE that needs to be shrunk, leave a healthy (up to a few years growth would be ideal, even if a guess) amount of free space in the file and then rebuild your indexes after the fact.

      • Sean December 29, 2011 at 16:00 #

        Are you saying that if you’re forced to perform a shrink (i.e. running out of HD space), that you should rebuild and degragment your indexes?

        • Mike Walsh December 29, 2011 at 16:30 #

          Hey Sean -

          Yeah that is what I am saying. You should perform your index maintenance operations when you have the allocated time on the system to do it after a shrink as these operations can cause fragmentation. I also would not shrink it down to no free space in the data files since they will likely grow again.

  7. Nick Harris June 16, 2011 at 16:43 #

    Sorry for being late to the party, I’ve got a question. I think having about 20% extra space in the datafile is reasonable (74 gb datafile). Recently we’ve had a table that uses the text data type which is used in the processing of a lot of data. Every once in a while when we restart the SQL service or reboot the server, SQL server bugs out and does not release the space used by the text datatype field. Next thing ya know the mdf grows up to 100 gbs. Once we fix the issue I’m left with 45 gbs of free space for the datafile (45%). So I’m conflicted. Should I just leave it for the next time this happens to avoid autogrowth or is it reasonable for me to shrink it back down to 20% free space?

    Thank you.

    • Mike Walsh June 16, 2011 at 20:34 #

      Hey Nick ,

      I’m not 100% sure I understand the question. I am assuming that the Text data type column is a temporary column used in processing and then data is deleted/removed from there? There are a few variables here but I’ll send you an e-mail with a couple questions.

  8. javier meca September 26, 2011 at 13:36 #

    Ok, i´ve understood what you explain, but in SQL 2008 the transaction log backup doesn´t reduce the size of the LDF file. So is it necessary to do it manually? my ldf is 74 GB and my mdf 25 GB. The LDF grows up about 1 Gb per day.

    This is my script:

    USE mydatabase;
    GO

    ALTER DATABASE mydatabase
    SET RECOVERY SIMPLE;
    GO

    DBCC SHRINKFILE (mydatabase_Log, 1);
    GO

    ALTER DATABASE mydatabase
    SET RECOVERY FULL;
    GO

    But it doesn´t work. I obtain the message: ” Cannot shrink log file %d (%s) because of minimum log space required.”

    The database is in full mode. I have programmed a complete backup every day at 22:00 and a backup of transaction log every hour from 08:00 to 21:00
    Any idea???

  9. sharon February 23, 2012 at 20:49 #

    Hi what will happen if i only truncate the table and didnt shrink the database?

    • Mike Walsh February 24, 2012 at 11:20 #

      You’ll end up with the free space that that table was holding left in your database. This normally isn’t a problem as other growth in the DB will just end up using that free space. Also the truncate command (if you literally mean using the TRUNCATE TABLE command) will be a little kinder to your transaction log and won’t cause it to grow like a DELETE command would.

Trackbacks/Pingbacks

  1. How to REALLY Compress Your SQL Server Backups | Brent Ozar - Too Much Information - February 15, 2010

    [...] – nowhere in today’s discussion am I going to shrink the databases. Shrinking databases is evil. Instead, we’re going to do some things to lose the fat and keep the [...]

  2. 5 Things SQL Server Should DROP | SQL Server Blog - StraightPath Solutions - May 11, 2010

    [...] SQL Blogging with a series of rants about shrinking databases. I still get hits to articles like “Don’t Touch That Shrink Button!” from web searches or forums. The simple fact of the matter is.. Well to put it simply, “raise [...]

  3. Hey Software Vendors! Get a Clue about SQL Server | SQL Server Blog - StraightPath Solutions - July 2, 2010

    [...] documentation. Talk about recovery models so you don’t end up with huge transaction logs and bad advice being given to your customer’s IT support team from Google and [...]

  4. Brent Ozar - July 9, 2010

    @ranjeeth I send them this link by @mike_walsh: http://bit.ly/ar5Upz

  5. Daniel Costa – TechBlog » How to shrink SQL Server Database Log File - September 13, 2010

    [...] Advice from @afernandez from twitter: Be carefull about this, only use it when you are realy out of space, check: http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/ [...]

  6. Argenis Fernandez - September 13, 2010

    @danieljcosta that is terrible advice to give — please see this http://bit.ly/bSp8xg #SQLServer

  7. Aaron Bertrand - November 29, 2010

    @end_olin @mike_walsh's is particularly good: http://bit.ly/e1T6Va #sqlserver

  8. Aaron Bertrand - May 3, 2011

    @SQLThugette http://is.gd/3pZv9O & @mike_walsh good series: http://is.gd/Fqv41z http://is.gd/wXpcHs http://is.gd/2eu6nx http://is.gd/9sdDnU

  9. Database consumes available free space? - dBforums - July 13, 2012

    [...] can cause fragmentation on your hard drives so yes it is probably not recommended to do often: Don't Touch That Shrink Button in SQL Server! | Straight Path Solutions, a SQL Server Consultancy The solution would be to reset the percentage of growth on your database (and get a larger hard [...]

  10. Shrink Database and Files in MSSQL « Naveen's Weblog - March 13, 2013

    [...] http://technet.microsoft.com/en-us/library/ms189493.aspx http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/ [...]

Leave a Reply