February 7, 2011 at 3:38 am
Hi ,
I am not expert on this so need some help with it. I am running a sql job overnight which runs the prcedure script below but this morning its came up with this error . What can I do t get over this . my query does a select into statement and that's where is the problem m but I don't know how can I refactor it.
Something as below
SET @fields1 = ''
SELECT @fields1 = @fields1 + 'CAST((CASE WHEN SUM(CAST(['+COLUMN_NAME+'] AS INT))>0 THEN 1 ELSE 0 END) AS TINYINT) AS ['+COLUMN_NAME+'],'
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'EventCodes'
AND COLUMN_NAME NOT IN ('UNIQUE_ID', 'DataSource_ID', 'CH', 'CodeType', 'Code', 'CodeValue', 'CleanValue', 'MeasureDate', 'Gcenie_Status', 'Gcenie_Time', 'SPID', 'EvalValue')
ORDER BY COLUMN_NAME
set @fields1 = SUBSTRING(@fields1,1,LEN(@fields1)-1)
-- new code - did work except for some shared DB sites
set @mysql = 'SELECT DataSource_ID, CH , ' + @fields1 + '
INTO EventCodesCriteria
FROM EventCodes
GROUP BY DataSource_ID, CH'
Any help advice will be much apperciated. When it rans my temptb grow madly and then get the error message above.
February 7, 2011 at 6:26 am
Are you shrinking the tempdb over & over? Please don't do that. It hurts the system in lots of ways.
If whatever you're processing needs to to use that much tempdb, you either have to provide it with the amount of tempdb space it needs, or look at refactoring it. Nothing jumped out at me that would cause tempdb to go so high, so it must be something underlying the data. How many indexes are there on the tables being modified? How much data is being affected by the GROUP BY? These types of things use tempdb.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 7, 2011 at 6:41 am
Don't cross-post, please.
http://www.sqlservercentral.com/Forums/Topic1059463-360-1.aspx
-- Gianluca Sartori
February 7, 2011 at 6:47 am
Are you sure that the problem with your tempdb was caused by the code that you posted? Don’t forget that a different code can use most of the tempdb’s space and actually cause you problem. Can you explain how you’ve decided that this code causes the problems?
Adi
edit: sorry didn't notice the double post
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 7, 2011 at 8:03 am
1) It Drops the Table every time -the job runs and there is only one no clustered index on the table created.No I am not shrinking it again and again. Only did once by restarting the serive this morning.
2) It stopped on there - I checked the logs and its this script ; I ran it manullay (only this script and still facing the same issue).
Thanks for your help agian.
February 7, 2011 at 8:13 am
Try running the script I suggested while your sql is running. It should display the tempdb usage.
If the tempdb usage is high, you will have to inspect the query plan to detect which operator is the culprit.
Can you post the actual execution plan?
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply