SQL: Not enough space on temporary disk
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.
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.
SQL_SERVER_DBA
Increase size for tempdb, I think this is where you running out.
ASKER
How do I go about doing that?
before I tell you this, is your database in simple or full recovery mode
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.
ASKER
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?
>>. 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?
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?
ASKER
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.
That is correct. Tempdb is always simple.
If you have the disk space, you need to allow it to auto grow.
If you have the disk space, you need to allow it to auto grow.
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.
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.
ASKER
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?
Any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.