Bulk Insert - tracking

Question

Bulk Insert - tracking

Budd

Hall of Fame

Points: 3781
More actions
February 8, 2019 at 2:19 pm

#379292

How can I query for when a bulk insert was started, ended and record count inserted ?
I have searched the web but all I get is information on how to use bulk insert and why it shouldn't be used. I have no say in that, as it is part of an application, I just need to provide the previously stated data.
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Budd - Friday, February 8, 2019 2:19 PM
How can I query for when a bulk insert was started, ended and record count inserted ?
I have searched the web but all I get is information on how to use bulk insert and why it shouldn't be used. I have no say in that, as it is part of an application, I just need to provide the previously stated data.
Thanks.

It's pretty much like anything else you can do in a quey... Set a variable for the start date/time before you call it. Immediately after the call, select from @@ROWCOUNT to get the rows affected and subtract the start date/time from the current date time to get the duration.

Would love to see some of the articles you found why BULK INSERT shouldn't be used. Got any links in particular?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Jeff Moden - Friday, February 8, 2019 2:37 PM
It's pretty much like anything else you can do in a quey... Set a variable for the start date/time before you call it. Immediately after the call, select from @@ROWCOUNT to get the rows affected and subtract the start date/time from the current date time to get the duration.
Would love to see some of the articles you found why BULK INSERT shouldn't be used. Got any links in particular?

I was curious about articles saying why bulk insert shouldn't be used. I found one related to Entity Framework but it's EF with all of their issues so I ignored it.
Otherwise the ones I found were related to security risks. I'd love to see any others. Here is one typical of some of the posts and concerns about security:
Why is BULK INSERT Considered Dangerous?

That's the gist of it from what I could find. And standing up any database server is a security risk. So we need to stop doing that.

Sue

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Sue_H - Friday, February 8, 2019 3:01 PM
Jeff Moden - Friday, February 8, 2019 2:37 PM
It's pretty much like anything else you can do in a quey... Set a variable for the start date/time before you call it. Immediately after the call, select from @@ROWCOUNT to get the rows affected and subtract the start date/time from the current date time to get the duration.
Would love to see some of the articles you found why BULK INSERT shouldn't be used. Got any links in particular?
I was curious about articles saying why bulk insert shouldn't be used. I found one related to Entity Framework but it's EF with all of their issues so I ignored it.
Otherwise the ones I found were related to security risks. I'd love to see any others. Here is one typical of some of the posts and concerns about security:
Why is BULK INSERT Considered Dangerous?
That's the gist of it from what I could find. And standing up any database server is a security risk. So we need to stop doing that.
Sue

Thanks, Sue. It's amazing with what people on threads like that come up with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Budd Hall of Fame Points: 3781 More actions · Answer 4

Jeff Moden - Friday, February 8, 2019 2:37 PM
Budd - Friday, February 8, 2019 2:19 PM
How can I query for when a bulk insert was started, ended and record count inserted ?
I have searched the web but all I get is information on how to use bulk insert and why it shouldn't be used. I have no say in that, as it is part of an application, I just need to provide the previously stated data.
Thanks.
It's pretty much like anything else you can do in a quey... Set a variable for the start date/time before you call it. Immediately after the call, select from @@ROWCOUNT to get the rows affected and subtract the start date/time from the current date time to get the duration.
Would love to see some of the articles you found why BULK INSERT shouldn't be used. Got any links in particular?

I didn't save any links about why bulk insert should not be used, and my statement was too general. Most of what I was referring to in my initial post had to do with security permissions required.
As for your suggestion of capturing @@ROWCOUNT; I'm not sure how I can do that, as I said the BULK INSERT is call from within the application code. Can you explain, please ?

frederico_fonseca SSCoach Points: 16155 More actions · Answer 5

Budd - Monday, February 11, 2019 6:10 AM
Jeff Moden - Friday, February 8, 2019 2:37 PM
Budd - Friday, February 8, 2019 2:19 PM
How can I query for when a bulk insert was started, ended and record count inserted ?
I have searched the web but all I get is information on how to use bulk insert and why it shouldn't be used. I have no say in that, as it is part of an application, I just need to provide the previously stated data.
Thanks.
It's pretty much like anything else you can do in a quey... Set a variable for the start date/time before you call it. Immediately after the call, select from @@ROWCOUNT to get the rows affected and subtract the start date/time from the current date time to get the duration.
Would love to see some of the articles you found why BULK INSERT shouldn't be used. Got any links in particular?
I didn't save any links about why bulk insert should not be used, and my statement was too general. Most of what I was referring to in my initial post had to do with security permissions required.
As for your suggestion of capturing @@ROWCOUNT; I'm not sure how I can do that, as I said the BULK INSERT is call from within the application code. Can you explain, please ?

I think that in order to make it absolutely clear that you should tell us exactly how your "bulk insert" is done.
and is it really T-SQL bulk insert statement or bcp, or oledb/ado.net bulk insert object
code sample might be the best, full snippet, not just the bulk insert itself.

Budd Hall of Fame Points: 3781 More actions · Answer 6

I understand that would be the best way to do this, however, I can't do that. Let me try to rephrase my question.
Are there any system tables that capture bulk insert transactions ?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Budd - Monday, February 11, 2019 7:37 AM
I understand that would be the best way to do this, however, I can't do that. Let me try to rephrase my question.
Are there any system tables that capture bulk insert transactions ?

To the best of my knowledge, there's nothing intrinsic to monitor for such things except maybe the default trace, which is unreliable on a busy system because of the quick rollover of the logs. I wouldn't know how to do it but I believe that Extended Events could be set up to monitor for such transactions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Budd Hall of Fame Points: 3781 More actions · Answer 8

Thanks Steve, Extended Events is what I just started looking into.

Sue_H SSC Guru Points: 90860 More actions · Answer 9

Budd - Monday, February 11, 2019 8:02 AM
Thanks Steve, Extended Events is what I just started looking into.

Depending on what you want to track, you may want to check the extended event: database_bulk_insert_rows

Sue

Budd Hall of Fame Points: 3781 More actions · Answer 10

Thank you Sue. I don't see that in SSMS 2017 on SQL 2016Ent.

Sue_H SSC Guru Points: 90860 More actions · Answer 11

Budd - Monday, February 11, 2019 9:32 AM
Thank you Sue. I don't see that in SSMS 2017 on SQL 2016Ent.

Start a new Events session and for the event, just type in bulk and you will see the events for bulk insert.

Sue

Budd Hall of Fame Points: 3781 More actions · Answer 12

That I do see, actually I see it in the Template selection list under new session wizzard.
Thank you.