Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.

> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.

> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.

> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1

> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.

> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>