Techyaz.com

Fix Error 1101: Could not allocate a new page for database

by · Published · Updated

We get error 1101 because of space issues. Database engine doesn’t able to allocate new pages to the database because of insufficient disk space. The error details are given below:

Error: 1101, Severity: 17, State: 2
Could not allocate a new page for database ‘DBNAME’ because of insufficient disk space in filegroup ‘PRIMARY’. 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.

There is another error 1105 that is also very much like above error code. Error 1105 can also be fixed using same solution given in this article.

Error 1101 – Root Cause

Main reason behind error 1101 or error 1105 is the insufficient disk space in filegroup/files. You might also get this error if your database drives are running out of space. There are multiple reasons that can cause this error to generate. Some of them are given below.

  • You have restricted database maximum file size or put size limits for files.
  • Database file drives are running out of space.
  • Autogrowth is not enabled for database files.

Solutions

To fix this issue we need to make space for database files to grow. Issue might vary depending on the nature of issue mentioned in above section. Sometimes, you might face this issue if maximum size for database files are restricted to some values so whenever database will grow to that point, SQL Server will not allow to allocate any page to that database. Sometimes you might get error 1101 because you don’t have any space on your database drives. Here, I will discuss each case individually and its solution to fix it.

Unrestrict or Increase Database File Size Limits

You might have enough space in your database drives but still you can get error 1101 because the maximum file size of database files is limited to some value and that causes not to grow your database beyond that size. You can unrestrict or increase the existing value of maximum file size to some bigger value. We have also an option to set database file size to unlimited size value that means database file will grow till the database file drive limit. Make sure to set appropriate autogrowth size for each database files.

You can set unlimited file size option or increase the value of maximum file size using GUI in SQL Server Management Studio or using ALTER statement.

  • Connect to Database Instance in SQL Server Management Studio. Expand Database folder.
  • Right Click on your database and choose Property window.
  • Click on Files tab from left side pane in database property window.
  • Here you can see all database files. Now you can click on three dots given next of database files in “Autogrowth/Maxsize” column. You can see max size is set to a limited value as shown in below screenshot. I have chosen unlimited option and clicked on Ok button.

Set Database file size to unlimited

Now we will not get any issue if our database drive has enough space.

Space in Database Drives

Another possibility for this error is, there might be possibility that your drives on which you have placed your database files are running out of space. If this is the case then you have below 4 options.

  • Add additional database file to different disk where there is enough space.
  • Move additional/unwanted files from your existing database drives.
  • Drop/Purge or Move unwanted data (table/indexes)
  • Add Additional Disk Space

Immediate fix of this issue is to add new database file to another drive where you have enough space to run your transactions. You can use below command to add addition database file to another disk.

USE [master]
GO
ALTER DATABASE [DBNAME]
ADD FILE (NAME = N'NewFileName', FILENAME = N'F:\mssql\data\newdatafile.ndf', SIZE = 100096KB, FILEGROWTH = 10240KB)
TO FILEGROUP [SECONDARY]
GO

You can also add new database file in SQL Server Management Studio by launching Database Property Window. Select Files from left side pane and Click on “Add” button showing in below screenshot. Make sure to set autogrowth value carefully for any database file. Also it is recommended to enable IFI – Instant File Initialization to speed up autogrowth process for your databases. Refer to below article to get best value for autogrowth for your database files. You can also read what are the benefits and how to enable IFI – Instant File Initialization in below article.

Add database file using GUI

You can also have a look at the existing database drives whether there is any unwanted files or older backup files (*.bak) are not stored. Sometimes we run backups into database drives and forgot to delete or move them. Make sure to drop or move all unwanted files to some other location. Database drives should have only database files.

Final and long-term solution is the data purging. Identify the database objects that are not needed and then plan the data removal or data purging process to make space on your database.

We also have an option to extend the database drives as per the data growth. Make sure to plan this activity carefully before deploying it on production servers.

Enable Autogrowth

I have already written an article on Database autogrowth property setting. Read attached article to find out best value for Autogrowth settings.

You might get error 1101 if you have not enabled authogrowth setting for your database files. You need to enable it by launching database property window. You can click on three dots next to each database files in column “Authogrowth/Maxsize” and click at the enable authogrowth checkbox as showing in below screenshot.

Enable Autogrowth

You can also use ALTER statement to enable database autogrowth setting for your database file. Remember, you need to run this ALTER statement for each database file separately.

Run below ALTER command to enable autogrowth for database file.

--Pass logical filename of your database file for under NAME section.
ALTER DATABASE [Techyaz] MODIFY FILE ( NAME = N'Techyaz_Data', FILEGROWTH = 10000KB )

Make sure to enable Instant File Initialization to speedup autogrowth operations. Links to enable Instant File Initialization is attached in this article.

If you are facing this issue for tempdb database then you can consider one more option apart from all of the above options. And this option is to restart SQL Server service if it’s allowed. Keep in mind, SQL Server service restart will cause an outage for few minutes but it will create new tempdb files with initial size and error 1101 will be disappeared. This is not recommended solution because of outage so you should work on long terms solutions.

You can also use above solutions to fix error 1105 that is very much similar to error 1101. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Read More:

Manvendra Deo Singh
Follow me:
Latest posts by Manvendra Deo Singh (see all)
Summary
Article Name
Fix Error 1101: Could not allocate a new page for database
Description
We get error 1101 because of space issues. Database engine doesn’t able to allocate new pages to the database because of insufficient disk space. The error details are given below: Error: 1101, Severity: 17, State: 2 Could not allocate a new page for database ‘DBNAME' because of insufficient disk space in filegroup 'PRIMARY'. 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.

Tags:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *