tempdb will not grow

Question

Post reply

tempdb will not grow

marklegosz

SSCrazy

Points: 2816
More actions
June 25, 2010 at 11:35 am

#72844

I have a weird problem on a SQL 2000 instance - tempdb will not grow.
I run the following script to create data:
set nocount on
create table #tmp5(
txt1 char(1000),
txt2 char(1000),
txt3 char(1000),
txt4 char(1000),
txt5 char(1000),
txt6 char(1000),
txt7 char(1000)
)
declare @id bigint
set @id=1
while @id < 100000
begin
insert into #tmp5(txt1,txt2,txt3,txt4,txt5,txt6,txt7) values ('This is the insert text','This is the insert text','This is the insert text','This is the insert text','This is the insert text','This is the insert text','This is the insert text')
set @id= @id + 1
end
--select COUNT(*) from #tmp5
drop table #tmp5
And I get this message:
Msg 1105, Level 17, State 2, Line 21
Could not allocate space for object '#tmp5_______________________________________________________________________________________________________________000000001008' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
However tempdb is set to grow automatically, and the drive it is located on has over 143GB free, which should be more than enough for the above query.
Does anybody have any ideas? This was not a problem until the last server reboot.

Viewing 5 posts - 1 through 4 (of 4 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

Has maxsize been set for tempdb?

---------------------------------------------------------------------

marklegosz SSCrazy Points: 2816 More actions · Answer 2

It appears to not be set. Here is sp_helpdb for the tempdb database:

tempdev 1 f:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf PRIMARY14144 KB Unlimited10%data only

templog 2 f:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf NULL4672 KB Unlimited10%log only

I'm going crazy over this... any other ideas?

marklegosz SSCrazy Points: 2816 More actions · Answer 3

I also tried changing the default size of tempdb, restarting SQL, and it did not take the new size. Also, a restart of the complete server had no impact.

When I set tempdb to a new size, I see the file on the file system change size for a minute or two, then revert back to the original size

Does anybody else have other ideas?

marklegosz SSCrazy Points: 2816 More actions · Answer 4

I was able to address the problem I was having, but not resolve the growth issue.

I added a second physical file to tempdb, and was able to confirm that the system is allowing this file to grow, effectively resolving the issues I was having.

If anybody has any ideas on what caused this, I'd be interested to know!