How to go about extending volumes on SQL Server boxes?

Question

Here is a question for the sys ops out there (or those who have gone through this experience before).

My company wants to allocate additional disk space on existing SQL Server boxes in production. We are wondering whether extending a volume ("Extend Volume..." in Disk Management) can be done ONLINE or whether the SQL Server Service needs to be shut down.

Any reason why SQL Server Service should be stopped to do this or is it OK to just go an perform this operation during the day?

Just in case the system specs are relevant. This is a real box (no virtualization) running Windows 2012 R2 and SQL Server 2008 R2.

Thanks!

Answer 1

You can extend disks while the database is online, but I will recommend you plan for a brief 15 - 30 minute outage during which you can take the services offline.

I've extended disks hundreds of times while databases were online and running without issues. However one time, and only one time at this point in my career, the operation caused corruption in some SharePoint Content databases that were running and I lost a fair chunk of my hair getting things back up and working.

If you want/have to do this while the database is running, the key is that while you're extending the disks, you do NOT want any file growth activities occurring lest they try to grab some of the not-quite-ready new space. I think this is what happened in my situation as our Sharepoint environments were treated as a "better" fileshare for the company and I'm pretty sure an auto-growth event occurred during the disk extend operation.

For what it's worth, MS also recommends you turn off the services while performing this operation. After the disks are extended also be sure to run a DBCC CHECKDB to be certain no corruption occurred.