Due to insufficient disk space in the 'default' file group, a new page cannot be allocated to the database 'tempdb '.

I received a friend's question today. sqlserver reported that due to insufficient disk space in the DEFAULT file group, it was impossible to allocate a new page for the database TEMPDB. According to the error message, it should be due to insufficient tempdb space, check that the size of the database file corresponding to tempdb is less than 4 GB. Check that the corresponding file is self-increasing, and the hard disk space is more than 40 GB, so there should be no problem.

I received a friend's question today. SQL server reported that due to insufficient disk space in the DEFAULT file group, it was unable to allocate a new page for the database TEMPDB. According to the error message, it should be due to insufficient tempdb space, check that the size of the database file corresponding to tempdb is less than 4 GB. Check that the corresponding file is self-increasing, and the hard disk space is more than 40 GB, so there should be no problem.

After receiving a friend's question today, SQL server reported "unable to allocate a new page for the database 'tempdb' due to insufficient disk space in the 'default' file group ", according to the error message, the size of the tempdb database file is less than 4 GB, and the size of the corresponding file is increased. The hard disk space is more than 40 GB, there should be no problem. Later, she said that the disk where the file is located is fat32, and the problem is that fat32 supports a maximum file size of 4 GB. Solution:

1. Restore Files in tempdb to the default value and set the maximum value.

1) restart the SQL server service. The tempdb usually automatically changes to the initialization size.

2) if 1) fails, use the following statement:

Dbcc shrinkfile (tempdev, 10)

Dbcc shrinkfile (templog, 10)

2) set the maximum size of tempdev and templog to 4000 MB.

2. Add data files and log files, and set the maximum value to 4000 MB.

3. modify the program to solve the problem from the root cause

Analyze the cause of the problem

After asking a friend, she joined a 6kw table and 1kw table and obtained the required data and inserted it into another table, because the data volume involved was very large, all these results in the rapid increase of the tempdb database, exceeding the system file size limit. This may cause an error at the beginning of the article. Simply adding files will not solve the problem for programs with such a large amount of data. You should optimize the program, instead of adding the size and quantity of files in tempdb to solve the problem.

The number of times the program is submitted, rather than all data is submitted at one time, put in one thing.

I wrote two Processing Methods Based on program requirements.

Simulated environment:

-- Create a simulated table

SELECT id, name INTO t_1 FROM sys. sysobjects;

SELECT object_id id, name INTO t_2 FROM sys. columns;

-- Create a trail table

Create table t_s (id int DEFAULT 0, date datetime DEFAULT getdate ());

Insert into t_s VALUES (0, getdate ());

-- Create a data table to be inserted

SELECT t1.name n1,

T2.name n2,

T1.id

INTO test_1

FROM t_1 t1,

T_2 t2

WHERE t1.id = t2.id

AND 1 = 2;

Method 1:

Features: 1. High execution efficiency, no need to sort or insert a trail table

2. disadvantage: you cannot view the program execution progress. If an exception is terminated, you must execute the program again.

Create PROCEDURE [dbo]. [INSERT_d_1]

AS

DECLARE @ name1 varchar (4000)

DECLARE @ name2 varchar (4000)

DECLARE @ id int

DECLARE c1 CURSOR

FOR

SELECT t1.name,

T2.name,

T1.id

FROM t_1 t1,

T_2 t2

WHERE t1.id = t2.id

ORDER

T1.id

Open c1

Fetch next from c1 into @ name1, @ name2, @ id

WHILE @ fetch_status = 0

BEGIN

Insert into test_1 VALUES (@ name1, @ name2, @ id)

FETCH next FROM c1 INTO @ name1, @ name2, @ id

END

CLOSE c1

DEALLOCATE c1

Method 2:

Features: 1. You can track the execution progress of the program to query the t_s table. If an exception is terminated, you can write other programs to continue.

2. The program uses dual cursors to achieve low execution efficiency.

Create PROCEDURE [dbo]. [INSERT_d_2]

AS

DECLARE @ name1 varchar (4000)

DECLARE @ name2 varchar (4000)

DECLARE @ id int

DECLARE c1 CURSOR

FOR

SELECT t1.name,

T1.id

FROM t_1 t1

Order by t1.id

Open c1

Fetch next from c1 into @ name1, @ id

WHILE @ fetch_status = 0

BEGIN

-- Dual Loop

DECLARE c2 CURSOR

SELECT t2.name FROM t_2 t2 WHERE t2.id = @ id

OPEN c2

FETCH next FROM c2 INTO @ name2

WHILE @ FETCH_STATUS = 0

BEGIN

Insert into test_1 VALUES (@ name1, @ name2, @ id)

UPDATE t_s SET id = @ id, date = getdate ()

FETCH next FROM c2 INTO @ name2

END

CLOSE c2

DEALLOCATE c2

FETCH next FROM c1 INTO @ name1, @ id

END

CLOSE c1

DEALLOCATE c1