I was On call this week and one of our databases files became over 90% full. This is the threshold we set to alert us to manually expand any file. We have autogrowth just in case, but we want to manually expand files after hours to avoid the performance impact on SQL server when expanding files.The question I had was do I wait for the maintence window, when services are shut off and no database activity is going on, or do I expand the file after business hours when there is minimun activity. One of the things we’ve been working for is a maintenance window reduction, so for future reference, I needed to know what the performance impact was – not to mention the fact that the maintenance window is at 2 AM and with a baby, I wanted all the sleep I can get.Here’s what I’ve found – any data changes that cause a page split or an additional page will be suspended, pretty much everything else will work – a good explanation here -
Any operation that depends on a new extent allocation will suspend while the data file is being expanded. If you are inserting on a non-full page, it will work. In-place updates will work. New rows that trigger an extent allocation will suspend.
So here’s what I did – I added 5Gb to the 700 in a non-peak time and it took 1 minute and 30 seconds. During this time, I monitored for blocking, there was none. My goal was to grow it 15 Gb total, so I repeated this process two more times.