SQL TempDB - Runaway File Size

Yahoo Archive
1
Our experience has been that the BAQ stops returning data. We have had some
BAQ's stop returning data after updating to a new patch level. Most of the
time, we are able to look at the BAQ and reestablish the links between
tables and they start working again. That is the only criteria I know of
with my limited experience with bad BAQ's.

Jeff



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Dale S Walker
Sent: Tuesday, August 18, 2009 9:56 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: SQL TempDB - Runaway File Size





If a BAQ tests OK while in development how does one know if it is a "bad
BAQ"?

[cid:image001.jpg@01CA1FE1.B5B8A4C0
<mailto:image001.jpg%4001CA1FE1.B5B8A4C0> ]
Dale Walker
Director, Information Technology
Le Sueur Incorporated
1409 Vine Street
Le Sueur, MN 56058
ph.507.665.6204 x277
fx .507.665.8466

www.lesururinc.com<http://www.lesururinc.com>

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
2
Vantage .407 SQL

We have a problem where at different times our SQL tempdb file starts to grow and fills the drive. This causes the system to fail and forces a reboot.

Has anyone had problems like this ? What caused this for you ?

Under normal circumstances the tempdb runs at 30-50 MB but when it goes into this failure mode it grows to over 90 GB until the partition is full.

Any suggestions on how to determine what is causing this to happen ?

Tools or Scripts or what to look for would be appreciated.

This tends to happen during the day, it doesn't happen near scheduled events like Full Text Catalog Rebuilds or Backups or MRP runs which are all done in the middle of the night.

TIA,
Neil
3
When we have had this problem it was caused by a bad (poorly written) BAQ.
Is SQL Server Management Studio, you can go to the databases, system
databases and select the TempDB. If you right click on it and select tasks,
you can select Shrink. This will bring the TempDB back to normal size.
This only works if the process that caused the TempDB to grow is done
running.

Jeff



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
too_much_hg
Sent: Monday, August 17, 2009 12:25 PM
To: vantage@yahoogroups.com
Subject: [Vantage] SQL TempDB - Runaway File Size





Vantage .407 SQL

We have a problem where at different times our SQL tempdb file starts to
grow and fills the drive. This causes the system to fail and forces a
reboot.

Has anyone had problems like this ? What caused this for you ?

Under normal circumstances the tempdb runs at 30-50 MB but when it goes into
this failure mode it grows to over 90 GB until the partition is full.

Any suggestions on how to determine what is causing this to happen ?

Tools or Scripts or what to look for would be appreciated.

This tends to happen during the day, it doesn't happen near scheduled events
like Full Text Catalog Rebuilds or Backups or MRP runs which are all done in
the middle of the night.

TIA,
Neil





[Non-text portions of this message have been removed]
4
I don't have an answer on this, but I've seen it myself.

Its not a Vantage problem per se. MSSQL 2005 uses Tempdb for a lot of its work, and essentially *all* Vantage updates are done through temp tables, thus using tempdb.

One thing I'd recommend is to move TempDB database file and transaction log to its own partition. That way if it fills things up you're not impacting anything else.

Moving TempDB is easy - just set the database and log file locations using Enterprise Manager (or T-SQL) and restart the database engine. It gets truncated at stop and recreated at start anyways.

I would see what 'dbcc opentran' shows, or MSSQL activity monitor. I'd wonder if there's a blocked transaction causing lots of other things to queue up and tempdb to fill.

Some good ideas here:

http://www.mssqltips.com/tip.asp?tip=1388

But no one-size-fits-all answer.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of too_much_hg
Sent: Monday, August 17, 2009 12:25 PM
To: vantage@yahoogroups.com
Subject: [Vantage] SQL TempDB - Runaway File Size

Vantage .407 SQL

We have a problem where at different times our SQL tempdb file starts to grow and fills the drive. This causes the system to fail and forces a reboot.

Has anyone had problems like this ? What caused this for you ?

Under normal circumstances the tempdb runs at 30-50 MB but when it goes into this failure mode it grows to over 90 GB until the partition is full.

Any suggestions on how to determine what is causing this to happen ?

Tools or Scripts or what to look for would be appreciated.

This tends to happen during the day, it doesn't happen near scheduled events like Full Text Catalog Rebuilds or Backups or MRP runs which are all done in the middle of the night.

TIA,
Neil
5
Neil,

Yes, seen this problem many times.

As someone else mentioned, it's due to a malformed BAQ being ran.
Ensure anyone who has BAQ rights inform you prior to executing a newly
developed (untested) BAQ.

As Brian said, move the TEMPDB to it's very own segregated partition
away from all other databases.

Other than that, there's nothing much you can do.

