rouge queries

Recently I ran into an issue where a poorly crafted sql statement that was
building a temp table grew the tempdb from 5 megs to 35 gigs then errored out
as that was the size of the drive. The result was that the hard drive
thought it was out of space, setting off drive space alerts and stopping
anything from being written to dbs that used that drive.
The developer has rewritten the query….
The question remains, how can I stop something like this from happening
again. Does sql server have some sort of resource governor to detect
processes gone wild and kill them?
Fyi: based on my rough math, this temp table would have grown to over 180
gigs.
Jason,
You can (and probably should) configure your tempdb not to grow
automatically. That way when the temp table maxes out the size of the
database, the offending process would error out and release resources back
to the other processes. As a matter of fact, I believe in all databases
being configured not to grow automatically. Usage should be regularly
reviewed by a DBA and pro-active sizing should be performed when it won't
affect other processes.
Also, you can set up an alert through Perfmon to send an alert when the
Logical Disk/Free Megabytes falls below a specified threshold.
-- Bill
"jason" <jason@.discussions.microsoft.com> wrote in message
news:E0B61060-77A8-4D38-972A-EDC30DFC5070@.microsoft.com...
> Recently I ran into an issue where a poorly crafted sql statement that was
> building a temp table grew the tempdb from 5 megs to 35 gigs then errored
> out
> as that was the size of the drive. The result was that the hard drive
> thought it was out of space, setting off drive space alerts and stopping
> anything from being written to dbs that used that drive.
> The developer has rewritten the query..
> The question remains, how can I stop something like this from happening
> again. Does sql server have some sort of resource governor to detect
> processes gone wild and kill them?
> Fyi: based on my rough math, this temp table would have grown to over 180
> gigs.
>
|||Hello Jason,
I understand that you'd like to monitor tempdb log file to avoid it use too
much disk size.
YOu could run following statement to see the log used space on the tempdb.
dbcc sqlperf(logspace)
You may run scheduled job to monitor the size routinely. Also, as Bill
mentioned perfmon alert is an another option.
The transaction log of tempdb is just to be able to roll back a
transaction. Therefore, once the transaction is committed, the related log
can be truncated automatically. Therefore, after checkpoint is done on the
tempdb, this log file size can be reused.
However, if there is large open transaction on tempdb that is not committed
due to some issues, the log file size might grow quickly. You may want to
run the statment to see the details:
dbcc opentran (tempdb)
If there is no open tran, you may want to run "checkpoint" statment on
tempdb to truncate transation log though this is supposed to run
automatically. This may reduce the templog.ldf a bit.
use tempdb
checkpoint
By default, the tempdb database is configured to autogrow as needed;
therefore, this database may grow in time to a size larger than desired. A
simple restart of SQL Server resets the size of tempdb to its last
configured size. The configured size is the last explicit size set with a
file size changing operation such as ALTER DATABASE with the MODIFY FILE
option or the DBCC SHRINKFILE statement. Please see the following article
for details:
307487How to shrink the tempdb database in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;307487
If you have any questions on the issue, please feel free to let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||thanks....
"AlterEgo" wrote:

> Jason,
> You can (and probably should) configure your tempdb not to grow
> automatically. That way when the temp table maxes out the size of the
> database, the offending process would error out and release resources back
> to the other processes. As a matter of fact, I believe in all databases
> being configured not to grow automatically. Usage should be regularly
> reviewed by a DBA and pro-active sizing should be performed when it won't
> affect other processes.
> Also, you can set up an alert through Perfmon to send an alert when the
> Logical Disk/Free Megabytes falls below a specified threshold.
> -- Bill
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:E0B61060-77A8-4D38-972A-EDC30DFC5070@.microsoft.com...
>
>
|||Bill, i did what you said on the Datafile...should i cap the log file too?
"AlterEgo" wrote:

> Jason,
> You can (and probably should) configure your tempdb not to grow
> automatically. That way when the temp table maxes out the size of the
> database, the offending process would error out and release resources back
> to the other processes. As a matter of fact, I believe in all databases
> being configured not to grow automatically. Usage should be regularly
> reviewed by a DBA and pro-active sizing should be performed when it won't
> affect other processes.
> Also, you can set up an alert through Perfmon to send an alert when the
> Logical Disk/Free Megabytes falls below a specified threshold.
> -- Bill
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:E0B61060-77A8-4D38-972A-EDC30DFC5070@.microsoft.com...
>
>
|||Hello Jason,
I think log file monitoring is important since we'v seen some very large
temp log file issues.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Peter,
we do monitor drive space, that is what alerted us to the problem. the
issue was that the log file grew from 50 megs to 30 gigs in 5 minutes.
"Peter Yang [MSFT]" wrote:

> Hello Jason,
> I think log file monitoring is important since we'v seen some very large
> temp log file issues.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hello Jason,
It's not normal that log file grow so quick on tempdb. Does the issue
consistently occur even if you restart SQL Service? Did you use "dbcc
opentran(tempdb)" to check the open transactions on tempdb? If the issue
occurs consistently, you may want to enable profiler to trace the
transactions related the tempdb. Please compare the time/SPID information
referenced in opentran result with the related records in profiler trace.
You may also send me the opentran result and profiler trace for some check.
Please remove "online" in my displayed email address. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Peter,
it hasn't been reoccuring.
I ran into an issue where a poorly crafted sql statement that was
building a temp table grew the tempdb from 5 megs to 35 gigs then errored out
as that was the size of the drive. they rewrote the query so it didn't
happen again. my question was how to stop this same thing happening again, i
limited size of tempdb for now as bill suggested.
"Peter Yang [MSFT]" wrote:

> Hello Jason,
> It's not normal that log file grow so quick on tempdb. Does the issue
> consistently occur even if you restart SQL Service? Did you use "dbcc
> opentran(tempdb)" to check the open transactions on tempdb? If the issue
> occurs consistently, you may want to enable profiler to trace the
> transactions related the tempdb. Please compare the time/SPID information
> referenced in opentran result with the related records in profiler trace.
> You may also send me the opentran result and profiler trace for some check.
> Please remove "online" in my displayed email address. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hello Jason,
Thank you for your clarification. I agree with that limit the tempdb size
is the better option for this situation. The related transaction may fail
and due to this limitation but you could avoid more critical sitution
caused by out of disk error. You may encounter log file full errors and
then you may troubleshoot the issue then.
If you have further comments or concerns, please let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====