INSERT/SELECT Adds then Removes Rows

Question

INSERT/SELECT Adds then Removes Rows

ncforums

SSC Rookie

Points: 45
More actions
November 14, 2017 at 10:19 am

#402671

First, my code is on a separated network so I cannot copy it to here.
I've been running this same Perl code to copy a subset of 3 large tables from a production DB to a development DB for **two years and it has always worked**. I use the same routine, passing the table name to the routine:
CopyTableData("tableA");
CopyTableData("tableB");
CopyTableData("tableC");
Rows copied are: A:17M; B:33M C:52M
Since the data changes on production the code does this:
   TRUNCATE tableX
   SET IDENTITY ON
   INSERT INTO dev..TableX(col1, col2)
   SELECT col1, col2
   FROM prod..TableX
   WHERE keyValue > 201715
All 3 tables are copied with one call to the perl script. And it has worked every time for two years (about 48 times).
Yesterday, TableA and TableB copied fine. TableC started. I could see the number of rows increasing in my table list/row counts on a web page. Then, at about 35M rows, all the rows started slowly disappearing, all the way back to zero. I tried a few times with the same result. Tried again today and got to 45M rows and then the rows started disappearing. Then I tried a smaller set to TableC and it worked, so I went for the whole hog and it copied 40M rows then started rolling them back. There is no rollback or commit in my code -- I've not had to use them
My DBE say that I should be dropping the tables and indexes and rebuilding them. I agree, but I don't own the tables, so not an option.
Any thoughts on fixing this?

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

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

Pretty good bet that you may have run out of log space. Check what recovery mode the database has because if these inserts are fully logged, there's a ton of data that goes into the transaction logs, and there probably isn't enough disk space to handle that, and thus the query fails, and SQL Server automatically does a ROLLBACK of the INSERT. You may not have any error-trapping in your Perl code that would detect an error and do something about it. However, that "do something" could only be to give you an error message. You can't stop the automatic rollback. You'll either h"ave to use some kind of minimally logged bulk method, or change the recovery mode. Do a Google search on "minimally logged inserts SQL Server".

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ncforums SSC Rookie Points: 45 More actions · Answer 2

That has to be it. I added a COMMIT and am going to take multiple subsets of the set of rows I need.
Thanks for the explanation

Phil Parkin SSC Guru Points: 246717 More actions · Answer 3

ncforums - Tuesday, November 14, 2017 10:19 AM
First, my code is on a separated network so I cannot copy it to here.
I've been running this same Perl code to copy a subset of 3 large tables from a production DB to a development DB for **two years and it has always worked**. I use the same routine, passing the table name to the routine:
CopyTableData("tableA");
CopyTableData("tableB");
CopyTableData("tableC");
Rows copied are: A:17M; B:33M C:52M
Since the data changes on production the code does this:
   TRUNCATE tableX
   SET IDENTITY ON
   INSERT INTO dev..TableX(col1, col2)
   SELECT col1, col2
   FROM prod..TableX
   WHERE keyValue > 201715
All 3 tables are copied with one call to the perl script. And it has worked every time for two years (about 48 times).
Yesterday, TableA and TableB copied fine. TableC started. I could see the number of rows increasing in my table list/row counts on a web page. Then, at about 35M rows, all the rows started slowly disappearing, all the way back to zero. I tried a few times with the same result. Tried again today and got to 45M rows and then the rows started disappearing. Then I tried a smaller set to TableC and it worked, so I went for the whole hog and it copied 40M rows then started rolling them back. There is no rollback or commit in my code -- I've not had to use them
My DBE say that I should be dropping the tables and indexes and rebuilding them. I agree, but I don't own the tables, so not an option.
Any thoughts on fixing this?

Firstly, SET IDENTITY ON is not a valid T-SQL command.
Your DBA is wrong (if that's what a DBE is). Truncate/Populate is fine, though you could try dropping the indexes and recreating them at the end of the load, as that may speed things up. But it will not resolve this problem.
I would suggest checking overall server health, including disk space usage. Perhaps you are running out of log file space, for example, and the INSERT gets rolled back as a result.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.