Restarting SQL is the ONLY way to reset the TempDB. Contrary to Jeff's
statement, Shrinking it will never fully reclaim all space (at least it
doesn't for me).

I had a user run a bad BAQ that swelled my TempDB from 50MB to 11GB in a
little over an hour!!

I wrote a SQL query and I created an ASP dashboard that monitors the
size of all databases. Just an idea.

Good luck.



Vic


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of too_much_hg
Sent: Monday, August 17, 2009 12:25 PM
To: vantage@yahoogroups.com
Subject: [Vantage] SQL TempDB - Runaway File Size

Vantage .407 SQL

We have a problem where at different times our SQL tempdb file starts to
grow and fills the drive. This causes the system to fail and forces a
reboot.

Has anyone had problems like this ? What caused this for you ?

Under normal circumstances the tempdb runs at 30-50 MB but when it goes
into this failure mode it grows to over 90 GB until the partition is
full.

Any suggestions on how to determine what is causing this to happen ?

Tools or Scripts or what to look for would be appreciated.

This tends to happen during the day, it doesn't happen near scheduled
events like Full Text Catalog Rebuilds or Backups or MRP runs which are
all done in the middle of the night.

TIA,
Neil



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

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
6
You are correct, you will not recover all of the available space, but if the
BAQ has finished processing, sometimes this can reclaim most of the area
taken up. If it works, you will not have to reboot or stop the AppServers
during the middle of the day. I have used the Shrink function during the
day to take my TempDB from 41 GB to 12 MB, saving my company some downtime.

Jeff



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Vic Drecchio
Sent: Monday, August 17, 2009 1:06 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] SQL TempDB - Runaway File Size





Neil,

Yes, seen this problem many times.

As someone else mentioned, it's due to a malformed BAQ being ran.
Ensure anyone who has BAQ rights inform you prior to executing a newly
developed (untested) BAQ.

As Brian said, move the TEMPDB to it's very own segregated partition
away from all other databases.

Other than that, there's nothing much you can do.

Restarting SQL is the ONLY way to reset the TempDB. Contrary to Jeff's
statement, Shrinking it will never fully reclaim all space (at least it
doesn't for me).

I had a user run a bad BAQ that swelled my TempDB from 50MB to 11GB in a
little over an hour!!

I wrote a SQL query and I created an ASP dashboard that monitors the
size of all databases. Just an idea.

Good luck.

Vic

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of too_much_hg
Sent: Monday, August 17, 2009 12:25 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] SQL TempDB - Runaway File Size

Vantage .407 SQL

We have a problem where at different times our SQL tempdb file starts to
grow and fills the drive. This causes the system to fail and forces a
reboot.

Has anyone had problems like this ? What caused this for you ?

Under normal circumstances the tempdb runs at 30-50 MB but when it goes
into this failure mode it grows to over 90 GB until the partition is
full.

Any suggestions on how to determine what is causing this to happen ?

Tools or Scripts or what to look for would be appreciated.

This tends to happen during the day, it doesn't happen near scheduled
events like Full Text Catalog Rebuilds or Backups or MRP runs which are
all done in the middle of the night.

TIA,
Neil

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

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links





[Non-text portions of this message have been removed]
7
We had this same problem when we updated to 8.03.407c. It was very hard to track down. We finally found that it was related to a BAQ/Dashboard that we had been using for about 1.5 years. I don't know of any way to see what Vantage users are using BAQs/Dashboards.

How did the rest of you track down the problem BAQ?


--- In vantage@yahoogroups.com, "Jeff Stockard" <jeff.stockard@...> wrote:
>
> You are correct, you will not recover all of the available space, but if the
> BAQ has finished processing, sometimes this can reclaim most of the area
> taken up. If it works, you will not have to reboot or stop the AppServers
> during the middle of the day. I have used the Shrink function during the
> day to take my TempDB from 41 GB to 12 MB, saving my company some downtime.
>
> Jeff
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
> Vic Drecchio
> Sent: Monday, August 17, 2009 1:06 PM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] SQL TempDB - Runaway File Size
>
>
>
>
>
> Neil,
>
> Yes, seen this problem many times.
>
> As someone else mentioned, it's due to a malformed BAQ being ran.
> Ensure anyone who has BAQ rights inform you prior to executing a newly
> developed (untested) BAQ.
>
> As Brian said, move the TEMPDB to it's very own segregated partition
> away from all other databases.
>
> Other than that, there's nothing much you can do.
>
> Restarting SQL is the ONLY way to reset the TempDB. Contrary to Jeff's
> statement, Shrinking it will never fully reclaim all space (at least it
> doesn't for me).
>
> I had a user run a bad BAQ that swelled my TempDB from 50MB to 11GB in a
> little over an hour!!
>
> I wrote a SQL query and I created an ASP dashboard that monitors the
> size of all databases. Just an idea.
>
> Good luck.
>
> Vic
>
> -----Original Message-----
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
> Behalf
> Of too_much_hg
> Sent: Monday, August 17, 2009 12:25 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] SQL TempDB - Runaway File Size
>
> Vantage .407 SQL
>
> We have a problem where at different times our SQL tempdb file starts to
> grow and fills the drive. This causes the system to fail and forces a
> reboot.
>
> Has anyone had problems like this ? What caused this for you ?
>
> Under normal circumstances the tempdb runs at 30-50 MB but when it goes
> into this failure mode it grows to over 90 GB until the partition is
> full.
>
> Any suggestions on how to determine what is causing this to happen ?
>
> Tools or Scripts or what to look for would be appreciated.
>
> This tends to happen during the day, it doesn't happen near scheduled
> events like Full Text Catalog Rebuilds or Backups or MRP runs which are
> all done in the middle of the night.
>
> TIA,
> Neil
>
> ------------------------------------
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
> have already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report Builder
> and Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
> <http://groups.yahoo.com/group/vantage/files/>
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
8
It was hit and miss. I had one user come in and complain that the Dashboard
she had been using for quite a while was not returning any data. While we
were talking, my phone started ringing that Vantage was running slow. On
another instance, I got cocky and created a BAQ and ran it in Live, without
testing it first. I had the join going the wrong direction and it
completely ran up the TempDB. The shrink function worked on the dashboard
problem, but the BAQ I created kept running after I closed it and ended my
session. I did have enough experience with the TempDB to check it while I
was running my test BAQ. But that did not do any good, I had to reboot to
get the TempDB down to a normal size.

