Unable to allocate new page for database ' TEMPDB ' because there is not enough disk space in filegroup ' DEFAULT '

I got a question from a friend today, SQL Server Report "because there is not enough disk space in filegroup ' DEFAULT ' to allocate a new page for database ' tempdb ', you should have insufficient tempdb space to view the tempdb corresponding database file size less than 4g by error , see its corresponding file for self growth, hard disk space and 40g more points, that should be no problem. Later she said that the disk in the location of the file was FAT32, and that the problem was that the FAT32 maximum supporting file size was 4g. Solution:

　　1, let the files in tempdb restore the default value, set its maximum value

1 Restart the SQL Server service, tempdb typically automatically changes to the initialization size

2) If 1 fails, use the following statement

DBCC SHRINKFILE (TEMPDEV, 10)

DBCC SHRINKFILE (templog, 10)

2) Then set the maximum size of tempdev and Templog to 4000m

　　2, add data files and log files, and set their maximum value of 4000m

　　3. Modify the program to solve the problem from the root

Analyze the cause of the problem

After asking a friend, she was associating a 6kw table with a 1kw table, and then getting the data needed to be inserted into another table because the amount of data involved was very large, all resulting in a rapid increase in the tempdb database, exceeding the system file size limit. Causes the article to start the error, the light adds the file, has not solved the problem for this big data quantity program. Should be optimized on the program rather than by adding the size and number of files in tempdb to solve the problem.

Add the number of times a program is submitted, not all data is submitted once, and placed in one thing.

I have written two methods of processing based on program requirements.

Simulated environment:

--Create a mock table

SELECT id,name into t_1 from sys.sysobjects;

SELECT object_id id,name into t_2 from Sys.columns;

--Create a trace table

CREATE TABLE t_s (id int DEFAULT 0,date datetime default GETDATE ());

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

--Create the data table you want to insert

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 One:

Features: 1, the advantages of high performance efficiency, no need to sort and insert tracking table

2, disadvantage: can not see the progress of program execution, if the exception terminates, you must do it 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 BY

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 Two:

Features: 1, you can track the execution progress of the program query t_s table, if the exception to terminate can write other programs continue

2, the procedure uses two revisit the standard, the execution efficiency is lower

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

--Double cycle

DECLARE C2 CURSOR for

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