Insert query from Link server locking whole database

Question

Post reply

Insert query from Link server locking whole database

Ketan-358671

SSC Veteran

Points: 220
More actions
March 29, 2011 at 1:27 pm

#237557

We are pulling data from remote server and doing insert to sql server table.
through linkserver but during insert whole datbase locked and we are not able to access daatbase....
Please advice

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you post the code?

How do you know the whole database is locked? What is the output of sys.dm_tran_locks? What are the symptoms?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

bvsc Ten Centuries Points: 1258 More actions · Answer 2

how you are pulling data from linked server & inserting in the current server's database?

Can you explain little bit clearly, better post the query you using as jack requested.

UMG Developer SSChampion Points: 13482 More actions · Answer 3

My guess is that you are using the SELECT .. INTO method, and that will keep a schema lock on the DB that prevents a lot of things but not everything. For example, you would still be able to open a connection and select from a different table. (People usually think that the database is completely locked because you can't browse the object tree in SSMS while the SELECT .. INTO statement is running.)

This isn't unique to linked servers, but your linked server query probably takes longer to run, or you don't use SELECT .. INTO as much on local objects.

There is essentially one way to resolve this: create the table first and then use a INSERT INTO .. SELECT instead. (With SQL Server 2008 this is usually still a minimally logged activity just like SELECT .. INTO.)

You can create the table in two different ways:

Write the actual CREATE TABLE statement for example:

CREATE TABLE dbo.TestTable (ID INT NOT NULL PRIMARY KEY, Name VARCHAR(10) NULL);

Or the lazy way: (Which also has the benefit of not having to update the code if the table changes.)

SELECT * INTO dbo.TestTable FROM LinkedServer.DB.dbo.SourceTable WHERE 1 = 2;

or

SELECT TOP 0 * INTO dbo.TestTable FROM LinkedServer.DB.dbo.SourceTable;

Both of them accomplish the exact same thing by creating a CONSTANT SCAN query plan, so it just depends if you like the TOP 0 method or the WHERE 1 = 2 method.

Note: this method will still take a schema lock on the DB, but it will be really short since it only needs it long enough to create the empty table.

Then you can just do your insert:

INSERT INTO dbo.TestTable SELECT * FROM LinkedServer.DB.dbo.SourceTable;

Of course using SELECT * is usually a bad practice, so actually list only the columns you need in the select list if you can.

Ketan-358671 SSC Veteran Points: 220 More actions · Answer 4

Ketan-358671

SSC Veteran

Points: 220

March 31, 2011 at 3:55 pm

#1306234

Thanks for your advice

Ketan-358671 SSC Veteran Points: 220 More actions · Answer 5

UMG Developer,

I still have one question that if i run this same query

"INSERT * INTo tablename" on local server

database is accessible so no database locking happen.

UMG Developer SSChampion Points: 13482 More actions · Answer 6

Ketan,

Using the "INSERT INTO tablename SELECT ..." method will put locks on the table you are inserting into, but it shouldn't lock the entire database like the "SELECT ... INTO" method does.