Ошибка because the primary filegroup is full

Обновлено 12.11.2017

добрый день уважаемые читатели блога pyatilistnik.org, у меня на работе по исторически сложившейся ситуации, досталась в наследство одна архаичная служба СКУД Quest 2, кто не в курсе, это очень старая программа по организации системы прохода в компании, по типу СФИНКС. Все это хорошо, но в какой-то момент у меня появилась ошибка «Could not allocate space for object because the ‘PRIMARY’ filegroup is full» не дающая мне ни записывать карточки, ни что либо удалять. Давайте посмотрим как это решается и как это поправить.

Вот как выглядит данная ошибка:

Через какое-то время вообще программа Quesr 2 перестала запускаться.

Начав разбираться я обнаружил, что данная утилита, древняя как мамонт, и ее база данных построена на базе MS SQL 2000, да да, ей 17 лет. Размер базы данных оказался 1,8 ГБ.

Исправление ошибки

Если перевести первую ошибку, то видно, что забилась одна из таблиц базы данных, для того, чтобы попасть в базу нам потребуется установить, бесплатный модуль управления SQL Server Management Studio. Как он устанавливается, читаем по ссылке слева. Запускаем его, далее вам необходимо открыть все имеющиеся таблицы, в моем случае это Quest, а в ней уже Events. Щелкните по ней правым кликом и выберите пункт «Открыть таблицу». В результате чего вы увидите общее количество строк в ней.

Как очистить таблицу MS SQL

Давайте теперь вычистим таблицу и восстановим работу нашего СКУД Quest 2. Нам помогут две команды, полностью чистящие таблицу от всех записей.

либо

Если у вас объем таблицы в базе данных SQL большой, то выполнение запроса может занять некоторое время. Отличие truncate от delete, в том, что первый не ведет лог обработки. Надеюсь вам это помогло решить ошибку: Could not allocate space for object because the ‘PRIMARY’ filegroup is full.

In this article, we are going to understand SQL Server error: The Primary filegroup is full and how we can fix this error.

The database administrators must keep track of the database growth. Also, they must do the capacity planning and space management according to the growth and utilization of the database. When we create a new database, we set the value of the max size parameter to UNLIMITED most of the time. However, sometimes due to business requirements and hardware constraints, we are forced to keep the value of the MAXSIZE parameter to a specific value.

When we set the value of the MAXSIZE parameter limited to a specific value, we might encounter the Primary filegroup is full error. This error also occurs when we are using the SQL Server express edition. The maximum allowed size of the database in SQL Server Express edition is 10GB. When the database tries to grow beyond 10GB, the user will encounter the Primary filegroup is full error.

To understand the concept and error, we must create a demo setup with a specific configuration. I have created a database named DemoDatabase, and the size of the database is 40MB. Run the following query to create the database.

In the database, we are creating a table named tblFilgroup in the DemoDatabase. The table contains two columns named first_name and address. The datatype of the first_name column is varchar(500) and the datatype of the address column is char(8000). Execute the following query to create the tblFilgroup table.

Once the table is created, we are inserting some demo records in the tblFilgroup table. We are inserting 5000 rows in the table. To insert 5000 records, we can use GO 5000 keywords at the end of the insert query. Execute the following query to insert the records.

During the execution of the INSERT query, we encounter the following error.

Msg 1105, Level 17, State 2, Line 18
Could not allocate space for object ‘dbo.tblFilegroup’ in database ‘DemoDatabase’ because the ‘PRIMARY’ filegroup is full.
Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Screenshot:

To identify the root cause, first, let us check the available disk space in the workstation. Run the following command in the PowerShell.

Command Output:

As you can see in the above image, sufficient space is available in the workstation. Now, let us see the value of the MAXSIZE parameter of DemoDatabase. We can query the sys.master_files DMV to review the Autogrowth and max size values of the DemoDatabase. Run the following query.

Output

As you can in the above image, the value of the Autogrowth parameter for the data file is 12MB and the log file is 18MB. The maximum allowed size of the data and log file is set to 20MB. To resolve this issue, we can increase the value of the MAXSIZE parameter or set the value to UNLIMITED. In our case, we are going to set the value of the MAXSIZE parameter to UNLIMITED. We can change the values by using any of the following methods.

1. Change value using SQL Server Management Studio (SSMS)
2. ALTER DATABASE command

First, let us change the value using SQL Server management studio. Launch SQL Server Management Studio -> Connect to the SQL Server instance -> Expand Databases Right-click on DemoDatabase and click on Properties.

First, we will change the MAXSIZE of data files. To do that, click on Files. In the right pane, click on a balloon in the Autogrowth / Maxsize column in ROWS File Type.

A dialog box named Change Autogrowth for DemoDatabase opens. In the dialog box, set Maximum File Size to Unlimited. Click on OK to save the value and close the dialog box.

Similarly, change the value of the MAXSIZE parameter of the log files. Once parameter values are updated, click OK to close the database properties.

Alternatively, we can use the ALTER DATABASE statement to change the values of the MAXSIZE parameter. To change the MAXSIZE parameter, the syntax of the ALTER DATABASE statement is the following.

We want to set the value MAXSIZE parameter of the data and log files of the Demodatabase to UNLIMITED. To do that, run the following query.

Once the query is executed successfully, run the below query against sys.master_files DMV.

Output:

As you can see, the value of the MAXSIZE parameter is -1. According to the Microsoft document, when the MAXSIZE parameter value is -1, the data file can grow until the disk is full.

Now, run the following INSERT statement to insert 5000 records. As we have set the value of the MAXSIZE parameter to UNLIMITED, the records will be inserted successfully.

Output.

As you can see in the above image, the primary filegroup is full error has been resolved and the query inserted 5000 records in the tblFilgroup table successfully.

Summary

In this article, I have explained about the primary filegroup is full error and how to resolve the error. I have also demonstrated a use case to explain when this error can occur and how to fix it and make the database operational.