August 16, 2013 at 9:08 am
Hi All
I have 5 temp tables that are inner joined to load 6 million rows into a master temp table.The query that loads 6-7 million rows from these 5 primary temptables into master table is taking more than 2 hrs and throwing an error
Msg 9002, Level 17, State 4, Line 201
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Below are the temp tables and the query used to load data into master temp table.
#noncalc_Data -- 160033 rows
#CHTYAMT_DATA -- 11538 rows
#BADAMT_DATA --73783 rows
#INSADJ_DATA -- 554835 rows
#INSOSB_DATA -- 47871 rows
#OSPATBAL_DATA -- 83934 rows
#PRECALC_DATA -- 972092 Rows
SELECT
#NonCalc_DATA.accountNumber
, #NonCalc_DATA.mrn
, #NonCalc_DATA.guarantorNumber
, recordAccountId
, patientType
, insurance1FinancialClass
, insurance2FinancialClass
, insurance3FinancialClass
, #NonCalc_DATA.currentFinancialClass
, payer
, accountstatus
, hospitalId
, serviceType
, #NonCalc_DATA.guarantorLastName
, #NonCalc_DATA.guarantorFirstName
, #NonCalc_DATA.guarantorMiddleInitial
, #NonCalc_DATA.guarantorAddress1
, #NonCalc_DATA.guarantorAddress2
, #NonCalc_DATA.guarantorCity
, #NonCalc_DATA.guarantorState
, #NonCalc_DATA.guarantorZip
, #NonCalc_DATA.guarantorHomePhone
, #NonCalc_DATA.guarantorSSN
, #NonCalc_DATA.guarantorDOB
, #NonCalc_DATA.guarantorGender
, #NonCalc_DATA.guarantorEmployer
, #NonCalc_DATA.patientLastName
, #NonCalc_DATA.patientFirstName
, #NonCalc_DATA.patientMiddleInitial
, #NonCalc_DATA.patientAddress1
, #NonCalc_DATA.patientAddress2
, #NonCalc_DATA.patientCity
, #NonCalc_DATA.patientState
, #NonCalc_DATA.patientZip
, #NonCalc_DATA.patientHomePhone
, #NonCalc_DATA.patientSSN
, #NonCalc_DATA.patientDOB
, #NonCalc_DATA.patientGender
, #NonCalc_DATA.patientEmployer
, #NonCalc_DATA.admitDate
, #NonCalc_DATA.dischargeDate
, #NonCalc_DATA.arPostingDate
, #NonCalc_DATA.lastBillingDate
, #NonCalc_DATA.lastPaymentDate
, #NonCalc_DATA.selfPayDate
, #NonCalc_DATA.closingDate
, #NonCalc_DATA.returnMailFlag
, #PRECALC_DATA.totalCharges_48
, #PRECALC_DATA.accountBalance_49
, #PRECALC_DATA.totalPayments_50
, #PRECALC_DATA.TotalAjustments_51
, #CHTYAMT_DATA.charityAmount -- 52
, #PRECALC_DATA.InitialPatientResponsibility_53
, #OSPATBAL_DATA.outstandingPatientBalance
, #PRECALC_DATA.patientPayments_55
, patientAdjustments_56
, #BADAMT_DATA.badDebtAmount --57
, #PRECALC_DATA.insurancePayments_58
, #NonCalc_DATA.insurance1Payments --59
, #NonCalc_DATA.insurance2Payments -- 60
, #NonCalc_DATA.insurance3Payments -- 61
, #INSADJ_DATA.insuranceAdjustments --62
, #INSOSB_DATA.insuranceOutstandingBalance --63
, insurance1OutstandingBalance
, insurance2OutstandingBalance
, insurance3OutstandingBalance
, hisUserId
, agencyCode
, earlyOutPlcmtDate
, badDebtPlcmtDate
, collectorId
, billingType
, adminHold
, balanceVisible
, billingIndicator
, fileType
, firstStatementDate
, lastStatementDate
, ins1PlanCode
, ins2PlanCode
, ins3PlanCode
INTO #MASTER_DATA
FROM
#NonCalc_DATA INNER JOIN #PRECALC_DATAON
#NonCalc_DATA.accountNumber = #PRECALC_DATA.ACCOUNT
INNER JOIN #CHTYAMT_DATAON
#PRECALC_DATA.ACCOUNT = #CHTYAMT_DATA.ACCOUNT
INNER JOIN #BADAMT_DATAON
#CHTYAMT_DATA.ACCOUNT = #BADAMT_DATA.ACCOUNT
INNER JOIN #INSADJ_DATAON
#BADAMT_DATA.ACCOUNT = #INSADJ_DATA.ACCOUNT
INNER JOIN #INSOSB_DATAON
#INSADJ_DATA.ACCOUNT = #INSOSB_DATA.ACCOUNT
INNER JOIN #OSPATBAL_DATAON
#INSOSB_DATA.ACCOUNT = #OSPATBAL_DATA.ACCOUNT
the temp tabels do not have any indexes but I tried adding and it didnt help.Does it help if I use table variable or how can I resolve this Issue.
August 16, 2013 at 4:22 pm
If you get this message, this indicates that you are running out of space on the disk where tempdb is located. How much free space do you have on that disk? Can you post the output from "sp_helpdb tempdb" (I need the second result set).
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 17, 2013 at 11:02 am
There is still space in tempdb drive.
Out of 209Gb , 130Gb is still available.
But still we are getting below message.
Msg 1105, Level 17, State 2, Line 200
Could not allocate space for object 'dbo.SORT temporary run storage: 142034254233600' 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.
Msg 9002, Level 17, State 4, Line 200
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
sp_helpdb results:
namefileidfilenamefilegroupsizemaxsizegrowthusage
tempdev1N:\tempdb.mdfPRIMARY146265152 KBUnlimited10%data only
templog2N:\templog.ldfNULL3241152 KBUnlimited10%log only
namedb_sizeownerdbidcreatedstatuscompatibility_level
tempdb 146002.25 MBsa2Jul 31 2013Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics100
I am not sure why this error message is coming even tough tempdb drive has free space.
August 17, 2013 at 12:02 pm
sdennis (8/17/2013)
There is still space in tempdb drive.Out of 209Gb , 130Gb is still available.
...
sp_helpdb results:
namefileidfilenamefilegroupsizemaxsizegrowthusage
tempdev1N:\tempdb.mdfPRIMARY146265152 KBUnlimited10%data only
templog2N:\templog.ldfNULL3241152 KBUnlimited10%log only
The numbers does not match up. 146 + 32 = 178. If the N disk is 209 GB in size, there is roughly 31 GB left for other things. To grow the primary file with 10% you need 14 GB of free disk space.
You can use xp_fixeddrives to review the free space on your disks.
But maybe we should go back to your query. You join seven tables over the ACCOUNT column. This only makes sense if ACCOUNT is the primary key in at least six of them. Else you will produce a lot of duplicate rows. And given the size of your tempdb, I suspect that this is what is happening.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 10:48 am
i think add 2-3 more files to tempdb and then run the query.
August 19, 2013 at 12:45 pm
Is the TempDB set to be able to grow the files?
There is an exception to every rule, except this one...
August 20, 2013 at 9:23 am
Are you doing all this in one transaction? If you break it up into multiple transactions and issue a manual CHECKPOINT in tempDB between transactions you will be able to better control transaction log growth.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply