[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL: Not enough space on temporary disk

Posted on 2007-10-02
11
Medium Priority
?
344 Views
Last Modified: 2013-11-30
We have a large DTS that extracts info from one DB and dumps it into SQL. We don't usually run this DTS alot so I don't know at what point something might have happened to cause this problem. When we execute the package we get "Not enough space on the temporary disk"

We checked all the disks on our local SQL machine each has over 10GB free. Our log files write to our biggest disk (240+ GB free). This DTS can't be broken up into smaller inserts either it is all or nothing.

This DTS has worked in the past so I do not know why it has decided to crap out.
0
Comment
Question by:jamesPeck
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 19998458
Increase size for tempdb, I think this is where you running out.
0
 

Author Comment

by:jamesPeck
ID: 19998511
How do I go about doing that?
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 19998535
before I tell you this, is your database in simple or full recovery mode
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 19998552
Check your Temp directory; this may be in C:\TEMP, C:\WINDOWS\TEMP, C:\WINNT\TEMP or C:\DOCUMENTS AND SETTINGS\<USER NAME>\LOCAL SETTINGS\TEMP.  Make sure you set the folder view options in Windows to allow viewing hidden files and folders or you may not find the temp directory.  Once you open the temp directory in Windows Explorer you can delete all files that are older than today's date in order to free up some space.
0
 

Author Comment

by:jamesPeck
ID: 19998900
I inherited this DB so I'm not to sure. I'll guess simple if thats the default. Why is changing the tempdb size going to possibly cause some catastrophic incident?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20000580
>>. I'll guess simple if thats the default. <<
No  It is Full.

When was the last time you backed up your database?  Or should I ask have you ever backed up your database?
0
 

Author Comment

by:jamesPeck
ID: 20001363
The database is backed up nightly to a tape. We also copy data to a backup SQL server. The setting is simple restore (found under the tempdb database properties) I just need this DTS to run everything is fine with the DB except this DTS.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20004037
That is correct.  Tempdb is always simple.

If you have the disk space, you need to allow it to auto grow.
0
 
LVL 1

Expert Comment

by:benefitsystems
ID: 20018209
I am having the exact same problem.  I found the temp folder in two places

1: C:\temp
2:C:\WINDOWS\TEMP

I'm not sure which one of the two I need to delete. I'm using the Ms SQL SERVER 2005.
Additionally, I've tempDB  unrestricted Autogrowth by 1 MB, is that fine?

Thanks in advance.
0
 

Author Comment

by:jamesPeck
ID: 20080471
I did some searching and this is what was recommended. I created 4 data files for the tempdb on a seperate disk. Set the max size of each to 1GB (1024MB) and set autogrow to 10%. Rebooted and tried to run the DTS package again, failed with same error.

Any suggestions?
0
 
LVL 1

Accepted Solution

by:
benefitsystems earned 1500 total points
ID: 20080836
I got mine fixed. I was lacking space on the destination server where the file FIRST get exported to.

FYI: In  my case,  DTS first export the file to a ServerB, from the SERVER B the data get exported to THE SQL SERVER C.  Check if you are doing something similar, your [SERVER B] might be lacking space.

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question