Bulk Operations

Question

Post reply

Bulk Operations

sri2015

SSCrazy

Points: 2780
More actions
February 29, 2016 at 7:38 am

#399785

I have few questions on Bulk Operations
1) how the SQL Server detects Bulk Operations ?
2) I did put SQL Server database in Bulk Logged recovery model and started to insert 1000 rows
after insert 500 rows Server got rebooted because of some Hard issue.
i) After the server is backup to online, is the database went to restoring mode ?
ii) is the transaction roll backed after the server is back to online ?
ii) do we need rerun the same transaction after server is back to online ?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

fatfingered an enter key; answer is below

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

sri2015 (2/29/2016)
I have few questions on Bulk Operations
1) how the SQL Server detects Bulk Operations ?
2) I did put SQL Server database in Bulk Logged recovery model and started to insert 1000 rows
after insert 500 rows Server got rebooted because of some Hard issue.
i) After the server is backup to online, is the database went to restoring mode ?
ii) is the transaction roll backed after the server is back to online ?
ii) do we need rerun the same transaction after server is back to online ?

are these questions about theoretical actions?

1)Bulk operations explicitly use the BULK methodology, whehter it's an object,like .net's System.Data.SqlClientSqlBulkCopy Class,

or the BULK INSERT operator.

2)SQL is atomic, so your 500 row operation out of 1000 would be rolled back, as an incomplete transaction if the server went down mid insert.

i) it would be in normal ONLINE mode; you can only get in RESTORINg mode if you were to run a resotre with no recovery, so it would be waiting on more logs. that's not happening in your example.

ii) yes, the old transaction would have been rolledback, assuming it didn't finish before the server went down.

iii)since the transaction would be rolled back, you would have to rerun the transaction.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!