Controlling the size of the Tempdb

Description:
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.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> 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.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...
>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
>> Description:
>> 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.
>> Rather than have to check the size of the tempdb.mdf
every
>> day to make sure it's not consuming too much drive
space,
>> we would like to find out if anyone has found an
automated
>> way of backing up the tempdb to reduce the log size, or
>> maybe even schedule a restart of the SQL services in
the
>> middle of the night to reset the log to its default
size?
>> Any help would be greatly appreciated.
>
>.
>