Bulk insert operation never end

Question

Hi,


I'm trying a bulk insert in Management Studio 2005 like this one:

BULK 
INSERT
 ##tmptblPrices 
FROM
 '\\server\backups\archivosplanos\PC20081104.txt'

WITH
 (FORMATFILE='\\server\Backups\ArchivosPlanos\xml\price.xml'
, FIRSTROW = 2)

It works fine, but sometimes  the operation executes but never end, in Management Studio there is no timeout, no error message, I try stop it and it doesn't work.
We try the operation through the application and a timeout error occurs (because the SQL connection timeout period).

Details: bulkadmin role is checked, the folder paths are valid, permissions are enough. The database is SQL 2005. Amount of data: 3-10Kb text file


Has anyone seen this behavior before? What is it and why?

Any idea is appreciated. Thanks.


Cheers,


Edgar L.

---

eRios

Answer 1

Sounds like the insert process may be blocked and is waiting for a resouce.  Typically a bulk insert requires an exclusive lock, so it can achieve a minimally logged transaction.  Next time you hit the problem, check for db locking, on the global temp table.

---

http://jahaines.blogspot.com/

Answer 2

Hi Adam,

This problem appears in production enviroment of one of our customers; it has been a little difficult to figurate out when and why that happends.


I found helpfull this information, we don't have others things we can check. Thanks a lot for you advice.

Cheers,


Edgar

---

eRios