September 17, 2018 at 7:52 pm
Hi All
I shrank data file in a production database . at first the size was 99 GB , after shrinking it went down to 87% but 1 hour later it went back to 99 GB .
Why does this happen ?
can I fix this by performing restart ?
or it could be due to index fragmentation but is it possible to get fragmented that big ?
I know shrinking data file is not a good practice but i need to shrink it until i get more disk space
Your feedback is much appreciated
thank you
September 17, 2018 at 7:52 pm
Sorry I mean it went down to 87 GB
September 17, 2018 at 9:32 pm
Did you rebuild the indexes after the shrink? That would do it.
September 17, 2018 at 11:06 pm
pietlinden - Monday, September 17, 2018 9:32 PMDid you rebuild the indexes after the shrink? That would do it.
I don't do it actually ....
September 21, 2018 at 12:19 am
This was removed by the editor as SPAM
September 21, 2018 at 3:50 am
WhiteLotus - Monday, September 17, 2018 7:52 PMHi All
I shrank data file in a production database . at first the size was 99 GB , after shrinking it went down to 87% but 1 hour later it went back to 99 GB .
Why does this happen ?
can I fix this by performing restart ?
or it could be due to index fragmentation but is it possible to get fragmented that big ?I know shrinking data file is not a good practice but i need to shrink it until i get more disk space
Your feedback is much appreciated
thank you
If you're short of space then another option to consider before shrinking files is to compress some of your tables and indexes. This can save a lot of space. You can do it by right clicking of the table in object explorer and selecting the "Storage" option. This will also sometimes make queries faster.
September 21, 2018 at 7:38 am
what is the autogrowth setting? sounds like you shrank all the empty space out and it did an autogrow for new data. that would be a very odd increment tho...
September 21, 2018 at 7:45 am
Jonathan AC Roberts - Friday, September 21, 2018 3:50 AMWhiteLotus - Monday, September 17, 2018 7:52 PMHi All
I shrank data file in a production database . at first the size was 99 GB , after shrinking it went down to 87% but 1 hour later it went back to 99 GB .
Why does this happen ?
can I fix this by performing restart ?
or it could be due to index fragmentation but is it possible to get fragmented that big ?I know shrinking data file is not a good practice but i need to shrink it until i get more disk space
Your feedback is much appreciated
thank youIf you're short of space then another option to consider before shrinking files is to compress some of your tables and indexes. This can save a lot of space. You can do it by right clicking of the table in object explorer and selecting the "Storage" option. This will also sometimes make queries faster.
There are tools to estimate the saving for compression. I would also recommend testing this thoroughly in a non-prod environment prior to simply applying compression.
While you can gain, you can also lose!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 23, 2018 at 9:56 pm
jsqldba - Friday, September 21, 2018 7:38 AMwhat is the autogrowth setting? sounds like you shrank all the empty space out and it did an autogrow for new data. that would be a very odd increment tho...
just checked , the autogrow for data file is 20% , unrestricted growth
What do u think ?
September 24, 2018 at 12:44 am
WhiteLotus - Sunday, September 23, 2018 9:56 PMjsqldba - Friday, September 21, 2018 7:38 AMwhat is the autogrowth setting? sounds like you shrank all the empty space out and it did an autogrow for new data. that would be a very odd increment tho...just checked , the autogrow for data file is 20% , unrestricted growth
What do u think ?
well, the math doesn't work. 87GB x 1.2 = 104.4GB. i've no idea how you got to 99GB but i would change the autogrowth to something more reasonable like 1GB instead of a percentage.
instead of shrinking to 87GB shrink it to 90GB. leave SOME freespace in the file for future growth. then monitor for autoextend events and correlate to the processes that you are running.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply