The following article lists multiple solutions for you to free up data disk space in different situations.
While working on SQL Server Database Engine, users often face the problems related to insufficient disk space, especially during the recovery process. SQL server sometimes might require extra disk space to recover files. In this article, we will try to solve the issue with effective approaches.
Disk Space Error
Database Engine shows errors like 1101 or 1105 in the cases where additional disk space is required for performing operations. If the database is online and the disk space gets filled, the database, however, remains online but the insertion of more data is not possible. If the same happens during recovery, Database engine labels it as pending resource. So it comes down to users to take steps to make space available for further operations.
Resolving the Disk Space Issue
You can take several actions to resolve the issue of disk space unavailability. Since the operations won’t take place further as long as there is no disk space so there has to be some user input to free up space.
The simplest approach would be to free disk space on the full disk. It could be done by removing indexes or tables which are unnecessarily acquiring space to lighten up the size on the disk. You should also take into account the error message and figure out the problem. Freeing up some space by removing needless tables can allow the files in the file group to function and work properly.
If the error is occurring during the recovery process of the database, then you must attempt SQL Server recovery of the database after resolution of the error to avoid data loss.
Adding Files to a Different Disk
If the Database engine shows an error of disk space unavailability then you can try adding files on a different disk using Transact-SQL. It should solve the disk space issue as you are now using disk space of different disk to add files. You can try adding files to different disk with the help of ALTER DATABASE command and defining ‘filegroup’ name.
Alternatively, you can also move the databases by specifying a new location. For this use option, FILENAME Clause of ALTER DATABASE statement and move the Full-text catalog files, logs, and Data to a new location.
You can also use SQL Server Management Studio to add files on some other disk and that can free up space. There is an option of Object Explorer in SQL management Studio where users can specify Autogrowth column i.e. file growth and users can also specify a maximum size limit for files according to the available disk space.
Increasing File Size and Recovery of Database
Once the Autogrow is disabled and the database is online, and there is available disk space then users can manually try increasing the file size so that a single growth increment can be produced.
Users can also try enabling Autogrow with the help of Alter Database statement to fix a non-zero increment in Filegrowth Option. So now users can simply increase the MAXSIZE value to resolve the issue for both the cases.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdb fix and sql recovery software products. For more information visit https://www.datanumen.com/