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 (http://blog.sqlauthority.com), Image source unknown.
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. http://msdn.microsoft.com/en-us/library/ms190488.aspx
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.
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:
http://www.sqlskills.com/BLOGS/PAUL/post/What-5-things-should-SQL-Server-get-rid-of.aspx
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
http://www.sqlskills.com/blogs/paul/post/Why-you-should-not-shrink-your-data-files.aspx
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,
[...] DBA Before Taking Vacation. There were lots of curious looks at my article on Shrinking Database SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server. Many people online either refused to see it just based on title. Many online tried to even call [...]
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?
[...] Earlier this year, I was working on SQL Server Performance Tuning consultancy; I had faced very interesting situation. No matter how much I attempt to reduce the fragmentation, I always end up with heavy fragmentation on the server. After careful research, I figured out that one of the jobs was continuously Shrinking the Database – which is a very bad practice. I have blogged about my experience over here SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server. [...]
Shrinking a db that has grown to large for it’s disk may be necessary. defragging your indexes after shrinking is an option if you must shrink. Remember that it is very expensive for sql server to expand your data and log files when free space runs out. So if you shrink your data and your logs with dbcc shrinkdatabase command be sure to allocate a percentage of freespace to remain using the target-percent. How to determine the amount? Look at the growth of your log files over the past months and make a determination as to its future growth given what you find there. ou want to leave enough freeespace so it doesn’t expand for a year. You should do daily checks on your db so keep an eye on it’s growth after you have shrunk it. All in all shrinking should be done cautiously and only when absolutely needed. Never automatically or on a schedule!
[...] SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server [...]
Hello Pinal
I m working on a window app and i m also using sql 2000.My client has a 10 year old data and which size is .mdf=26 gb and .Log=35 gb.The problem is that i want to reduce Database size without any affect plz help me
[...] SHRINKDATABASE For Every Database in the SQL Server [...]
[...] Server Interview Questions and Answers ISBN: 1466405643 Page#136 SHRINKDATABASE For Every Database in the SQL Server Shrinking Database is Bad – Increases Fragmentation – Reduces Performance Reclaim Space After [...]
Hi Pinal,
In my production server i didn’t have much space and they are not ready to increase the space. for that i have deleted some data what they don’t want.
after that i didn’t noticed any more space in the drive.
But when i perform the database shriniking i got around 80gb free space. with out shirniking the database how can i get the space.
Hi all,
We have an application that access the sql db 24/7 – the dba’s are;
checking the db integrity
rebuilding indexes
update statistics
shrinking the database
This is being done weekly at a time where the application is still very active on the db – can this cause any corruption issues?
I see lots of people asking the same question that I have. “What do you do, if not shrink your databases, if you are out of hard drive space?”. Yet I really haven’t seen an answer from Pinal regarding this very question.
What do we do if the databases are so very large the only thing left to do is shrink them?
i have questions:
1. how to find out the unused space in both database and log files prior to shrinking?
2. if it is bad then why microsoft provided the auto and manual shrinking options?
Hi Bourne,
run below script will give you free space detail for a database.
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,’SpaceUsed’)/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,’SpaceUsed’))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
No answer as to how to reclaim space. I shrink after reindexing and have not found any issues at all. I don’t believe this post especially since there are no responses!
I received the SCOM alert that the Ultrasound Log file is running out of space. Further checking that the Ultrasound Log file is over 40GB while the Ultrasound Database file is only 15MB. I noticed that the Ultrasound Database is set to “Full” recovery model.
1. Should I perform a shrink file operation on the Ultrasound Log file ?
2. Should I change the recovery model to “Simple” ?
nice post for this post i learn some thing.thanks.
I’m sorry, but I’m new in shrink stuff could anyone explainme how exactly shrink process is? also which store procedures are related?, thanks