Error 9002, tempdb log full

We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
DanDan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is ver
y
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:

> We have a reporting database that end users run ad-hoc queries against (SQ
L
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log fo
r
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she i
s
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of dis
k
> space, and have gotten this message with tempdb log set to unrestricted an
d
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because
it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this
?
> Thanks,
> Dan

Error 9002, tempdb log full

We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
Dan
Dan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is very
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:

> We have a reporting database that end users run ad-hoc queries against (SQL
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log for
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she is
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of disk
> space, and have gotten this message with tempdb log set to unrestricted and
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this?
> Thanks,
> Dan

Error 9002, tempdb log full

We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
DanDan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is very
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:
> We have a reporting database that end users run ad-hoc queries against (SQL
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log for
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she is
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of disk
> space, and have gotten this message with tempdb log set to unrestricted and
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this?
> Thanks,
> Dan

Error 9002 tempdb log file is full

Hi,
I've the following error message, which doesn't make sense to me.
The log file is not full, it has autogrow set to on and the disk drive on
which it
resides has 256GB free space. I hope i'm missing something and welcome
any suggestions on what to do or what to look at.
"Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log space."If the application is requesting space in TempDB and the request interval is
faster than the disk can allocate the space then you will see this message
whilst the Autogrow is happening.
If you have this much space then try pre allocating a larger TempDB
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Stressed" <k@.c.co.uk> wrote in message
news:ufQ6UCJlDHA.2536@.tk2msftngp13.phx.gbl...
> Hi,
>
> I've the following error message, which doesn't make sense to me.
> The log file is not full, it has autogrow set to on and the disk drive on
> which it
> resides has 256GB free space. I hope i'm missing something and welcome
> any suggestions on what to do or what to look at.
>
> "Server: Msg 9002, Level 17, State 6, Line 1
>
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log space."
>|||Thanks for that, it all makes sense now.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:O$FlXLJlDHA.1084@.tk2msftngp13.phx.gbl...
> If the application is requesting space in TempDB and the request interval
is
> faster than the disk can allocate the space then you will see this message
> whilst the Autogrow is happening.
> If you have this much space then try pre allocating a larger TempDB
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Stressed" <k@.c.co.uk> wrote in message
> news:ufQ6UCJlDHA.2536@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> >
> >
> > I've the following error message, which doesn't make sense to me.
> >
> > The log file is not full, it has autogrow set to on and the disk drive
on
> > which it
> >
> > resides has 256GB free space. I hope i'm missing something and welcome
> >
> > any suggestions on what to do or what to look at.
> >
> >
> >
> > "Server: Msg 9002, Level 17, State 6, Line 1
> >
> >
> >
> > The log file for database 'tempdb' is full. Back up the
> >
> > transaction log for the database to free up some log space."
> >
> >
>|||Just to add - check with Profiler what is going on. TempDb could be a target
of a DoS attack.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:O$FlXLJlDHA.1084@.tk2msftngp13.phx.gbl...
> If the application is requesting space in TempDB and the request interval
is
> faster than the disk can allocate the space then you will see this message
> whilst the Autogrow is happening.
> If you have this much space then try pre allocating a larger TempDB
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Stressed" <k@.c.co.uk> wrote in message
> news:ufQ6UCJlDHA.2536@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> >
> >
> > I've the following error message, which doesn't make sense to me.
> >
> > The log file is not full, it has autogrow set to on and the disk drive
on
> > which it
> >
> > resides has 256GB free space. I hope i'm missing something and welcome
> >
> > any suggestions on what to do or what to look at.
> >
> >
> >
> > "Server: Msg 9002, Level 17, State 6, Line 1
> >
> >
> >
> > The log file for database 'tempdb' is full. Back up the
> >
> > transaction log for the database to free up some log space."
> >
> >
>|||The server isn't exposed to the outside world, although the point has been
taken
on board for future occurences of the same.
Thanks.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OPFEUOJlDHA.3024@.tk2msftngp13.phx.gbl...
> Just to add - check with Profiler what is going on. TempDb could be a
target
> of a DoS attack.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:O$FlXLJlDHA.1084@.tk2msftngp13.phx.gbl...
> > If the application is requesting space in TempDB and the request
interval
> is
> > faster than the disk can allocate the space then you will see this
message
> > whilst the Autogrow is happening.
> >
> > If you have this much space then try pre allocating a larger TempDB
> >
> >
> >
> > --
> > --
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Stressed" <k@.c.co.uk> wrote in message
> > news:ufQ6UCJlDHA.2536@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > >
> > >
> > > I've the following error message, which doesn't make sense to me.
> > >
> > > The log file is not full, it has autogrow set to on and the disk drive
> on
> > > which it
> > >
> > > resides has 256GB free space. I hope i'm missing something and welcome
> > >
> > > any suggestions on what to do or what to look at.
> > >
> > >
> > >
> > > "Server: Msg 9002, Level 17, State 6, Line 1
> > >
> > >
> > >
> > > The log file for database 'tempdb' is full. Back up the
> > >
> > > transaction log for the database to free up some log space."
> > >
> > >
> >
> >
>sql

Error 605, 823

Does anyone see these errors before? How did you fixed it?
Attempt to fetch logical page (4:27456) in database 'tempdb' belongs to
object '0', not to object '#1AB78AA5'..
Error: 605, Severity: 21, State: 1
... IAM indicates that page is allocated to this object
(4:27456)/(4:27456) 0/448236197
pageno is/should be: objid is/should be:
Getpage: bstat=0x9, sstat=0x4800, cache
I/O error (bad page ID) detected during read at offset 0x000000b6ec4000 in
file 'I:\MSSQL\Data\tempdb1.mdf'..
Error: 823, Severity: 24, State: 2
Thanks,
Lijun
General recommendations for corrupt or suspect databases:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message news:usIUpWzdFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Does anyone see these errors before? How did you fixed it?
> Attempt to fetch logical page (4:27456) in database 'tempdb' belongs to
> object '0', not to object '#1AB78AA5'..
> Error: 605, Severity: 21, State: 1
> ... IAM indicates that page is allocated to this object
> (4:27456)/(4:27456) 0/448236197
> pageno is/should be: objid is/should be:
> Getpage: bstat=0x9, sstat=0x4800, cache
> I/O error (bad page ID) detected during read at offset 0x000000b6ec4000 in
> file 'I:\MSSQL\Data\tempdb1.mdf'..
> Error: 823, Severity: 24, State: 2
>
> Thanks,
> Lijun
>