Help: tempdb grows to 8G suddenly

Hi, guys,
The tempdb is usually 70/80M in size when SQL start, but it grows to 8G
suddently during times, how could I identify where the issue is, it happened
after application upgraded to newer version, but no sp or job changed.
application db is now <>10G.
I am using SQL2000+SP3 IN WIN2000+SP4
Thanks in advance.
Regards,
JackJack,
In my experience, sudden increases in tempdb size are usually attributable
to someone running a query which has a join that has not been qualified
properly. In such a case, a huge result set gets built up (in tempdb where
temporary working space is always allocated during query execution) in order
to satisfy the query and tempdb quite often runs out of space. As a really
basic example, in the old pre SQL-99 syntax, let's say someone was trying to
write
select * from tableA a, tableB b
where a.id = b.id
and a.surname like 'H%'
and b.country = 'Australia'
and they left off the join part of the WHERE clause
select * from tableA a, tableB b
where a.surname like 'H%'
and b.country = 'Australia'
they'd end up with the results getting pulled from the Cartesian product of
tableA & tableB (ie. a CROSS JOIN) rather than an INNER JOIN of the two
tables. Usually, you see this in much larger queries with many JOIN
statements, some with multiple join criteria (eg. multi-column primary
keys), and so it is much harder to identify but if you can get onto the
server while tempdb is filling up rapidly and check sp_who repeatedly, you
should be able to see which SPID is chewing up the CPU & I/O most rapidly;
then you can do a DBCC INPUTBUFFER (<spid>) to find out what query that SPID
is trying to execute and if you look hard enough you can often find a join
in that query that is not qualified properly.
HTH
--
Cheers,
Mike
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:e0WrtAq0EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi, guys,
> The tempdb is usually 70/80M in size when SQL start, but it grows to 8G
> suddently during times, how could I identify where the issue is, it
> happened
> after application upgraded to newer version, but no sp or job changed.
> application db is now <>10G.
> I am using SQL2000+SP3 IN WIN2000+SP4
> Thanks in advance.
> Regards,
> Jack
>|||One possibility is a query that caused a lot of worktables to be created.
Since it takes quite a while to create worktables to occupy 8 Gig, you might
use profiler to monitor queries taking > x minutes. Then, shrink the
tempdb, run the query via Query Analyzer and see if tempdb grows again.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:e0WrtAq0EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi, guys,
> The tempdb is usually 70/80M in size when SQL start, but it grows to 8G
> suddently during times, how could I identify where the issue is, it
happened
> after application upgraded to newer version, but no sp or job changed.
> application db is now <>10G.
> I am using SQL2000+SP3 IN WIN2000+SP4
> Thanks in advance.
> Regards,
> Jack
>|||Hi, Mike,
thanks for your valuable info, let me see what I can do, things are messed
up.
Regards,
Jack
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:OonAlcq0EHA.1308@.TK2MSFTNGP09.phx.gbl...
> Jack,
> In my experience, sudden increases in tempdb size are usually attributable
> to someone running a query which has a join that has not been qualified
> properly. In such a case, a huge result set gets built up (in tempdb
where
> temporary working space is always allocated during query execution) in
order
> to satisfy the query and tempdb quite often runs out of space. As a
really
> basic example, in the old pre SQL-99 syntax, let's say someone was trying
to
> write
> select * from tableA a, tableB b
> where a.id = b.id
> and a.surname like 'H%'
> and b.country = 'Australia'
> and they left off the join part of the WHERE clause
> select * from tableA a, tableB b
> where a.surname like 'H%'
> and b.country = 'Australia'
> they'd end up with the results getting pulled from the Cartesian product
of
> tableA & tableB (ie. a CROSS JOIN) rather than an INNER JOIN of the two
> tables. Usually, you see this in much larger queries with many JOIN
> statements, some with multiple join criteria (eg. multi-column primary
> keys), and so it is much harder to identify but if you can get onto the
> server while tempdb is filling up rapidly and check sp_who repeatedly, you
> should be able to see which SPID is chewing up the CPU & I/O most rapidly;
> then you can do a DBCC INPUTBUFFER (<spid>) to find out what query that
SPID
> is trying to execute and if you look hard enough you can often find a join
> in that query that is not qualified properly.
> HTH
> --
> Cheers,
> Mike
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:e0WrtAq0EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > Hi, guys,
> >
> > The tempdb is usually 70/80M in size when SQL start, but it grows to 8G
> > suddently during times, how could I identify where the issue is, it
> > happened
> > after application upgraded to newer version, but no sp or job changed.
> > application db is now <>10G.
> >
> > I am using SQL2000+SP3 IN WIN2000+SP4
> >
> > Thanks in advance.
> >
> > Regards,
> >
> > Jack
> >
> >
>|||FYI...
If you're trying to find where this happens...
Profiler has an event called Missing Join Predicate in the errors and
warnings event class that will capture situation in which SQL Server thinks
that a join predicate is missing. I have never tested the logic to see how
it determines the missing predicate to know if it can absolutley catch all
partial cartesian products, but it does a pretty good job and is worth
taking a look at...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:O5iC7i41EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Hi, Mike,
> thanks for your valuable info, let me see what I can do, things are messed
> up.
> Regards,
> Jack
> "Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
> news:OonAlcq0EHA.1308@.TK2MSFTNGP09.phx.gbl...
> > Jack,
> >
> > In my experience, sudden increases in tempdb size are usually
attributable
> > to someone running a query which has a join that has not been qualified
> > properly. In such a case, a huge result set gets built up (in tempdb
> where
> > temporary working space is always allocated during query execution) in
> order
> > to satisfy the query and tempdb quite often runs out of space. As a
> really
> > basic example, in the old pre SQL-99 syntax, let's say someone was
trying
> to
> > write
> >
> > select * from tableA a, tableB b
> > where a.id = b.id
> > and a.surname like 'H%'
> > and b.country = 'Australia'
> >
> > and they left off the join part of the WHERE clause
> >
> > select * from tableA a, tableB b
> > where a.surname like 'H%'
> > and b.country = 'Australia'
> >
> > they'd end up with the results getting pulled from the Cartesian product
> of
> > tableA & tableB (ie. a CROSS JOIN) rather than an INNER JOIN of the two
> > tables. Usually, you see this in much larger queries with many JOIN
> > statements, some with multiple join criteria (eg. multi-column primary
> > keys), and so it is much harder to identify but if you can get onto the
> > server while tempdb is filling up rapidly and check sp_who repeatedly,
you
> > should be able to see which SPID is chewing up the CPU & I/O most
rapidly;
> > then you can do a DBCC INPUTBUFFER (<spid>) to find out what query that
> SPID
> > is trying to execute and if you look hard enough you can often find a
join
> > in that query that is not qualified properly.
> >
> > HTH
> >
> > --
> > Cheers,
> > Mike
> >
> > "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> > news:e0WrtAq0EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > > Hi, guys,
> > >
> > > The tempdb is usually 70/80M in size when SQL start, but it grows to
8G
> > > suddently during times, how could I identify where the issue is, it
> > > happened
> > > after application upgraded to newer version, but no sp or job changed.
> > > application db is now <>10G.
> > >
> > > I am using SQL2000+SP3 IN WIN2000+SP4
> > >
> > > Thanks in advance.
> > >
> > > Regards,
> > >
> > > Jack
> > >
> > >
> >
> >
>

