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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a SELECT INTO statement writing to a database which is in SIMPLE recovery mode, and is not tempdb. Will this use tempdb at all?

The SQL is something like


SELECT * INTO DestinationTable FROM SRCDB.dbo.SourceTable

Will this use tempdb at all? We are running into contention issues.

share|improve this question
How have you determined that the contention is in tempdb? – Aaron Bertrand Nov 13 '12 at 19:51

First, run your query and make a note of your SESSION_ID.

Then, run these two queries to see how much TempDB space your original query is using. Be sure to update them with your SESSION_ID.

-- TempDB session usage
select * 
from sys.dm_db_session_space_usage
where session_id = 123

-- TempDB task usage
select *
from sys.dm_db_task_space_usage t
where session_id = 123

Also, run this query to show us the layout of your TempDB. Please post the results.

use tempdb
exec sp_helpfile
share|improve this answer

I suppose it could since it will use tempdb sometimes for internal objects to hold intermediate results.

I would suggest that you go to Books online and search for tempdb and then read the section on troubleshooting. It has some suggestions and queries for how to find out what is causing your tempdb problem.

share|improve this answer

Tembdb is used to store temporary objects (cursors, tables, etc.) and as a work area to spool and sort records in certain situations.

I would have expected there to be no tempdb usage for the simple query in your example (unless the source table is actually a view).

Can you elaborate on the "contention issues" you're experiencing?

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.