Most of the time I find out after someone tells me that their BAQ or
dashboard is not working (keeps running and returns no data).

Jeff



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
lapulsifer
Sent: Monday, August 17, 2009 4:02 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: SQL TempDB - Runaway File Size





We had this same problem when we updated to 8.03.407c. It was very hard to
track down. We finally found that it was related to a BAQ/Dashboard that we
had been using for about 1.5 years. I don't know of any way to see what
Vantage users are using BAQs/Dashboards.

How did the rest of you track down the problem BAQ?

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Jeff
Stockard" <jeff.stockard@...> wrote:
>
> You are correct, you will not recover all of the available space, but if
the
> BAQ has finished processing, sometimes this can reclaim most of the area
> taken up. If it works, you will not have to reboot or stop the AppServers
> during the middle of the day. I have used the Shrink function during the
> day to take my TempDB from 41 GB to 12 MB, saving my company some
downtime.
>
> Jeff
>
>
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
> Vic Drecchio
> Sent: Monday, August 17, 2009 1:06 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: RE: [Vantage] SQL TempDB - Runaway File Size
>
>
>
>
>
> Neil,
>
> Yes, seen this problem many times.
>
> As someone else mentioned, it's due to a malformed BAQ being ran.
> Ensure anyone who has BAQ rights inform you prior to executing a newly
> developed (untested) BAQ.
>
> As Brian said, move the TEMPDB to it's very own segregated partition
> away from all other databases.
>
> Other than that, there's nothing much you can do.
>
> Restarting SQL is the ONLY way to reset the TempDB. Contrary to Jeff's
> statement, Shrinking it will never fully reclaim all space (at least it
> doesn't for me).
>
> I had a user run a bad BAQ that swelled my TempDB from 50MB to 11GB in a
> little over an hour!!
>
> I wrote a SQL query and I created an ASP dashboard that monitors the
> size of all databases. Just an idea.
>
> Good luck.
>
> Vic
>
> -----Original Message-----
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
> Behalf
> Of too_much_hg
> Sent: Monday, August 17, 2009 12:25 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] SQL TempDB - Runaway File Size
>
> Vantage .407 SQL
>
> We have a problem where at different times our SQL tempdb file starts to
> grow and fills the drive. This causes the system to fail and forces a
> reboot.
>
> Has anyone had problems like this ? What caused this for you ?
>
> Under normal circumstances the tempdb runs at 30-50 MB but when it goes
> into this failure mode it grows to over 90 GB until the partition is
> full.
>
> Any suggestions on how to determine what is causing this to happen ?
>
> Tools or Scripts or what to look for would be appreciated.
>
> This tends to happen during the day, it doesn't happen near scheduled
> events like Full Text Catalog Rebuilds or Backups or MRP runs which are
> all done in the middle of the night.
>
> TIA,
> Neil
>
> ------------------------------------
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
> have already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report Builder
> and Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/>
> <http://groups.yahoo.com/group/vantage/files/>
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
>
>
>
>
>
> [Non-text portions of this message have been removed]
>





[Non-text portions of this message have been removed]
9
If a BAQ tests OK while in development how does one know if it is a "bad BAQ"?







[cid:image001.jpg@01CA1FE1.B5B8A4C0]
Dale Walker
Director, Information Technology
Le Sueur Incorporated
1409 Vine Street
Le Sueur, MN 56058
ph.507.665.6204 x277
fx .507.665.8466

www.lesururinc.com<http://www.lesururinc.com>




[Non-text portions of this message have been removed]
10
Hypothetically if a BAQ tests "OK" in Dev then it will be fine in Live.


I knew the BAQs were bad when you Test the results from the BAQ window
and nothing ever is displayed. But, "Analyze" found no errors.

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Dale S Walker
Sent: Tuesday, August 18, 2009 9:56 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: SQL TempDB - Runaway File Size

If a BAQ tests OK while in development how does one know if it is a "bad
BAQ"?







[cid:image001.jpg@01CA1FE1.B5B8A4C0]
Dale Walker
Director, Information Technology
Le Sueur Incorporated
1409 Vine Street
Le Sueur, MN 56058
ph.507.665.6204 x277
fx .507.665.8466

www.lesururinc.com<http://www.lesururinc.com>




[Non-text portions of this message have been removed]



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

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links