SQL Server資料庫檔案不滿足擴充套件條件時不再自動擴充套件

客戶SQL Server資料庫日誌中，有如下錯誤報警：

 

日期        2011-12-11 00:36:44

日誌        SQL Server (當前 - 2011-12-13 13:00:00)

源      spid222

訊息

Could not allocate a new page for database '…' 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.

 

日期        2011-12-13 08:53:25

日誌        SQL Server (當前 - 2011-12-13 13:00:00)

源      spid148

訊息

Could not allocate space for object 'dbo.…' in database '…' 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.

 

…

 

日期        2011-12-13 09:29:33

日誌        SQL Server (當前 - 2011-12-13 13:00:00)

源      spid407

訊息

Could not allocate a new page for database '…' 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.

 

檢查發現，出現問題的資料庫配置了兩個資料檔案，檔案最大限制為4000MB，growth為10%，當前兩個資料檔案分別為4,073,216 KB和3,827,136 KB，剩餘容量為22,784 KB和268,864 KB，但不足當前檔案大小的10%。

分析原因，可能是因為剩餘容量不滿足擴充套件條件，系統簡單選擇了不擴充套件，而不是儘可能的利用可用空間進行擴充套件。

下面用例項來重現這一過程：

 

USE master;

GO

IF DB_ID (N'Demo') IS NOT NULL

DROP DATABASE Demo;

GO

-- Get the SQL Server data path

DECLARE @data_path nvarchar(256);

SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)

   FROM master.sys.master_files  WHERE database_id = 1 AND file_id = 1);

 

-- execute the CREATE DATABASE statement

EXECUTE ('CREATE DATABASE Demo

ON

( NAME = Demo_dat,

    FILENAME = '''+ @data_path + 'Demodat.mdf'',

    SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 30MB )

LOG ON

( NAME = Demo_log,

    FILENAME = '''+ @data_path + 'Demolog.ldf'',

    SIZE = 5MB,  MAXSIZE = 250MB,  FILEGROWTH = 5MB )'

);

GO

 

Select * into t_demo from sys.databases;

Go

Insert into t_demo select * from t_demo;

Go

Insert into t_demo select * from t_demo;

Go

…

Insert into t_demo select * from t_demo;

Go

訊息 1105，級別 17，狀態 2，伺服器 …，第 1 行

無法為資料庫 'Demo' 中的物件 'dbo.t_demo' 分配空間，因為 'PRIMARY' 檔案組已滿。

請刪除不需要的檔案、刪除檔案組中的物件、將其他檔案新增到檔案組或為檔案組中的現有檔案啟用自動增長，以便增加可用磁碟空間。

sp_helpdb demo;

go

name     fileid filename    filegroup size     maxsize    growth    usage

-------- ------ ----------- --------- -------- ---------- --------- ---------

Demo_dat      1 Demodat.mdf PRIMARY   40960 KB   51200 KB  30720 KB data only

Demo_log      2 Demolog.ldf NULL      35840 KB  256000 KB   5120 KB log only