Why shrinking database is bad

Then ending up whit a less fragmented database file and at the same time not ha fragmented indexes? No — in that case the shrink is entirely superfluous — you should just rebuild. What about a database that has approx. There are 8 file groups the default install for this database not chosen by myself.

The DB is Gb of which 75Gb is just empty. I am not sure if I should reclaim the space or just leave it at half empty. Just wanted to drop a line for praise, thank you Paul! Here is a beer for you efforts. I had a database for a third party mailing application that grew to an unsightly size due to an issue with the grooming job external to SQL Server.

The data file used to be in the neighborhood of GB and eventually we had to grow it to GB just the data file and much to the chagrin of my SAN Admin before the company provided a fix for the issue. Quick question however, why would Msft put out the info regarding the shrink db if it is not a best-practice?

When I do data compression, the file size grows unless I do a dbcc shrinkfile. I know that it is not recommended to use dbcc shrinkfile. The file size grows because you need to rebuild to enable data compression.

Best way to do that is to rebuild the indexes into a new filegroup, then you can drop the old one. We periodically delete lots of data in our warehouse env have SQLServer style patitioned views with new daily partitioned tables created every day and an archival job to move old data to a different archive database. I feel we are wasting multiple TB of space and bloating up the DBs. What is my best option now to bring the transaction log back to formal state and maintain my small size fro the database.

Thanks for the advise on shrink… Can you recommend a stored proc or similar to move table s to a new file group? Seems there are many challenges invovled in achieving it — When a table is heap, table has LOB data, different schema exists within a database, table has FK, constraints, etc. Hi Paul, Great article!

I was having a discussion with another DBA today….. The data file is shrunk only to the last allocated extent. Or is it still a bad option? Paul, First off love your work its helped me out alot in day to day activities. Any ideas why.. Any ideas why what? And how are you measuring fragmentation? If you have the time and space to run it that is. These commands only require a single 8KB page of extra space, instead of needing to build a whole new index in the case of an index rebuild operation.

I was curious about this so just for fun I ran a test where I shrank the data files in a testing DB that had s of GB of data removed. The file size dropped and fragmentation was heavy as expected. File sizes increased by a moderate amount. Looking at sys. Am I correct in assuming this empty space is the result of the index rebuild operation? The advice you quoted is to avoid the data files growing again when removing any fragmentation caused by the shrink.

I ran into a situation today where I needed some space. I did the normal investigation of what my options were and decided to try the shrinkfile and then just defrag. Is the dbcc shrinkfile fixed in SQL ? It did of course generate a boatload of log activity. No, same algorithm in all versions. Rare to see no fragmentation after a shrink. The fragmentation is very high after the shrink but I see no alternative in my situation.

We have a partitioned index with partitions in a different filegroup for each month. You could do that. No — you can switch out a partition the sliding window scenario. Recommend you read the partitioning whitepapers.

Thanks Paul, I suspected that. It is a great post being referenced for years! Thank you for responding in advance Paul. It is understood that data file shrink is bad for performance due to fragmentation, IO and CPU cost and log growth. Also, eventually if database will use up the data, it seems logical to leave it alone. But every backup file is bigger than it needs to be.

I apologize for any confusion I may have caused with my stream of thought style and wording. For SQL Server the major point I was trying to make, however, was that physical file fragmentation is bad. 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 maintenance plans which sort of lump an index reorganization and shrink into what seemed like one operation IIRC. The best way to handle this type of fragmentation is through rebuilding or reorganizing indexes, not shrinking a database. All that is designed to do is reduce the free space within your database files.

What would webmasters do if database size is limited in hosting services? For example: MB before shrinking and 18MB after. They asked my opinion of shrinking. 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? Thanks for the comment. 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. 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 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. The operation given in the example is incomplete. What you do if youre and admin is, you shrink the db, you rebuld the idx, then you shrink again.

Without this last step youre doing bad because you dont understand the general behavior of sql server. I would add to that you should Reorganize after the second shrink as well. So is there a way to shrink a database and remove the fragmentation without increasing the size? Surely there must be? I have a question, I have shrunk the database and rebuilt as well as reorganized all the indexes as needed but still my physical reads are continuously high. He holds a Masters of Science degree and numerous database certifications.

Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long term multi-channel campaigns to drive leads for their sales pipeline. Is your SQL Server running slow and you want to speed it up without sharing server credentials? I pay for storage. I am happy to pay for my core business, but paying for silly data seems My backups are now huge also.

I do nightly full backups, and then ship them off to an FTP server. This process is taking longer and longer. Given my issues, is shrinking so bad? I will rebuild all my indexes. I did a dry run, and brought my database down to 6GB. This guy who I have always trusted as my SQL guru , says it's bad Looks like you are already aware of the risks associated with shrinking so I wont go over those again.

As a once off due to a bug or some form of large growth that will only happen once then doing a one time shrink is fine. If Full make sure you are taking frequent transaction log backups to keep the log files down in size. I did a dry run, and brought my database down to 6gb.

Most of the advice given on the Internet is copied and is disseminated, while not reading the whole topic carefully.

There is no doubt that shrinking of data files is bad, but if you ask any SQL guru he would always say, "yes I have shrunk the data file".