Autogrow of file 'templog' in database 'tempdb' cancelled or timed out

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel Jameson - 25 Jul 2008 17:52 GMT

Hi,

We have a nightly process that twice in the last week has crashed, and at
the same time I find this error message logged in the SQL Server ERRORLOG
file:

---
Autogrow of file 'templog' in database 'tempdb' cancelled or timed out after
58922 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new
size.
---

The first time it happened, the templog file was about 28 GB, and the second
time it was about 10 GB. The data file was about 100 MB and 10MB,
respectively. With the 28 GB file, the disk still had over 60 GB free
space. Both the data file and the transaction log file are set to autogrow
without limit.

Any insight or suggestions would be greatly appreciated.

Signature

Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 25 Jul 2008 18:01 GMT

Is the growth for the log file set to MB or %? What is the number?

On 7/25/08 12:52 PM, in article ezvSMbn7IHA.4040@TK2MSFTNGP06.phx.gbl,

> Hi,
>
[quoted text clipped - 15 lines]
>
> Any insight or suggestions would be greatly appreciated.

Reply to this Message

Andrew J. Kelly - 25 Jul 2008 18:34 GMT

The amount of free space on the disk is not the issue. It is the fact it is
taking longer than the timeout period to grow and initialize the file. This
is one reason why you should never use a % for autogrowth and use a fixed
size instead that is small enough to always grow in 10 to 15 seconds max.
But why do you have a 28GB log file with a data file that is only 100MB? Is
this db in FULL recovery mode? If so are you issuing regular log backups?

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Hi,
>
[quoted text clipped - 15 lines]
>
> Any insight or suggestions would be greatly appreciated.

Reply to this Message

Roy Harvey (SQL Server MVP) - 25 Jul 2008 18:47 GMT

>But why do you have a 28GB log file with a data file that is only 100MB? Is
>this db in FULL recovery mode? If so are you issuing regular log backups?

Since it was said to be tempdb I guess we can give them a pass on the
regular log backups, but it takes real imagination to figure out how
to get the tempdb log and data to those relative sizes. I guess this
would do it (though it is UNtested):

BEGIN TRAN
SELECT *
INTO #Nonsense
FROM sysobjects

WHILE @@rowcount > 0
UPDATE #Nonsense
SET name = REVERSE(name)

COMMIT TRAN

Roy Harvey
Beacon Falls, CT

Reply to this Message

Daniel Jameson - 25 Jul 2008 19:18 GMT

Hi,

Aaron: It is set to 10% autogrow.

Andrew: I agree that figuring out the 28GB to 100MB ratio is our main
concern. What are the applications doing to cause that? What was even
stranger, is when the file was 28 GB, Enterprise Manager's Shrink File
dialog box said that it had 25 GB used. But when I did a File Shrink on
templog, within just a few seconds, it shrunk to 1 MB with .3 MB used. I
wondering why SQL Server thought that 25 GB was used, but then allowed it to
be discarded so quickly.

Andrew: It has simple recovery model and it is not backed up at all.

Roy: The application developers claim that the only transactions in the
application running at the time have only insert statements. But they do
make heavy use of temp tables.

We are going to do a SQL Server restart 15 min before the job's next run so
we have a fresh templog file. Then I will monitor templog's size as the job
runs.

Signature

Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

>>But why do you have a 28GB log file with a data file that is only 100MB?
>>Is
[quoted text clipped - 18 lines]
> Roy Harvey
> Beacon Falls, CT

Reply to this Message

Andrew J. Kelly - 25 Jul 2008 19:23 GMT

Sorry Daniel I completely missed the fact that it was tempdb. What version
are you running? There were several issues related to Tempdb filling up like
that with SP2 or eelier. If you don't already you should look at loading one
of the cumulative updates. I believe CU7 had most if not all the related
fixes.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Hi,
>
[quoted text clipped - 40 lines]
>> Roy Harvey
>> Beacon Falls, CT

Reply to this Message

Daniel Jameson - 25 Jul 2008 19:56 GMT

Andrew,

We are running SQL 2000 SP4.

Signature

Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

> Sorry Daniel I completely missed the fact that it was tempdb. What
> version are you running? There were several issues related to Tempdb
[quoted text clipped - 47 lines]
>>> Roy Harvey
>>> Beacon Falls, CT

Reply to this Message

Andrew J. Kelly - 25 Jul 2008 23:26 GMT

Ahh, well for future reference always state the version you are on. The
issues I mentioned are 2005 related. I wish I had more for you.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Andrew,
>
[quoted text clipped - 51 lines]
>>>> Roy Harvey
>>>> Beacon Falls, CT

Reply to this Message

TheSQLGuru - 26 Jul 2008 22:09 GMT

Set up a profiler trace and check for a looping situation where small
amounts of data are updated iteratively, or insert/deletes are done
iteratively, all within an uncommitted transaction. This would lead to a
small datafile size but massive tempdb log file. It is about the only thing
I can think of that would lead to it, other than a bug.

Hmm, what is your recovery interval on the server set to? Probably couldn't
do this but I will ask anyway.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Hi,
>
[quoted text clipped - 40 lines]
>> Roy Harvey
>> Beacon Falls, CT

Reply to this Message

Daniel Jameson - 29 Jul 2008 17:33 GMT

Hi,

Thank you all for your timely input. It seems that the templog.ldb file had
gotten corrupted and was not properly recycling its committed blocks. I
stopped the MSSQLSERVER service, renamed the templog.ldb file to
templogbak.ldb to force SQL Server to create a completely fresh templog.ldb
file, and the started MSSQLSERVER.

That nightly process has run three times since then, and templog.ldb is
still only 2 MB and the tempdb.mdb file is only 32 MB.

Signature

Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

> Set up a profiler trace and check for a looping situation where small
> amounts of data are updated iteratively, or insert/deletes are done
[quoted text clipped - 50 lines]
>>> Roy Harvey
>>> Beacon Falls, CT

Reply to this Message