Tempdb Full - Grown overnight

Question

Post reply

Tempdb Full - Grown overnight

Shazme

Ten Centuries

Points: 1292
More actions
February 7, 2011 at 5:21 am

#246378

Hi Masters
I am getting headache with this issue ; I have a job which ran over night and in the process it runs the procedure below. Last night it failed with the message "Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. I have tried all different options to bcp and otherways but still unable to get over. Any help will be much appericiated. TempDB fills up all my disk space but that was not the case till yesterday.
Script ran 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'
Thanks much

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

When tempdb runs out of disk space, you have to discover what filled the disk:

1) Tempdb itself. It could have been filled by temporary tables or internal objects.

2) Some other database. You get the message on tempdb, but the disk could have run out of space beacause of another database.

3) Some other process (other from SQL Server). Quite unlikely if your server stores SQL Server data only. I would check anyway.

If tempdb grows enormously and you're not using temporary tables, some query is probably allocating internal objects for hashes, spools and the like. If this is the case, you can use this query to identify the offending query (while it is running).

http://www.sqlservercentral.com/scripts/tempdb/72007/

Hope this helps.

Gianluca

-- Gianluca Sartori

Shazme Ten Centuries Points: 1292 More actions · Answer 2

Thanks Gianluca

Ran your query along with the script and

its coming back as

Internal object MB Space=43374.125000

Internal Object dealloc MB Space=0.437500

Any advice.

Thanks Again

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Ok, so looks like you filled your tempdb with internal objects.

Can you post the execution plan? It should be included in the query I suggested.

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 4

wouldn't this occur if the indexes were being rebuilt with the option SORT_IN_TEMPDB?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Lowell (2/7/2011)
wouldn't this occur if the indexes were being rebuilt with the option SORT_IN_TEMPDB?

Then you would see 'ALTER INDEX ...' as command in the tempdb usage query.

-- Gianluca Sartori

Shazme Ten Centuries Points: 1292 More actions · Answer 6

Thanks Lowell

but I am doing it as below

WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [INDEXES]

ImI

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

imlive (2/7/2011)
Hi Masters
I am getting headache with this issue ; I have a job which ran over night and in the process it runs the procedure below. Last night it failed with the message "Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. I have tried all different options to bcp and otherways but still unable to get over. Any help will be much appericiated. TempDB fills up all my disk space but that was not the case till yesterday.
Script ran 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'
Thanks much

Where is the rest of the code? All I see here is something to build the select-list. What we need to see ins the FROM/ON/ and WHERE clauses.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shazme Ten Centuries Points: 1292 More actions · Answer 8

Thanks for you advice.

It's resolved now. I have to re-ran the process. It was the indexes which were causing the problem so I have to drop the indexes and resolved the issue .