Recommendations and guidelines on dynamically extending SQL Server disk volumes

Applies to: Microsoft SQL Server 2005 Enterprise EditionSQL Server 2008 EnterpriseSQL Server 2008 R2 Enterprise

Summary


In a database environment, sizing the volumes on which the database files reside is very critical. Improper sizing can often result in space related errors and can also affect performance and reliability of the databases. In situations like these to create more disk space, an administrator may be required to expand the volumes on which the database files reside to ensure proper functionality of the databases.

Windows offers Diskpart utility to extend the volume or partition.  In situations where a volume or partition on which the database files reside need to be extended, it is recommended that the SQL Server services be first stopped before extending the volume to create more space. However; it may not be possible to incur downtime by stopping SQL Server services. In these cases, you can still use diskpart utility to extend the disk volumes when SQL Server services are online.

Once the disk volumes are extended successfully, it is also recommended but not required to run database consistency check using the DBCC CHECKDB command to ensure the logical and physical integrity of all the objects in the specified database. DBCC CHECKDB can take significantly longer time depending on the size of the database. In such cases, you can consider running dbcc checkdb with physical_only option to limit the checking to the integrity of the physical structure of the page and record header and the allocation consistency of the database. You can also consider using the trace flags 2562 and 2549 as described in the knowledge base article 2634571 to improve the performance of DBCC CHECKDB command when used with PHYSICAL_ONLY option.



Additional resources:

DBCC Checks and Terabyte-Scale Databases