Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS: Insufficient disk space Expand / Collapse
Author
Message
Posted Monday, August 25, 2014 3:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:20 AM
Points: 102, Visits: 332
Hi All,

I am getting below error while executing SSIS package through job:

[color=#ff0000]There may be error messages posted before this with more information about the failure. End Error Error: 2014-08-22 14:26:11.81 Code: 0x800705AA Source: IMPORTDETAILS Description: End Error Error: 2014-08-22 14:26:11.83 Code: 0xC004704B Source: IMPORTDETAILS Description: The buffer manager cannot write 8 bytes to file "T:\Windows\Temp\DTS{9448AF1C-04C5-4C33-8211-6FE6C9112EA8}.tmp". There was insufficient disk space or quota. End Error Error: 2014-08-22 14:26:12.39 Code: 0x800705AA Source: IMPORTDETAILS Description: End Error Error: 2014-08-22 14:26:12.39 Code: 0xC004704B Source: IMPORTDETAILS Description: The buffer manager cannot write 8 bytes to file "T:\Windows\Temp\DTS{C555DCA5-7131-41C9-844D-6700A5A32367}.tmp". There was insufficient disk space or quota. End Error Error: 2014-08-22 14:26:13.40 Code: 0x800705AA Source: IMPORTDETAILS Description: End Error Error: 2014-08-22 14:26:13.40 Code: 0xC004704B Source: IMPORTDETAILS Description: The buffer manager cannot write 8 bytes to file "T:\Windows\Temp\DTS{DF561118-A55C-4DF1-BF26-B28347E12F66}.tmp". There was insufficient disk space or quota. End Error Error: 2014-08-22 14:26:14.93 Code: 0xC020204B Source: IMPORTDETAILS Lookup 1 [1311] Description: A buffer could not be locked. The system is out of memory or the buffer manager has reached its quota. End Error Error: 2014-08-22 14:26:14.95 Code: 0xC004701A Source: IMPORTDETAILS SSIS.Pipeline Description: component "Lookup 1" (1311) failed the pre-execute phase and returned error code 0xC020204B. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:23:13 PM Finished: 2:27:29 PM Elapsed: 255.748 seconds. The package execution failed. The step failed.[/color]

I am using Lookup table and configured as Full Cache. Can anybody please help me on this . How to resolve the issue.

Thanks,
Arooj
Post #1606986
Posted Monday, August 25, 2014 3:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 2,412, Visits: 6,684
The error is exactly what is says on the tin, the system is out of disk space on the T: drive. To resolve this, either some space has to be freed up on that drive or the lookup changed from Full Cache to either Partial or None.
Post #1606989
Posted Monday, August 25, 2014 3:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:20 AM
Points: 102, Visits: 332
Hi Eirikur

Thanks for the reply. I have plenty of space in T drive almost 200 GB. and I have checked with partial and no cache but there is no use. Still I am getting the same error. I have changed the BLOB setting like BLOBtempstoragepath, while executing its taking C drive space,instead of T drive .
Post #1606992
Posted Monday, August 25, 2014 3:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 2,412, Visits: 6,684
arooj300 (8/25/2014)
Hi Eirikur

Thanks for the reply. I have plenty of space in T drive almost 200 GB. and I have checked with partial and no cache but there is no use. Still I am getting the same error. I have changed the BLOB setting like BLOBtempstoragepath, while executing its taking C drive space,instead of T drive .


This certainly rings a bell, digging into grey cell memory.... Quick questions, how large is the lookup set, what is the file system type (NTFS, FAT, xFAT etc.) on the T: drive, is there any disk quota?
Post #1606997
Posted Monday, August 25, 2014 3:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:20 AM
Points: 102, Visits: 332
T is NTFS system type and data will be 12 M.
Post #1607001
Posted Monday, August 25, 2014 6:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 2,412, Visits: 6,684
arooj300 (8/25/2014)
T is NTFS system type and data will be 12 M.


Just a quick thought, could this be a permission issue? Try creating a folder, give the appropriate permissions on that folder to the SSIS process user and see what happens.
Post #1607027
Posted Tuesday, August 26, 2014 1:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:20 AM
Points: 102, Visits: 332
This is not a permission issue. Because from 1 year job is running, and we did not have any problem. But from two days job has started failing because the records are more.
we have 28 Gb free space in C drive. we job is running, all 28 Gb will be reserverd by the job. After completing the job it will release the reserved space.
My concern is that, we have one source and one destination. Is there any alternative option apart from Lookup transformation, because Lookup will take all the drive space and then start copying the data.
I want something like which will not overloaded in both source and destination server and we can copy data easily through job.

Thanks
Post #1607351
Posted Tuesday, August 26, 2014 1:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
If the lookup has not enough memory, it will flush to disk.
So you might have a memory issue.

How did you configure the lookup? I hope you didn't use the dropdown menu...




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1607353
Posted Tuesday, August 26, 2014 1:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 2,412, Visits: 6,684
arooj300 (8/26/2014)
This is not a permission issue. Because from 1 year job is running, and we did not have any problem. But from two days job has started failing because the records are more.
we have 28 Gb free space in C drive. we job is running, all 28 Gb will be reserverd by the job. After completing the job it will release the reserved space.
My concern is that, we have one source and one destination. Is there any alternative option apart from Lookup transformation, because Lookup will take all the drive space and then start copying the data.
I want something like which will not overloaded in both source and destination server and we can copy data easily through job.

Thanks


Quick question, what has changed around the time the job started failing, hard to believe that there has been such a dramatic increase in the number of records?
Can you test the package on another server/pc to verify that the package is the issue?
Post #1607359
Posted Tuesday, August 26, 2014 10:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 69, Visits: 246
For a similar task , rowcount task to see the records got processed daily. Plus unit test the package for 100 records and monitor the db file growth as the package is executing.
Post #1607519
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse