June 28, 2009 at 11:40 pm
Hi, need your expert opinions.
The mdf file of our production database is originally sized 200 GB, after several tests on DB optimization, the mdf file was still around 200 GB, then after several days, we were surprised because it shrunk to 100 GB, and now it's back to its normal size of 200 GB.
Our optimization plan included SHRINKFILE, SHRINKDATABASE, rebuilding Indexes using DBCC REINDEX. That's it. The commands performed as it was supposed to, like the log file shrunk, and the database backup also shrunk, but the mdf also shrunk to half its size and now its back to its normal size. How come...
What could probably caused it? Pls I need your opinions, I'm afraid that we might lose some data because of it.
Any opinion will be highly appreciated.
Thanks,
Isabel
June 28, 2009 at 11:54 pm
Is your index optimization custom? Does it run for whole DB?
Do not shrink your database/ log files. It will lead to fragmentation
June 28, 2009 at 11:58 pm
You will not loose any data.
Its just that your data file is of 200GB and there is no free space available.So no matter how much you shrink the files it will grow back to its original size.
Use this for find the available space in database
Use database_name
sp_spaceused
Tanx 😀
June 29, 2009 at 12:19 am
joeroshan (6/28/2009)
Is your index optimization custom? Does it run for whole DB?Do not shrink your database/ log files. It will lead to fragmentation
No, the index optimization does not run for the whole DB, it was only executed on selected tables.
June 29, 2009 at 12:53 am
Rebuild index often needs more free space than your biggest table. Rebuild task makes the database to grow. The space allocated is released, but not shrunk unless you do it manually. So in case if you normally first shrink the DB and then re index it, the database may always have the extra room.
But shrinking data file is not advisable since it will lead to fragmentation. So do not change your plan. Try to avoid shrink totally if possible
June 29, 2009 at 12:54 am
When you rebuild an index, SQL Server "populates" a new copy of the index, then deletes the old version once the new one has been created (this way there's no long rollback process if the server falls over in the middle of the rebuild). However, this means that there has to be enough free space in the database to hold two copies of the index.
From what you've described I'm guessing that the growth factor of your .mdf file is 100GB (or maybe it's 50GB and it had to grow twice, or ...). You'd shrunk the file down to 100GB, but when the reindex ran it had to expand out again because it needed to have enough room to create the second copy of the index.
It is very rare that you will need to shrink any database file. The only time I've ever shrunk a file is when we've archived off large quantities of data that will not be replaced. For example we have a database with 7 years' data but decided to make the online database hold only 2 years' data and move the earlier 5 years' data off to an archive database, with a monthly job to move the oldest month's data to the archive database. We were therefore able to shrink the database down to around 40% of the size it was previously. Out of a couple of thousand production databases I think I've done maybe a dozen shrinks in the last decade.
June 29, 2009 at 4:44 am
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply