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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/81227/viewspace-713277/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/81227/viewspace-713277/