TempDb problem?

[Ajb2528]

Hi All!

I am trying to insert about 2.3 million rows of data from an Access database into a SQL Server 7.0 database using the import/export wizard. Each time I try to run the import I get the error message below:

Error at destination for row number 2328357. Errors encountered so far in this task: 1.
Could not allocate space for object '(SYSTEM table id:
-272326492)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

The tempdb data file is set to grow by 256mb and the tempdb log file is set to grow by 128mb. Both are set for unlimited growth.

The database that I am inserting the data into is set to grow by 10% on both the data file and the log file again with unlimited growth.

Can anyone help resolve this issue??

Regards,

Alan

 

[SQLBill]

I would increase the growth of TEMPDB to 1000 MB for both the log and data files. Why? You are inputting A LOT of data all at one time and the TEMPDB can't grow fast enough.

Once you input all the data, then change the values back.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Also check to make sure your hard drive isn't full or that the TempDB isn't trying to fill it up for this one job.

The way around this is to detach the Temp DB, move the .mdf & .ldf files to an otherwise empty drive, and reattach TempDB to the database. Sometimes getting the TempDB away from the HD that houses the other DBs is the only way to resolve a problem like this.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[MDXer]

You can move tempdb with an alter db as well

Moving tempdb
You can move tempdb files by using the ALTER DATABASE statement. 1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:


use tempdb
go
sp_helpfile
go



The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
2. Use the ALTER DATABASE statement, specifying the logical file name as follows:


use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go



You should receive the following messages confirming the change:
File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.
3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
4. Stop and restart SQL Server.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Ajb2528]

Thanks for all of your replies!

What I eventually did was to create a new DTS package with two connections (one for the Access db and one for the SQL Server db) an then added a workflow element to copy the table from Access to SQL Server. As part of the workflow element, I set (on the options tab) the insert batch size to 1000. This has the effect of inserting only 1000 rows at a time instead of the whole 2.3 million rows and therefore not swamping the tempdb with a mass of inserts. The downside of this approach is that it is not very quick! This was not a problem in this instance though.


Regards,

Alan

 

[Catadmin]

Glad we could help. @=) Glad you posted your solution too. This will probably help someone else out in the future.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.