客户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/