October 8, 2008 at 1:21 am
Hi,
I have never deleted/archived anything from DB but when I ran DBCC shrinkfile command, size of DB has gone down from 80 GB to 45 GB. Could you please explain what could be the reason?
Thanks in advance.
October 8, 2008 at 3:29 am
indexes rebuild?
Wilfred
The best things in life are the simple things
October 8, 2008 at 3:58 am
sanjeev_krs2004 (10/8/2008)
Hi,I have never deleted/archived anything from DB but when I ran DBCC shrinkfile command, size of DB has gone down from 80 GB to 45 GB. Could you please explain what could be the reason?
Thanks in advance.
Everything unnecessary things inside your DB has gone BYE-BYE! - and mostly is better for your DB!
For more info check the BOL for the DBCC Shrinkfile!
:hehe:
October 8, 2008 at 4:09 am
You should not worry about the Data. DBCC Shrinkfile never deletes any data.
It will shrink datafile to specific size which you have mention during the DBCC Shrinkfile command.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
October 8, 2008 at 5:37 am
Basically shrinkfile will reorganise the contents of the file and any unused pages will be moved to the end of the file. The unused pages at the end of the file can be removed to shrink the file. Data changes and their associated index updates can cause the empty spaces in the file but also the initial size of the file will have a lot of reserved but unused space - shrinking the file will get rid of the reserved unused space and the empty space caused by the data/index changes.
October 8, 2008 at 7:48 am
Im sure that your db had many updates and deletes and when the above mentioned operations causes page splits to occur. when you run the dbcc shrinkfile, sql server rearranges the pages thereby eliminating free pages and releases the excess space to the OS. The result, db file size goes down which is good for you to manage.
October 8, 2008 at 8:01 am
sanjeev_krs2004 (10/8/2008)
Hi,I have never deleted/archived anything from DB but when I ran DBCC shrinkfile command, size of DB has gone down from 80 GB to 45 GB. Could you please explain what could be the reason?
As a slight aside, by shrinking the DB, you have badly fragmented all of your indexes. It would be advisable to go and rebuild them all. That will however increase the size of the DB again
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
October 8, 2008 at 9:54 am
GilaMonster (10/8/2008)
...It would be advisable to go and rebuild them all...
Gila what will you do if you have the table with over 2 million records and after DBCC shrinking rebuilding the indexes will take too much time how can you do it faster than normal rebuilding!
I have table with 2 million records and 1 clustered and 3 non-clustered index on that table and on test environment rebuilding the index take too much time!
Sorry that I'm changing the way of this topic!
Dugi
October 8, 2008 at 11:55 am
If the fragmentation is low (< 30%) then reorganise instead of rebuilding. If you can, switch the DB into Bulk logged recovery for the duration of the rebuild. Index rebuilds are bulk operations and won't be fully logged in any recovery model other than full.
2 million rows isn't a lot and shouldn't take ages to rebuild. When you say too much time, what kind of time are we talking. If rebuilds are taking forever and the server doesn't have a lot of other activity, I would look for hardware bottlenecks, most likely disk
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
October 8, 2008 at 11:58 am
As the command suggests....your database file was shrunk.....and the empty space from your db file was truncated and release back to the Server OS.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply