TempData base filegroup full error

Question

Post reply

TempData base filegroup full error

raj-247939

SSCertifiable

Points: 5999
More actions
August 24, 2005 at 9:01 pm

#110266

Hi all,

I had scheduled a job and it was running fine from last 6 months and tonight it gave me error .

Error: 1105, Severity: 17, State: 2

Could not allocate space for object '(SYSTEM table id: -669903346)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

I written the code to truncate a the log on every third day.

Then why this error occured.

I studied http://support.microsoft.com/kb/q229726/

but did not undersatnd the real problem.As it says that this problem occurs becasue of primary or foreign key constraints.

but i dont have any object with that name in my tempdb.

Thanx in advance

from

Killer

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

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

Santhosh Kumar Say Hey Kid Points: 686 More actions · Answer 1

Santhosh Kumar

Say Hey Kid

Points: 686

August 26, 2005 at 8:21 am

#585426

I think your job contains some code, which takes lots of space in the tempdb. Is the autogrow option in your tempdb on.

Dylan Peters SSC Eights! Points: 916 More actions · Answer 2

Dylan Peters

SSC Eights!

Points: 916

August 26, 2005 at 8:22 am

#585430

Check the growth properties on tempdb, and the disk that the tempdev file is on. The problem is not (necessarily) with the log file but with the .mdf file.

Dylan Peters
SQL Server DBA

Sassan Karai SSC Veteran Points: 290 More actions · Answer 3

Temp DB is in simple recovery mode - accordingly truncating the log is unnecessary.

The error message you got indicates that the Default file group (Primary) was full - this indicates that tempdev (tempdb.mdf) ran out of room to accomodate more data. You may not have allowed autogrow on this file - or autogrow failed due to a possible SQL I/O stall when trying to autogrow in increments too large for the speed of a disk subsystem.

You may want to run sp_helpdb 'tempdb' in QA and post the result here for us to look at.

raj-247939 SSCertifiable Points: 5999 More actions · Answer 4

Hi,

Below is the result of sp_helpdb.

tempdb 263.31 MB sa 2 Aug 24 2005 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Thai_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 80

tempdev 1 d:\program files\MSSQL\data\tempdb.mdf PRIMARY 1258432 KB Unlimited 10% data only

templog 2 d:\program files\MSSQL\data\templog.ldf NULL 111200 KB Unlimited 10% log only

from

Killer

Sassan Karai SSC Veteran Points: 290 More actions · Answer 5

Several aspects stand out. Your collation is Thai_CI_AS which might add addtional overhead (double byte for unicode?) (I am assuming your whole server is in the same collation. Your temp db mdf is over 1 GB in size with a current total temp db consumption of 'only' 263 MB.

To me this indicates that a) yiur temp db sizing is potentially off - since it' mdf had to autogrow up to 1.2 GB and its startup value is apparently significantly smaller (if adjusted from default at all) You may have significant i/o throughput requirements which basically inflate your temp db possibly due to slow checkpoint writes on yur disk subsystem (are you running yiur data files on Raid 5? (consider Raid 10).

Watch your temp db startup size - adjust it up and watch where temp db is autogrowing to in your environment - size it accordingly.

You will help out your server and underlying i/o system since autogrowth is certainly overhead you dont want to experience when disk i/o subsystem demand is already high during production.