May 21, 2012 at 4:55 am
Hello,
Any help on the following mystery please.
I am puzzled why after altering my databases "size" parameter from 2Gb to 4Gb does a subsequent shrink reduce the DB size below the new size parameter?
I had thought that once you'd altered a database's size that this would now be the base value to which a shrink would take the file to.
I'm sure there's a good reason why this is, but it does appear rather pointless to be able to change a databases size if the new size is ignored when shrinking.
Regards
Steve
May 21, 2012 at 5:04 am
You should disable Auto Shrink entirely on all databases, it's terrible for performance as it causes loads of fragmentation.
When running the command manually, there's a parameter you can specify for how much it should shrink to, although even this shouldn't be run on a regular basis. The only time it should be run is if you've had a huge change in the size of the data due to an ad-hoc process, you believe it won't grow back to the data file size any time in the future and you need to recover the space (you should rebuild all indexes after doing so).
May 21, 2012 at 5:42 am
HowardW (5/21/2012)
You should disable Auto Shrink entirely on all databases, it's terrible for performance as it causes loads of fragmentation.
None of my DB's use auto-shrink, so there's no issues there.
HowardW (5/21/2012)
When running the command manually, there's a parameter you can specify for how much it should shrink to, although even this shouldn't be run on a regular basis. The only time it should be run is if you've had a huge change in the size of the data due to an ad-hoc process, you believe it won't grow back to the data file size any time in the future and you need to recover the space (you should rebuild all indexes after doing so).
Indeed, I have heard that this sort of thing should be avoided.
However, my question remains I think; Why does a database that started life as having an initial size of 2Gb upon creation and now has been altered to have a base size of 4Gb always revert to the original size setting after a SHRINK?
I would've thought that having reset the DB size to 4Gb minimum that this was the new base size and thus any subsequent shrink operations would not reduce the size below that limit.
It doesn't seem to work this way and that's what's bothering me.
May 21, 2012 at 5:51 am
raotor (5/21/2012)
HowardW (5/21/2012)
You should disable Auto Shrink entirely on all databases, it's terrible for performance as it causes loads of fragmentation.None of my DB's use auto-shrink, so there's no issues there.
HowardW (5/21/2012)
When running the command manually, there's a parameter you can specify for how much it should shrink to, although even this shouldn't be run on a regular basis. The only time it should be run is if you've had a huge change in the size of the data due to an ad-hoc process, you believe it won't grow back to the data file size any time in the future and you need to recover the space (you should rebuild all indexes after doing so).Indeed, I have heard that this sort of thing should be avoided.
However, my question remains I think; Why does a database that started life as having an initial size of 2Gb upon creation and now has been altered to have a base size of 4Gb always revert to the original size setting after a SHRINK?
I would've thought that having reset the DB size to 4Gb minimum that this was the new base size and thus any subsequent shrink operations would not reduce the size below that limit.
It doesn't seem to work this way and that's what's bothering me.
What command are you running to do the shrink?
May 21, 2012 at 7:19 am
HowardW (5/21/2012)
What command are you running to do the shrink?
I'm using the standard DBCC SHRINKDATABASE but the same result happens if I use the more selective DBCC SHRINKFILE version.
I know that there's a size parameter you can specify to which the file being shrunk will be reduced to (data permitting of course). However, I am puzzled as to why once you've designated a new size for your DB that the basic shrink operation does not comply with this new size limit.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply