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

USE TGTDB
GO

SELECT * INTO DestinationTable FROM SRCDB.dbo.SourceTable
GO

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

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

3 Answers

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
add comment

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
add comment

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
go

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

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

use tempdb
go
exec sp_helpfile
go
share|improve this answer
add comment

Your Answer

 
discard

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.