Ошибка sql 1101

The ‘SQL server error 1101’ occurs due to space issues.

Here at Bobcares, we have seen several such SQL related issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and see how to fix it.

Why does ‘SQL server error 1101’ occur

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.

  1. The database drives are running out of enough space.
  2. You have set a restriction for the database maximum file size or have put size limits for files.
  3. Autogrowth is disabled for database files.

How we fix ‘SQL server error 1101’ error

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 suggestions our Support Engineers provide to our customers 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.

  • Add an additional database file to some other disk where there is enough space.
  • Add an additional Disk Space.
  • Move additional/unwanted files from your existing database drives.
  • 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 any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, this error arises while changing or viewing the values of any advanced server configuration option. Today, we saw the suggestions our Support Engineers provide to our customers to fix this error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

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:mssqldatanewdatafile.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.

  • What Should be the Best Value for database Autogrowth settings?
  • How to Enable Instant File Initialization in SQL Server?

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:

  • Fix Error 5144 and Error 5145 for Long or Failed Autogrow of Database Files
  • Understanding Autogrowth Operation in SQL Server Databases
  • How to Enable IFI – Instant File Initialization?
  • Manage Database Files during huge Data Load
  • Author
  • Recent Posts

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.

Manvendra Deo Singh

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.

Источник
  • Remove From My Forums

  • Question

  • Hello,

    I am getting this error on my server; SQL Server 2005 Enterprise Edition. Available space on my hard disk is 107 GB and log file is at other location. The mdf file for this database is of size 153 GB.

    So as lot of solutions to this problem say to set the file size to autogrowth because I have sufficient space as of now (107 GB).

    But I am not sure about the consequences and also by how much size I should allow the autogrowth?

    Any urgent help on this will be highly appreciated.

    Regards,

    In persuit of learning SQL.

Answers

  • Hi

    AutoGrowth 10% would be the ideal option.

    You can consider the autogrowth performance based on this link.

    http://support.microsoft.com/kb/315512

    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

    I would disagree with 10% growth. Since its in percentage, every growth would be bigger in size.. Assume that db size is 100 GB, it would grow 10 GB (which is high)  and it would become 110 GB and next growth would be 11 GB.. and it increases exponentially.
    Filegrowth time would be included in response time by application.

    I would suggest to keep fix growth of 512 MB.

    Balmukund Lakhani | Please mark solved if I’ve answered your question, vote for it as helpful to help other user’s find a solution quicker

    ———————————————————————————
    This posting is provided «AS IS» with no warranties, and confers no rights.

    ———————————————————————————
    My Blog |
    Team Blog | @Twitter

    • Proposed as answer by

      Friday, August 26, 2011 6:15 AM

    • Marked as answer by
      Peja Tao
      Thursday, September 1, 2011 8:14 AM

  • I would agree also — however, I disagree on the fixed size of 512MB.  The fixed size for growth should be a factor of your daily growth and allow enough time for you to schedule a manual growth to allow for several months of growth.

    For example, if your daily growth is 500MB per day — an autogrowth of 512MB will only cover a single days activities.  If you happen to be on vacation for 2 weeks — and this occurs on the first day of your vacation, your users will be affected by the
    autogrowth every day until you get back.

    You want to also enable instant file initialization — this will reduce the impact of the autogrowth event on the system.

    To give you an idea, on my 1.3TB system — with an average daily growth of almost 700MB, I have an autogrowth setting of 20000MB.  If the system were to hit this, with instant initialization enabled — the autogrowth only takes a couple of seconds. 
    Of course, this is because of the IO subsystem, the SAN we are using and other factors.  I would not recommend a size that large unless you have tested your IO subsystem, your SAN and the system.

    Jeff Williams

    • Proposed as answer by
      Peja Tao
      Monday, August 29, 2011 8:27 AM
    • Marked as answer by
      Peja Tao
      Thursday, September 1, 2011 8:14 AM

Источник

I heard that mysql version prior to 8.0.13 accept default value for json type column, so I using the cmd:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT '{}' ;

but receive error:

Error Code: 1101. BLOB, TEXT, GEOMETRY or JSON column 'values' can't have a default value

So how do I fix it?

I’m using mysql version 8.0.19 and client tool Workbench

GMB's user avatar

GMB

213k24 gold badges82 silver badges132 bronze badges

asked Apr 12, 2020 at 8:58

Dave kam's user avatar

From version 8.0.13 onwards, the documentation says (emphasis is mine):

The BLOB, TEXT, GEOMETRY, and JSON data types can be assigned a default value only if the value is written as an expression, even if the expression value is a literal.

You can make your default an expression by surrounding the literal value with parentheses:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT ('{}') ;

Or:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT (JSON_OBJECT()) ;

Prior to version 8.0.13 of MySQL, it was not possible to set a default value on a JSON column, as the 8.0 documentation points out a few paragraphs later :

The BLOB, TEXT, GEOMETRY, and JSON data types cannot be assigned a default value.

answered Apr 12, 2020 at 9:19

GMB's user avatar

GMBGMB

213k24 gold badges82 silver badges132 bronze badges

3

According to the laravel docs:

$table->json('movies')->default(new Expression('(JSON_ARRAY())'));

The default modifier accepts a value or an IlluminateDatabaseQueryExpression instance. Using an Expression instance will prevent Laravel from wrapping the value in quotes and allow you to use database specific functions. One situation where this is particularly useful is when you need to assign default values to JSON columns

https://laravel.com/docs/8.x/migrations#default-expressions

answered May 2, 2022 at 15:47

Antonio Gonzalez's user avatar

MySql syntax is a bit different than Oracle/Postgres, hence to make say JSON_Array as default, the query would be —

ALTER TABLE table_name ALTER column_name SET DEFAULT (JSON_ARRAY());

Further reference here

answered Oct 14, 2021 at 16:30

Anvita Shukla's user avatar

Источник

При обновлении Битрикса получил ошибку:

MySQL Query Error: CREATE TABLE b_sale_cashbox_check_correction( ID int(11) unsigned not null auto_increment, CHECK_ID int(11) not null, CORRECTION_TYPE varchar(50) not null, DOCUMENT_NUMBER varchar(35) not null, DOCUMENT_DATE date not null, DESCRIPTION varchar(255) default », CORRECTION_PAYMENT text default », CORRECTION_VAT text default », PRIMARY KEY (ID) ) [[1101] BLOB, TEXT, GEOMETRY or JSON column ‘CORRECTION_PAYMENT’ can’t have a default value]

Решение — нужно создать эту таблицу «руками»:

 
CREATE TABLE b_sale_cashbox_check_correction( ID int(11) unsigned not null auto_increment, 
CHECK_ID int(11) not null, CORRECTION_TYPE varchar(50) not null, 
DOCUMENT_NUMBER varchar(35) not null, DOCUMENT_DATE date not null, 
DESCRIPTION varchar(255) default '', CORRECTION_PAYMENT text, 
CORRECTION_VAT text, PRIMARY KEY (ID) )
Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка sql 1093
  • Ошибка sql 1075
  • Ошибка spn 523704 fmi 5
  • Ошибка spn 523470 fmi 16 камаз евро 5
  • Ошибка spn 520264 fmi 11