I was recently called to attend the Query Tuning Project. I had a very interesting experience in this event. I would like to share to you what actually happened.
Note: If you are just going to say that shrinking database is bad, I agree with you and that is the main point of this blog post. Please read the whole blog post first.
The problem definition of the consultation was to improve the performance of the database server. I usually fly to the client’s location a day before, so the next day I am all fresh upon reaching the client’s office after a relaxing night’s sleep. Due to a fair availability of the flight, I reached the location earlier that day, at around 2 PM. I headed to the client’s location to familiarize myself with the place. I met the resident DBA and we talked for a few minutes. We looked at the index fragmentation during our conversation. It was the heaviest fragmentation that I have ever seen in my life. I asked the DBA if he could rebuild or reorganize indexes. He replied that he has never done it in the past one year. One year is really a long time; I could not believe that they have no maintenance task scheduled. I even wondered how they managed without index maintenance for the entire year. Anyway, I quickly handed my index script over him.
We waited until the end of their business hours, at about 5PM. After this, there are hardly any activities in the database. We executed the task on their production server after checking a few settings. The script ran for about 1.5 hours until it is finished. Afterwards, I checked the fragmentation of the indexes; it was very descent. In fact, some of the large tables were showing the fragmentation near to zero. I was pleased with this while the DBA confirmed that few reports even ran faster than before. Satisfied with the work done on Day 0, I left the location and went to the hotel where I stayed.
When I arrived to the location the next day, I had a meeting with the company director. He mentioned that he was updated by Dr. DBA regarding my de-fragmentation of indexes, and he wanted to see the status of the indexes. I ran my script once again to check the fragmentation of index. To my surprise, it was just like yesterday. I got confused and speechless. I checked the server instances and a few other things, but still nothing. The Sr. DBA also had no explanation at all. He started to mumble something I did not understand. Well, to make the long story short, I had a long face and did not feel comfortable. I was pretty sure that I had run the script of de-fragmentation and it worked fine.
Before I continued, I tried to check all the existing stored procedures and jobs. Finally, just like magic, I found the following code.
Before running the following code. Read the whole blog post.
EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'
This code shrinks the whole database on a single SQL Server Instance. I instantly figured out where this code was used, and then I removed it. After I got rid of the code, I rebuilt and reorganized indexes. For the next 5 days, I faced no problem at all. Well, this is another reason not to shrink the database. Shrinking the database causes heavy fragmentation of the tables and reduces the performance. After shrinking, it seems that rebuilding indexes is necessary. But again, there should not be any real need to shrink the database. Do NOT shrink your database.
Reference: Pinal Dave (https://blog.sqlauthority.com), Image source unknown.
45 Comments. Leave new
Nice post, thank you. Sometimes shrinking is necessary. I think you are correct with respect to the above piece of code, but you can prevent the fragmentation from happening during the ‘shrink’ if you use the below:
dbcc shrinkdatabase(N’ns_shrink_demo’,1,truncateonly) with no_infomsgs
The ‘truncateonly’ modifier does not perform any page movement, i.e. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql?view=sql-server-2017
This doesn’t shrink it to the extent that the ‘notruncate’ (default) does, but it still allows for much less space and no fragmentation problems…
FYI, great blog, I read it everyday.
mjfii
As usual, great post. If there’s one thing that really bugs me are DBA’s (usually at the request of the business for more space) that set up shrinking. I worked in an environment where this standard operating procedure. But every week they would come to me asking why the database was performing terrible. It was always a constant battle.
Hi,
As per my knowledge the ‘DBCC SHRINKDATABASE’ is used to decrease the actual size of the database which is not used,does dbcc is helpfull in query tuning?
Koteswar rao
Thanks Pinal.
I have become a follower of your posts.
This is a wonderful and very informative Post.
Generally its said that DB Shrink is Bad. But what other Options do we have to release the unused space in the Database. Its always not feasible to keep on adding extra Disk to your DB when there is already a lot of space available in the database.
We SHRINK, REBUILD INDEXES, and UPDATE Statistics (in that order) weekly. Seems to work fine and no complaints yet …
Hi Pinal,
This is really wonderful post. I know many DBAs used to shrink database regularly which is not good. At the same time question comes, should we allow ever growing transaction log? of course NO.
If you observe your database usage, you can plan out enough size for your transaction log and datafile so it won’t go nasty over your expectation.
Apart from that, keep regular backup policy for full backup, transaction log backup and differential backup. that is all you need.
In short I am against regular database shrinking.
Excellent post. Thanks Pinal.
Regards,
Kanchan
Just a quick aside to those of you who want to get rid of “unused” space in your database files. That “unused” space is usually there for a reason, so don’t assume that getting rid of it is a good idea. You need a certain amount of open space in the database for index maintenance, for example.
Are there times to shrink files? Certainly, but not without a thorough understanding of why, and how.
Thanks.
-D.
Database shrinking was choosen as one of the features that could be done away with in sql server check out this:
https://www.sqlskills.com/blogs/paul/what-5-things-should-sql-server-get-rid-of/
Careful planning of the database layout can help in avoiding frequent database shrinking.
great article. making sure your DB is a large enough size in the beginning also largely eliminates file fragmentation of the data and transaction log files. Auto shrinking will also help file fragmentation every time sql needs to increase the DB file sizes, so auto-shrinking is a no-no.
Also there is a good article from Paul Randall about this
https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
So I just have one question about: the following code (using trunctateonly) causes fragmentation too? anyone realize a test after defrag thedatabase and run this?
dbcc shrinkfile(‘database_namefilelog’, 512, truncateonly)
In my experience, this doesn’t generate fragmentantion.
Pinal, please your help to clarify this.
Thanks.
Hello!
As always, a great, practical post. This clearly demonstrates why shrinking is bad – gaining that xGB of space might be very lucrative, but is clearly not worth the impact.
Only scenarios where we use shrinking is when we have to ship the database to a different physical location on a disk/via the FTP or when we need to migrate to a new physical server. In both these cases, we would anyway run the re-index and usual scripts – therefore the negative impact of the shrinkage is compensated.
good tip
Thanx for one more nice post.
Its very good post. Meanwhile I have one question also that is there any harm in shrinking Log Files also ?
Thanks Pinal and others for this discussion I want to know do we have any alternative to meet both the needs
1)Eliminate unused space in DB (since sometimes this space becomes huge)
2)Avoid Index Fragmentation (Rebuilding the indexes)
What should be efficient step to achieve these goals?
Regards,
Nishit
nice post .i am new reader of your blog
please give the basic detail about trigger
thankyou
Hello, I am using SQL Server 2005. I am Working on big Project. So, I want this, when i creating table(s) ,i also do code documentation with that table. Simply, i want when create table place definition about every field. when any other developer generate the script of that tables. he will be to read the comments on that tables.
Please help me, i heavily need it…..
Thanks
Sushil Shanky
Hi Pinal,
I have created job to shrink only database log files, not data files . Will it be fine?
Thanks,
Hi Pinal,
Then how to release free space from datafiles? and How to reduce size of transactionlog file?
Thanks
Darshan Shah
Hi Pinal
As you say “Do NOT shrink your database.” BUT
How we reduse the size of database?
How we maintain size & performance together?