SQL server error 1101 generally occurs due to disk space issues.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to fix SQL related errors.

In this context, we shall look into what causes this error and how to tackle it.

Nature of SQL server error 1101 ?

Generally, this error appears when the database engine is not able to allocate any new pages to the database due to insufficient disk space.

For instance, the error appears as below:

~~
Error: 1101, Severity: 17, State: 2
Could not allocate a new page for database ‘DBNAME’ because of insufficient disk space in the filegroup ‘PRIMARY’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting auto-growth on for existing files in the filegroup.
~~

Here are the different reasons for this error to occur.

i. The database drives are running out of enough space.

ii. You have set a restriction for the database maximum file size or have put size limits for files.

iii. Autogrowth is disabled for database files.

Steps to fix SQL server error 1101 ?

This error occurs due to space issues. So in order to fix this error, we need to make some space for the database files to grow. However, this error can vary depending upon the reason for the error to occur. Sometimes, if any restriction is set for the database maximum file size then this error occurs. In some cases, the error may occur because there is no space left in the drives.

Now let's see the different methods to resolve this error.

1. Increase Database File Size Limits or remove any restrictions

Even though there is enough drive space for the database, you still receive this error. It happens because there is a limit set for the Database File Size. In order to fix this error, you can either increase the size limit or remove the restrictions. Also, you can set database filesize to unlimited size. Here the database will grow till the database file drive limit.

2. Database Drives space

Another reason for the error is the disk drive running out of space where you have your database files. Here are the 4 different options to overcome this problem.

i. Add an additional database file to some other disk where there is enough space.

ii. Add an additional Disk Space.

iii. Move additional/unwanted files from your existing database drives.

iv. Move or Drop/Purge any unwanted data (like table/indexes).

3. Enabling Autogrowth

If you have your auto-growth setting disabled then you will receive this error message. Simply enabling it by launching the database property window will fix the error.

For that, click on three dots next to each database files in the column “Authogrowth/Maxsize” and click at the enable auto-growth checkbox.

Moreover, you can use the ALTER statement to enable the database auto-growth setting. Here is the ALTER command:

ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’DBName_Data’, FILEGROWTH = 10000KB )

Note: Ensure to run this ALTER statement for each database file separately.

Under the NAME section, pass the logical filename of your database file.

[Need urgent assistance in fixing SQL errors? – We're available 24*7.]