The SQL Server Error Message 1101 occurs when a database runs out of space. The error message is given below.
ERROR 1101: Could not allocate a new page for database because of insufficient disk space in filegroup. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting Autogrowth on for existing files in the filegroup.
The error message also suggests possible solutions to resolve the error. The space can be cleared by dropping a large unwanted table, adding additional files to filegroup to a different storage if existing storage is full or by setting Autogrowth setting for files to ON.
If the storage is full, additional files can be created on new disk/storage using the T-SQL shown below
USE [master] GO ALTER DATABASE [mydb] ADD FILE (NAME = N'newdatafile', FILENAME = N'E:\mydb\newdatafile.ndf', SIZE = 4096KB, FILEGROWTH = 102400KB) TO FILEGROUP [SECONDARY] GO
The above TSQL adds a file ‘newdatafile’ to Secondary filegroup of mydb database. The new file is created with initial size of 1 MB and Autogrowth of 100 MB. To disable Autogrowth set FILEGROWTH to 0. The current size and Autogrowth size should be decided based on database size and careful capacity planning. The numbers here are taken just for explanation.
The Autogrowth setting can be cross checked using the below T-SQL.
SELECT file_id, physical_name, name As logical_name, growth FROM sys.database_files
The Autogrowth setting is disabled if the “Growth” column has value of 0. The Autogrowth setting can be enabled using below T-SQL.
ALTER DATABASE [mydb] MODIFY FILE (NAME = N'newfile', FILEGROWTH = 102400KB)
The above T-SQL changes the Autogrowth setting for ‘newfile’ of mydb database to 100 MB.
It is not advised to shrink database in order to free space and resolved this issue. Shrink will affect other user transaction and thus needs a downtime.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
