Options to mitigate SQL Server timeout on giant batch insert

Question

I wrote a script that works swimmingly in my dev environment:

INSERT INTO [Voyage] ([StartDate], [EndDate] ...)
SELECT ([StartDate], [EndDate] ...) FROM [LEG]
WHERE [STARDATE] IS NOT NULL
AND [ENDDATE] IS NOT NULL

All well and good, until this script is run in production. I'm told we have a billion Leg records, and the server is timing out because of it.

Anyone have a strategy to employ for a scenario like this?

Any chance of breaking up the batch? For example run a few months at a time. With a billion records, I'm surprised your DBA's haven't mentioned anything about log growth and blocking. — Brad D, Dec 12 '14 at 20:40

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

Are you running the script in SQL Management Studio? You should be able to just let it run. A different query client could have a timeout issue. See this answer to verify the timeout is set to 0 (no timeout):

Changing the CommandTimeout in SQL Management studio

edited May 23 '17 at 10:33

Community

1
1

answered Dec 12 '14 at 17:11

Rocky

494
6
18

Options to mitigate SQL Server timeout on giant batch insert

1 Answers1