Help: SQL 2000 connection error(.NET 2.0, VS 2005)

Moderated by XIII, please use a normal font size for better readability:

for some reason, my web application can not connect to my SQL 2000

my application is based on .net 2.0 (IIS 5.1) using vs 2005workstation does not install any sql serversql 2000 locates at the remote server.the sql server can be connected via vs environment (data connnection), therefore, i think the connection string, server name and instance name is correcti have tried all the solution i can find online, but it is not workingLocalSQLServer connection string has been removed in GobalSettingalso remove all default connection string in web.config (<clear />)aspnet_regsql can register the sql 2000 db successfulvs 2005 can access and display the data in sql 2000 quite wellhas been reboot lots of times.still bug out

any body have any idea? thanks, it already costs me the whole day

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Can you telnet to the remote server (test TCP/IP)? And how about "net use\\servername\IPC$" the remote server (test Named pipe)? You can take a look at this post:

http://forums.asp.net/thread/1266057.aspx

|||

Thank Jay,

I can telnet to the remote server. Actually, I think the connection to the remote server is correct (in VS 2005 environment the data can be display quite well), the only problem is .NET 2.0 always think the remote server is SQL 2005. I have removed the localSQLServer connection string in web.config file (<clear />) but still not working.

|||

Then how about force to use TCP/IP to connect? You can do this by usingtcp:myinstance,portas Data Source, where port is the tcp port used the SQL instance service. Actually there should be no difference when connect to SQL2005 or SQL2000, you can take a look at this article which shows how to place membership on SQL2000:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

HELP: MFC ODBC SQL Server problems

I got a problem where in SQL Server a table has a column of type REAL & size
(precision) 4. It's meant to store double types for C++ variable values.
In SQL Server, the value 1E+10 translates to 10000000000 in C++ fine, via
MFC's CRecordset class (DBCORE.CPP). However any numbers above this value
translates to wrong numbers. Such as:
1.1E+10 => 10000000512
1.00001E+10 => 10000001024
1.01E+10 => 10099999744
1.0000001E+10 => 10000100352
Strange huh? Has anybody experienced this before? Does anyone know what is
wrong? I tried upgrading my SQL Server 2000 to SP4 and even upgraded MAC2.6
to SP2. Still no success.You should not use "Real" to handle a big number like "1E+10" since real
data type does not have enough precisions to do the job. If you change
the data type to "Float", you will have better chance to get the number
right. Or if you want to store "Exact" numbers, you should use "decimal"
as your data type.
Charles Zhang
http://www.speedydb.com
SpeedyDB ADO.NET is the fastest, most secure, and most flexible ADO.NET
Provider over Wide Area Netword (WAN).
Andrew Wan wrote:
> I got a problem where in SQL Server a table has a column of type REAL & si
ze
> (precision) 4. It's meant to store double types for C++ variable values.
> In SQL Server, the value 1E+10 translates to 10000000000 in C++ fine, via
> MFC's CRecordset class (DBCORE.CPP). However any numbers above this value
> translates to wrong numbers. Such as:
> 1.1E+10 => 10000000512
> 1.00001E+10 => 10000001024
> 1.01E+10 => 10099999744
> 1.0000001E+10 => 10000100352
> Strange huh? Has anybody experienced this before? Does anyone know what is
> wrong? I tried upgrading my SQL Server 2000 to SP4 and even upgraded MAC2.
6
> to SP2. Still no success.
>
>