Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Amit-1234
Starting Member
19 Posts |
 Posted - 2015-04-15 : 02:59:42
|
| Hi,We are calling a web service using script component in SSIS. This script is accessing around 2,84,000 rows but processing them one by one in small chunks, calling a web service by passing the row values as parameter and returning the output buffer to the final OLEDB destination.The web service returns valid address information in the form of xml using soap protocol. The xml returned is not storedas a file but it is used to access the values of inner elements. These values are finally inserted into the SQL server table.However, on each execution of the package the TEMPDB database is becoming full and the data insertion is failing, but the TEMPDB size allocated is 5GB. My question is why mere insertion of around 0.2 million records is causing the TEMPDB to become full. Does this have anything to do with the webservice call or the XML returned?In case it does how can we clean up the memory programatically?Thanks in advance |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-15 : 09:35:07
|
| Are you using OLEDB connector for your target table? be sure to choose the FastLoad option and change the Maximum insert commit size to something smaller - eg 1000 or 10000 rows. Also check the recovery model of your database. Is it Full? if so, is the database used for OLTP? If NOT for OLTP, consider changing the recovery model to Simple. |
 |
|
|
Amit-1234
Starting Member
19 Posts |
Posted - 2015-04-16 : 05:59:11
|
| Thanks for your response. Yes, we are using oldedb connector and the recovery model for tempdb as well as for my target database is set to simple.I will definitely try out the fast load option suggested by you and let you know. Thanks once again ... :) |
|
|
|
|
|
|
SQL SERVER TEMPDB FULL ISUE