Could not allocate space for object sys.sysfiles1.sysfiles1 in database

一、 问题描述

给数据库添加数据文件时遇到了一个奇怪的报错：

Could not allocate space for object 'sys.sysfiles1'.'sysfiles1' in database 'dbname' 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.

检查各磁盘均有剩余空间超过100G，新增的数据文件也没有设上限，怎么就没法分配空间了？

网上搜索文章发现是跟mdf这个文件有关

To add space to the primary filegroup requires some free space in the first MDF file. Adding just 1MB to the MDF file allowed the addition of another (NDF) file without error.

检查mdf大小，发现确实离上限还有11M，但是因为每次自动扩展设置是100M，到达了上限不能再扩了。

二、 解决方法

• 改小mdf文件自动扩展设置（例如1M），让它能自动扩展
• 改大mdf文件上限，让它能自动扩展

三、 报错原因详解

sys.sysfiles1是一个特殊的系统表，用于存储数据库中各数据文件的位置。这个表中的数据被限制死只能存在mdf文件中而不能到其他ndf文件中（不能理解为啥要这么设计...）。该报错是因为：每当新增数据文件时会往里插入一行数据，此时mdf文件刚好没有空间了，而自动扩展100M又到达了mdf文件设置的上限，只能报错无法分配空间。

以下是文章中的原文：

sys.sysfiles1 is a system table, usually hidden away from view, which holds information about all files in the database. It makes sense that this file would be restricted to living in the .mdf - the first file of the PRIMARY file group, and cannot move to other files in the PRIMARY filegroup (after all, if you stored information about which files are in the database inside another file in the database...)

From that error message, you're unable to even add a new row to sys.sysfiles1, whether for the PRIMARY filegroup, or adding a new one.

If you  need to make room in the .mdf file, you have some options - if you can grow the file by a couple of MB, do so. Alternatively, BACKUP FIRST (goes without saying!), and either drop some old tables (where the data is originally in the .mdf), or create a backup copy of them into another database, or into a new table (which will be on your autogrowing .ndf file). You can then drop the original table. If you copied the table into the same database, you can rename the new table to match the old name, and your database will still be working. You should then have enough room in the .mdf to add your new file.

参考

Adding space to PRIMARY filegroup · What Did I Learn Today

Error: "PRIMARY filegroup is full" when adding databse file