Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have someone who retrieves data from a linked server to a new table as following:

* newly edited - turns out there's multiple queries executed *

select * into dbDestination.dbo.tableDestination
from server2.dbSource.dbo.tableSource

select * into dbDestination.dbo.tableDestination2
from server2.dbSource.dbo.tableSoure2

select * from dbDestination.dbo.tableDestination3
from server2.dbSource.dbo.tableSource3

(don't ask me why - I just want to know why the effects are as they are)

Sizes:

tableSource - 24million rows (3,5GB data)

tableSource2 - 26million rows (8GB data)

tableSource3 - 1million rows (300MB data)

This query seems to effectively make my tempdb on "server2" to grow out of control and results in the disk being full. Like it is dumping 50GB data in there!?

What gives?

These statements are NOT seperated by a "GO" command. Does that make it run simultaneously? Or do the second and third statements wait until the first one is complete? I was thinking maybe tempdb can not send so much across the wire at once - resulting in tempdb spill? But why would it spill to tempdb at all? I have no sorts or joins or anything.

Looking at my execution plan I see nothing special: SELECT INTO (0%) <- Table Insert (36%) <- Top (0%) <- Compute Scalar (0%) <- Remote Query (64%)

What happens within this "remote query" ? Is it spilling so much data to the tempdb? Anyone who could explain why this is happening? It seems weird. I would have expected maybe tempdb to be affected on the server where the request is being issued, but not on the remote.

Thanks in advance for any help to solve this mystery.

share|improve this question

closed as unclear what you're asking by Paul White, Max Vernon, Mark Storey-Smith, Mike Walsh, RolandoMySQLDBA Nov 4 '13 at 18:57

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
You're not filtering at all, just a straight copy of all the data from server2 to server1? –  billinkc Oct 31 '13 at 16:13
    
You need to look at what's going on on the remote side. What you're looking at on the client side isn't useful for troubleshooting in this case. –  Jon Seigel Oct 31 '13 at 19:50
    
Please edit your question to include the remote query text (copy from the Remote Query node properties), and the configuration of the linked server. –  Paul White Nov 1 '13 at 3:58
    
I have edited my original post with more information about the script being run. Indeed it is a straight bulk operation - just copy the data from one place to a new table. No sorts. No joins. I looked at the Remote Query text but it only shows the usual "select column1, column2 from table" (but with more columns...). Nothing else. I'm confused as to why tempdb is involved in this at all. The linked server is setup with SQLNCLI10 and to use the logins current security context. Nothing special. –  mssqladm Nov 1 '13 at 9:27
    
The answer is in the details. If you don't provide them, you can't expect good answers. Incidentally, your third script item is missing an INTO clause. –  Paul White Nov 3 '13 at 5:19