tame tempdb when doing ETL - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

tame tempdb when doing ETL

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-02-04 : 18:24:01

Greetings

When doing ETL dealing with pretty large xml files I get dbo.Large Object Storage System object: 42250786717696 in database 'tempdb' because the 'PRIMARY' filegroup is full.

I can definitely see the tempdb bloating every few minutes.

So what I am doing is ingesting some xml files to my staging tables. Is it possible to clean up tempdb once one file is uploaded and then proceed to next file?

Thanks

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-02-04 : 19:08:47

the problem is not anything with tempdb. it is the way I am BULK INSERTing

DECLARE @xml_table TABLE(xml_data xml)



DECLARE @xmlDoc NVARCHAR(MAX), @handle INT, @sqlstmt NVARCHAR(MAX)

SET @sqlstmt= 'SELECT * FROM OPENROWSET ( BULK ''' + @xml_path + @xml_file_name + ''', SINGLE_CLOB ) AS XMLDATA'

INSERT INTO dbo.xml_table EXEC (@sqlstmt)

I need to do it in batches instead of one swoop

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources