Database gets locked while copying tables from another Server

Question

Database gets locked while copying tables from another Server

leo_dec

Old Hand

Points: 343
More actions
April 27, 2021 at 3:13 pm

#3876354

Hello
Need to know why SQL server database gets locked when tables are getting copied from another SQL server.
script used to copy the table from another SQL server given below:-
select * into TableName from [linkedServerName].[DatabaseName].[SchemaName].TableName
neither the database expands by any user using management studio nor other application writes the new table in that database.
Is there anything can be done so that database should not get locked for new tables writing.
regards

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

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

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 1

What is likely happening is sys.tables is getting a lock due to the SELECT * INTO TableName which would create the table AND do a select in a single transaction (this is a guess on my part; I've not actually tested this, but it makes sense in my mind).

What I would recommend is rather than "SELECT * INTO" would be to CREATE the table first and then do an insert into it. This way you can also build up proper indexes on the table before you do your insert and you will have better use of the table after creation.

Alternately, if you have a network congestion on the link between the main server and the linked server, and that data copy COULD be using up 100% of the network bandwidth. In this case, if you have no spare bandwidth to hand out to other queries, everything else will need to wait for the bandwidth to return to normal values.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 2

Yeah, the SELECT ... INTO holds certain internal locks on system tables.

Instead, create the table, the clus index (only), and only then load the data into it, like this:

SELECT TOP (0) * 
INTO dbo.TableName 
FROM [linkedServerName].[DatabaseName].[SchemaName].TableName

/* CREATE UNIQUE CLUSTERD INDEX ... ON dbo.TableName */

INSERT INTO dbo.TableName WITH (TABLOCK)
SELECT *
FROM [linkedServerName].[DatabaseName].[SchemaName].TableName

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

Agreed... Scott hit the nail on the head on this one, for sure. I've run into the identical problem several times and ended up doing just like Scott posted.

--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)