February 16, 2020 at 3:45 am
Hello!
When I check what is causing DB files (.mdf) to grow, it is showing as Update Statistics.
DB files are auto-growing by 1 GB.
In about a month, it has grown by more than 10 GB.
Could you please let me know the reason and how to prevent it from this?
Thanks in advance.
February 17, 2020 at 4:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 17, 2020 at 11:51 am
It's not clear - what is showing as Update Statistics? Where did you find that information - in a log file, an audit, or somewhere else?
John
February 17, 2020 at 11:55 am
It is showing in extended file events and also from script which shows what is causing database growth.
February 17, 2020 at 11:57 am
OK, good. Please will you share the relevant snippet from the Extended Events file, and the script you're using and the results it returns?
John
February 17, 2020 at 2:34 pm
I have attached the extended event snippet.
I am using the script from https://ola.hallengren.com and executing the script below from SQL Agent:
EXECUTE dbo.IndexOptimize @databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'
February 17, 2020 at 2:39 pm
That's not (just) updating statistics - it's index maintenance. When you rebuild an index, you should plan to have at least the same amount of free space in your database as the size of the index, and possibly more. If you don't, the data file is likely to have to grow.
John
February 17, 2020 at 3:03 pm
Thank you for the reply.
Right now, allocated space for the mdf file is 185746.88 MB and free space is 19268 MB(10%).
So, how much of free space do I need? and how can I find the size of the index?
February 17, 2020 at 3:12 pm
Your stored procedure will process indexes one by one, I believe, so you need as much free space as the size of the largest index (or half as much as that again, just to be safe). The largest index will usually be the clustered index on the largest table (assuming you have a clustered index on all your tables). You can find out which table that is in SSMS by right-clicking on the database name and choosing Reports -> Standard Reports -> Disk Usage by Top Tables.
John
February 17, 2020 at 3:52 pm
I used sp_spaceused to find size of index and it is 40 GB and free space on the DB is around 19 GB.
Will adding additional 40 GB or more to the disk where mdf files reside will help resolve this issue of DB growth during index rebuild?
February 17, 2020 at 3:55 pm
If that's your largest index then yes, that will probably be enough. Don't forget to allow also for the growth of your data over time.
John
February 17, 2020 at 3:57 pm
Thank you so much for quick response, appreciate it.
February 17, 2020 at 4:29 pm
That's not (just) updating statistics - it's index maintenance. When you rebuild an index, you should plan to have at least the same amount of free space in your database as the size of the index, and possibly more. If you don't, the data file is likely to have to grow.
John
It's probably not due to index maintenance. See the following setting in the code above...
@OnlyModifiedStatistics = 'Y',
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2020 at 4:37 pm
I used sp_spaceused to find size of index and it is 40 GB and free space on the DB is around 19 GB.
Will adding additional 40 GB or more to the disk where mdf files reside will help resolve this issue of DB growth during index rebuild?
It sounds to me that free space problem might be due to an "improvement" on SQL Server 2016 and above "lovingly" known as "Fast Inserts". If code uses certain type of bulk inserts (like "INSERT BULK", which should not be confused with "BULK INSERT") the code will generate an entire extent just to create 1 row (if the code is RBAR in nature even behind the scenes). As you can well imagine, that will cause a huge amount of unused space, which is allocated to a certain object and will not be used to fill in other rows. It supposedly only affects databases in the BULK LOGGED or SIMPLE Recovery Models. I've not taken the time to prove that it doesn't happen in the FULL Recovery Model.
So, see the following link on Trace Flags, find Trace Flag 692, and read about it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2020 at 4:40 pm
John
John Mitchell-245523 wrote:That's not (just) updating statistics - it's index maintenance. When you rebuild an index, you should plan to have at least the same amount of free space in your database as the size of the index, and possibly more. If you don't, the data file is likely to have to grow.
John
It's probably not due to index maintenance. See the following setting in the code above...
@OnlyModifiedStatistics = 'Y',
Mmmm... the parameter is @OnlyModifiedStatistics, not @OnlyModifyStatistics. As I understand it, all that means is that statistics are only updated if there have been any modifications to the data. It doesn't mean that indexes are left untouched. This is how the documentation suggests doing statistics only:
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'
John
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply