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 am trying to do a bulk update of a column from a csv file, however when I do the actual update operation my tempdb balloons to over 20 GB in size (overtaking the entire size of the SSD the tempdb is running on).

I load the csv file using a bulk copy in to a temporary table that has matching schema to the destination table, I then do the query

Update [Med_Rec_Entries] 
set [Comments] = [Tmp_Med_Rec_Entries].[Comments] 
from [Tmp_Med_Rec_Entries] 
where [Tmp_Med_Rec_Entries].[ID] = [Med_Rec_Entries].[ID]

And that is where temp db blows up in size. Both the source and destination tables have 1,770,373 rows in it (there is a 1=1 on ID relationship for the update).

I tried running the database tuning advisor and it had me make a new index, but it did not help. The database I am inserting in to is just the middle step in a data migration and no queries other than other csv imports like one I am doing here and a bulk export at the end will be run against this database so it does not have any indexes or keys itself.

Here is the create table statement for Med_Rec_Entries

CREATE TABLE [dbo].[Med_Rec_Entries](
    [ID] [nvarchar](255) NULL,
    [Patient_ID] [nvarchar](80) NULL,
    [Med_Rec_Code] [nvarchar](20) NULL,
    [Tx_Date_Performed] [nvarchar](254) NULL,
    [Comments] [nvarchar](max) NULL,
    [Staff_Code] [nvarchar](21) NULL,
    [Quantity] [real] NULL,
    [Hide_This_Entry] [bit] NULL,
    [Exclude_From_Printed_History] [bit] NULL,
    [Image_ID] [int] NULL,
    [Remote_Status] [nvarchar](2) NULL,
    [Special_Flag] [nvarchar](20) NULL,
    [Tx_Time_Performed] [nvarchar](254) NULL,
    [Tx_Date_Recorded] [nvarchar](254) NULL,
    [Appended_Comments] [nvarchar](max) NULL,
    [Tx_Time_Recorded] [nvarchar](254) NULL,
    [Entry_Is_Locked] [bit] NULL,
    [Create_User] [nvarchar](21) NULL,
    [Audit_DTS] [nvarchar](20) NULL,
    [Audit_Actor] [nvarchar](21) NULL,
    [vQPixAreax_] [varbinary](max) NULL,
    [Route] [nvarchar](80) NULL,
    [Units] [nvarchar](20) NULL,
    [CFR_Reason_Code] [nvarchar](40) NULL,
    [rpl_Key] [nvarchar](14) NULL,
    [rpl_DateTime] [nvarchar](14) NULL,
    [Medical_Description] [nvarchar](max) NULL,
    [caseID] [int] NULL,
    [Sign_Off_Target] [nvarchar](21) NULL,
    [Sign_Off_Date] [nvarchar](254) NULL,
    [Location_Code] [nvarchar](50) NULL,
    [ACUP_ID] [nvarchar](15) NULL,
    [ExcludeFromWeb] [bit] NULL,
    [SecondarySort] [int] NULL,
    [AutosaveState] [nvarchar](20) NULL,
    [_CommentsAndFindingsText] [nvarchar](max) NULL,
    [NewFormat] [bit] NULL,
    [Temp] [nvarchar](80) NULL,
    [Colour] [nvarchar](20) NULL,
    [PrimaryKey] [nvarchar](200) NULL,
    [Sign_Off_Time] [nvarchar](254) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--From the database engine tuning advisor
CREATE NONCLUSTERED INDEX [_dta_index_Med_Rec_Entries_13_933578364__K1] ON [dbo].[Med_Rec_Entries]
(
    [ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Here is the create table statement for Tmp_Med_Rec_Entries

CREATE TABLE [dbo].[Tmp_Med_Rec_Entries](
    [ID] [nvarchar](255) NULL,
    [Comments] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

What can I do to stop tempdb from growing too large?

share|improve this question
    
There is much more to dig in before tempdb is a problem - how many tempdb files are there ? Is tempdb on its own dedicated physical disk ? What is the autogrowth setting ? Are you using Trace Flag 610 : Minimally logged DML operations into Indexed tables, transaction log and allow bulk loading and Trace flag 1118 : Directs SQL Server to allocate full extents to each tempDB objects ? –  Kin Sep 3 '13 at 21:38
    
Yes temp db is on it's own disk, but it is a small SSD drive and space is getting used up. I do not know how to check the trace flags. –  Scott Chamberlain Sep 3 '13 at 21:47
    
DBCC TRACESTATUS(-1) to check the status of what flags are enabled and DBCC TraceON(Flag Number) – at session level DBCC TraceON(Flag Number, -1) -- Globally to turn on the trace flags. ALso how any tempdb files are there ? –  Kin Sep 3 '13 at 21:52
    
Only 1 mdf and 1 log with 10 MB auto-growth, also both trace flags are 0. –  Scott Chamberlain Sep 3 '13 at 21:54
1  
@Kin that is not going to resolve the issue. Changing the size or number of files for TempDB will not change the required size for the import to proceed. First thing I would do is put TempDB on a much larger disk, or failing that I would run the import in small batches, thereby removing the need for large tempdb space - problem solved. –  Max Vernon Sep 3 '13 at 22:16
show 5 more comments

1 Answer

up vote 2 down vote accepted

You could run the import in small batches.

TempDB is growing large enough to accommodate running various queries necessary for the import routine. Running smaller imports would lessen the amount of tempdb required during each run.

share|improve this answer
    
I ended up just running in smaller batches to solve the issue, but If you or anyone else would like to answer why this happened and how to avoid it in the future I would appreciate it. (If no one does in a few days I will just accept your answer) –  Scott Chamberlain Sep 3 '13 at 23:31
    
It happens because SQL Server is designed to use tempdb for various things such as temporary storage of data during processing. If you check the plan for the import statement you can see what is using tempdb. –  Max Vernon Sep 3 '13 at 23:36
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.