March 23, 2011 at 4:15 pm
Hello,
When I try to copy the records from my view(1M+ records) to another table, I am getting
the following error. I don't understand the problem Please Help, Thank you.
[font="Courier New"]
Could not allocate space for object 'dbo.SORT temporary run storage: 140769966227456' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup
[/font]
Following are the two stages I used in the task
Step 1. Query for creating my View
GO
/****** Object: View [dbo].[MultiApp_Tmp] Script Date: 03/23/2011 17:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[MultiApp_Tmp]
AS
SELECT a.PID,a.ID,a.TERM,a.USER_DEF_TEXT1
FROM
(
SELECT
SP.Text4 AS PID,
SP.sno AS ID,
AP.Application_Type AS TERM,
AP.User_Def_Text1 AS USER_DEF_TEXT1
FROM pple_t_Student_Profile SP, pple_t_Application AP
INNER JOIN
GENERAL_RMSApplicationType AT ON (AT.ApplicationType = AP.Application_Type)
INNER JOIN
GENERAL_Term GT ON (AT.termId = GT.termId)
WHERE
GT.startDate > 8/20/2009
AND
SP.Text4 IS NOT NULL
AND
SP.sno IS NOT NULL
)a
Step 2.Query for Copying from view to table
INSERT INTO MultiApp
(PID,ID,TERM,USER_DEF_TEXT1,CHANGE,Signed_Contract)
SELECT DISTINCT PIDM,ID,TERM,USER_DEF_TEXT1,Change,Signed_Contract
FROM MultiApp_Tmp
March 23, 2011 at 4:25 pm
Your tempdb is suiciding, it doesn't have enough room to grow large enough for whatever subtables the plan has decided it needed. My guess is because of the Distinct Sort that's required.
Break up the insert into chunks (say, 100k rows at a time) or get TempDB some more room.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 24, 2011 at 9:45 am
Thanks Craig.
The problem is, in the first step, its doing Cartesian product between resulting records of
3 joins with 'pple_t_Student_Profile' table which is killing tempDB. When I added another join between 'pple_t_Student_Profile' and 'pple_t_Application' , the problem is fixed !:-)
My new query for step 1 now looks like
GO
/****** Object: View [dbo].[MultiApp_Tmp] Script Date: 03/23/2011 17:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[MultiApp_Tmp]
AS
SELECT a.PID,a.ID,a.TERM,a.USER_DEF_TEXT1
FROM
(
SELECT
SP.Text4 AS PID,
SP.sno AS ID,
AP.Application_Type AS TERM,
AP.User_Def_Text1 AS USER_DEF_TEXT1
FROM pple_t_Student_Profile SP
INNER JOIN
pple_t_Application AP ON (AP.tid = SP.ptid)
INNER JOIN
GENERAL_RMSApplicationType AT ON (AT.ApplicationType = AP.Application_Type)
INNER JOIN
GENERAL_Term GT ON (AT.termId = GT.termId)
WHERE
GT.startDate > 8/20/2009
AND
SP.Text4 IS NOT NULL
AND
SP.sno IS NOT NULL
)a
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply