|
Home > Archive > MS SQL Server > March 2006 > Tempdb Transcation Log Growing
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Tempdb Transcation Log Growing
|
|
|
| Hi
In the last few months the tempdb transaction log on our 4 of our servers
will not always checkpoint/truncate at 70% full, but will keep growing until
they are full and then autogrow. This will not stop until we run out of disk
space.
Initial thoughts were long-running transactions but we can manually issue a
checkpoint/dbcc shrink file and the space will be reclaimed (and running dbcc
opentran returns no open transactions in any database on the server).
The servers affected are all identical builds:
Windows Server 2003 Standard (SP1)
SQL 2000 SP4
All servers are subscribers in transactional replication (from the same
publisher)
Tempdb consists of 2 data files and 2 log files. The primary tempdb log and
data files are on a RAM disk with autogrow set to off. The second data/log
files are on physical disk these are the ones that are growing and having a
performance impact.
There doesn't seem to be any pattern as to which servers are affected (or
when). The only way we've been able to resolve the problem is by rebooting.
Any suggestions greatly appreciated.
Cheers,
Matt
| |
| Tom Moreau 2006-03-13, 8:23 pm |
| It's possible that a query with an ORDER BY or GROUP BY has been issued.
Behind the scenes, work tables get created and that may be responsible for
what happened. Also, you can just create a temp table and fill it. If it's
large enough, that could cause an autogrow.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:BF177931-4B5D-461B-AAE1- BE8D538A6B7A@microso
ft.com...
Hi
In the last few months the tempdb transaction log on our 4 of our servers
will not always checkpoint/truncate at 70% full, but will keep growing until
they are full and then autogrow. This will not stop until we run out of disk
space.
Initial thoughts were long-running transactions but we can manually issue a
checkpoint/dbcc shrink file and the space will be reclaimed (and running
dbcc
opentran returns no open transactions in any database on the server).
The servers affected are all identical builds:
Windows Server 2003 Standard (SP1)
SQL 2000 SP4
All servers are subscribers in transactional replication (from the same
publisher)
Tempdb consists of 2 data files and 2 log files. The primary tempdb log and
data files are on a RAM disk with autogrow set to off. The second data/log
files are on physical disk these are the ones that are growing and having a
performance impact.
There doesn't seem to be any pattern as to which servers are affected (or
when). The only way we've been able to resolve the problem is by rebooting.
Any suggestions greatly appreciated.
Cheers,
Matt
| |
|
| Hey Tom
Thanks for the speedy response!
I don't mind the autogrow happening, what I don't understand is why after
that it continues to autogrow (until we run out of disk space). Shouldn't the
log checkpoint and truncate at 70% full? I can manually checkpoint and issue
a dbcc shrinkfile which makes me think there's nothing stopping it happening
- it just doesn't seem to happening automatically...
Thanks,
Matt
"Tom Moreau" wrote:
> It's possible that a query with an ORDER BY or GROUP BY has been issued.
> Behind the scenes, work tables get created and that may be responsible for
> what happened. Also, you can just create a temp table and fill it. If it's
> large enough, that could cause an autogrow.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:BF177931-4B5D-461B-AAE1- BE8D538A6B7A@microso
ft.com...
> Hi
>
> In the last few months the tempdb transaction log on our 4 of our servers
> will not always checkpoint/truncate at 70% full, but will keep growing until
> they are full and then autogrow. This will not stop until we run out of disk
> space.
>
> Initial thoughts were long-running transactions but we can manually issue a
> checkpoint/dbcc shrink file and the space will be reclaimed (and running
> dbcc
> opentran returns no open transactions in any database on the server).
>
>
> The servers affected are all identical builds:
>
> Windows Server 2003 Standard (SP1)
> SQL 2000 SP4
> All servers are subscribers in transactional replication (from the same
> publisher)
>
> Tempdb consists of 2 data files and 2 log files. The primary tempdb log and
> data files are on a RAM disk with autogrow set to off. The second data/log
> files are on physical disk these are the ones that are growing and having a
> performance impact.
>
> There doesn't seem to be any pattern as to which servers are affected (or
> when). The only way we've been able to resolve the problem is by rebooting.
>
> Any suggestions greatly appreciated.
>
> Cheers,
> Matt
>
>
| |
| Tom Moreau 2006-03-13, 8:23 pm |
| There's really no guarantee of a checkpoint at a certain percent full. I
saw a tempdb log stay full with no pending transaction. It turned out that
a developer was stepping through his code and it involved an ORDER BY. The
log couldn't truncate until the last row had been retrieved.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:9C37C4B2-6E61-4F61-B49F- C39B580DF0D4@microso
ft.com...
Hey Tom
Thanks for the speedy response!
I don't mind the autogrow happening, what I don't understand is why after
that it continues to autogrow (until we run out of disk space). Shouldn't
the
log checkpoint and truncate at 70% full? I can manually checkpoint and issue
a dbcc shrinkfile which makes me think there's nothing stopping it happening
- it just doesn't seem to happening automatically...
Thanks,
Matt
"Tom Moreau" wrote:
> It's possible that a query with an ORDER BY or GROUP BY has been issued.
> Behind the scenes, work tables get created and that may be responsible for
> what happened. Also, you can just create a temp table and fill it. If
> it's
> large enough, that could cause an autogrow.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:BF177931-4B5D-461B-AAE1- BE8D538A6B7A@microso
ft.com...
> Hi
>
> In the last few months the tempdb transaction log on our 4 of our servers
> will not always checkpoint/truncate at 70% full, but will keep growing
> until
> they are full and then autogrow. This will not stop until we run out of
> disk
> space.
>
> Initial thoughts were long-running transactions but we can manually issue
> a
> checkpoint/dbcc shrink file and the space will be reclaimed (and running
> dbcc
> opentran returns no open transactions in any database on the server).
>
>
> The servers affected are all identical builds:
>
> Windows Server 2003 Standard (SP1)
> SQL 2000 SP4
> All servers are subscribers in transactional replication (from the same
> publisher)
>
> Tempdb consists of 2 data files and 2 log files. The primary tempdb log
> and
> data files are on a RAM disk with autogrow set to off. The second data/log
> files are on physical disk these are the ones that are growing and having
> a
> performance impact.
>
> There doesn't seem to be any pattern as to which servers are affected (or
> when). The only way we've been able to resolve the problem is by
> rebooting.
>
> Any suggestions greatly appreciated.
>
> Cheers,
> Matt
>
>
| |
|
| I thought that was the whole point of simple recovery mode - that log space
would be reclaimed when necessary. Also I can truncate the log manually.
Cheers,
Matt
"Tom Moreau" wrote:
> There's really no guarantee of a checkpoint at a certain percent full. I
> saw a tempdb log stay full with no pending transaction. It turned out that
> a developer was stepping through his code and it involved an ORDER BY. The
> log couldn't truncate until the last row had been retrieved.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:9C37C4B2-6E61-4F61-B49F- C39B580DF0D4@microso
ft.com...
> Hey Tom
>
> Thanks for the speedy response!
>
> I don't mind the autogrow happening, what I don't understand is why after
> that it continues to autogrow (until we run out of disk space). Shouldn't
> the
> log checkpoint and truncate at 70% full? I can manually checkpoint and issue
> a dbcc shrinkfile which makes me think there's nothing stopping it happening
> - it just doesn't seem to happening automatically...
>
> Thanks,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
| Tom Moreau 2006-03-13, 8:23 pm |
| It's a matter of timing. It can truncate only as far back as the oldest
committed transaction. With the ORDER BY/GROUP BY thing, even though you're
doing a SELECT, the work table isn't dropped until you retrieve the last
row. It doesn't really have anything to do with the simple recovery model.
It simply cannot truncate the active part of the log.
Likely what you're seeing is that by the time you manually truncate the log,
the work that caused the growth is already done.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:00AFD105-B2A8-4E4F-AE20- 3C9BD6A3FB5E@microso
ft.com...
I thought that was the whole point of simple recovery mode - that log space
would be reclaimed when necessary. Also I can truncate the log manually.
Cheers,
Matt
"Tom Moreau" wrote:
> There's really no guarantee of a checkpoint at a certain percent full. I
> saw a tempdb log stay full with no pending transaction. It turned out
> that
> a developer was stepping through his code and it involved an ORDER BY.
> The
> log couldn't truncate until the last row had been retrieved.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:9C37C4B2-6E61-4F61-B49F- C39B580DF0D4@microso
ft.com...
> Hey Tom
>
> Thanks for the speedy response!
>
> I don't mind the autogrow happening, what I don't understand is why after
> that it continues to autogrow (until we run out of disk space). Shouldn't
> the
> log checkpoint and truncate at 70% full? I can manually checkpoint and
> issue
> a dbcc shrinkfile which makes me think there's nothing stopping it
> happening
> - it just doesn't seem to happening automatically...
>
> Thanks,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
|
| If that's the case shouldn't the log eventually truncate by itself? What
we're seeing here is that once the log starts growing it never truncates
again (until the server is rebooted). I can manually truncate it or set up an
agent job to do it at any time and it never fails to free up space.
To give you an idea the log file size is normally 128MB and I've seen it
grow as large as 30GB (over a number of hours) - at any point I've been able
to manually truncate with no problem.
I'm not trying to be argumentative and I really appreciate your help, but I
can't believe this is normal behaviour.
We've been storing the results of ::fn_virtualfilestat
s to see if that will
help us identify the catalyst. We can see the behaviour happening, but not
what might be starting it.
Thanks again,
Matt
"Tom Moreau" wrote:
> It's a matter of timing. It can truncate only as far back as the oldest
> committed transaction. With the ORDER BY/GROUP BY thing, even though you're
> doing a SELECT, the work table isn't dropped until you retrieve the last
> row. It doesn't really have anything to do with the simple recovery model.
> It simply cannot truncate the active part of the log.
>
> Likely what you're seeing is that by the time you manually truncate the log,
> the work that caused the growth is already done.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:00AFD105-B2A8-4E4F-AE20- 3C9BD6A3FB5E@microso
ft.com...
> I thought that was the whole point of simple recovery mode - that log space
> would be reclaimed when necessary. Also I can truncate the log manually.
>
> Cheers,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
| Tom Moreau 2006-03-13, 8:23 pm |
| Be careful on how you define "truncate". Truncate does not mean "shrink".
For example, if the log is 10GB and the used part of it is 1GB, yet the
active portion is 100MB, then truncating it will result in the physical size
remaining at 10GB, but the used portion becoming 900MB. Thus, truncating
had no effect on the physical size of the log.
If you want the log to shrink, then you run DBCC SHRINKFILE on the log.
You may want to run a profiler trace and include the Autogrow event
hopefully, you can find the SQL that was fired before an Autogrow and
correlate it.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:C5B2FCBC-81C2-4850-AD13- EC1CE82D0735@microso
ft.com...
If that's the case shouldn't the log eventually truncate by itself? What
we're seeing here is that once the log starts growing it never truncates
again (until the server is rebooted). I can manually truncate it or set up
an
agent job to do it at any time and it never fails to free up space.
To give you an idea the log file size is normally 128MB and I've seen it
grow as large as 30GB (over a number of hours) - at any point I've been able
to manually truncate with no problem.
I'm not trying to be argumentative and I really appreciate your help, but I
can't believe this is normal behaviour.
We've been storing the results of ::fn_virtualfilestat
s to see if that will
help us identify the catalyst. We can see the behaviour happening, but not
what might be starting it.
Thanks again,
Matt
"Tom Moreau" wrote:
> It's a matter of timing. It can truncate only as far back as the oldest
> committed transaction. With the ORDER BY/GROUP BY thing, even though
> you're
> doing a SELECT, the work table isn't dropped until you retrieve the last
> row. It doesn't really have anything to do with the simple recovery
> model.
> It simply cannot truncate the active part of the log.
>
> Likely what you're seeing is that by the time you manually truncate the
> log,
> the work that caused the growth is already done.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:00AFD105-B2A8-4E4F-AE20- 3C9BD6A3FB5E@microso
ft.com...
> I thought that was the whole point of simple recovery mode - that log
> space
> would be reclaimed when necessary. Also I can truncate the log manually.
>
> Cheers,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
|
|
I guess that's my point, it's never truncating. I can issue a checkpoint
manually which frees up space in the file but it isn't doing this
automatically - so the file continues to grow forever.
Unfortunately because this happens sporadically (every week or two) and
these are production boxes I can't really run a long term trace on them to
see what's causing it.
Thanks,
Matt
"Tom Moreau" wrote:
> Be careful on how you define "truncate". Truncate does not mean "shrink".
> For example, if the log is 10GB and the used part of it is 1GB, yet the
> active portion is 100MB, then truncating it will result in the physical size
> remaining at 10GB, but the used portion becoming 900MB. Thus, truncating
> had no effect on the physical size of the log.
>
> If you want the log to shrink, then you run DBCC SHRINKFILE on the log.
>
> You may want to run a profiler trace and include the Autogrow event
> hopefully, you can find the SQL that was fired before an Autogrow and
> correlate it.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:C5B2FCBC-81C2-4850-AD13- EC1CE82D0735@microso
ft.com...
> If that's the case shouldn't the log eventually truncate by itself? What
> we're seeing here is that once the log starts growing it never truncates
> again (until the server is rebooted). I can manually truncate it or set up
> an
> agent job to do it at any time and it never fails to free up space.
>
> To give you an idea the log file size is normally 128MB and I've seen it
> grow as large as 30GB (over a number of hours) - at any point I've been able
> to manually truncate with no problem.
>
> I'm not trying to be argumentative and I really appreciate your help, but I
> can't believe this is normal behaviour.
>
> We've been storing the results of ::fn_virtualfilestat
s to see if that will
> help us identify the catalyst. We can see the behaviour happening, but not
> what might be starting it.
>
> Thanks again,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
| Tom Moreau 2006-03-14, 3:23 am |
| Try running:
sp_configure 'recovery interval'
I'm wondering if it got set to something other than the default. If it's
non-zero, I'd wonder why it was reset.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:7CAFED0F-4078-42CE-A79A- D082C3235244@microso
ft.com...
I guess that's my point, it's never truncating. I can issue a checkpoint
manually which frees up space in the file but it isn't doing this
automatically - so the file continues to grow forever.
Unfortunately because this happens sporadically (every week or two) and
these are production boxes I can't really run a long term trace on them to
see what's causing it.
Thanks,
Matt
"Tom Moreau" wrote:
> Be careful on how you define "truncate". Truncate does not mean "shrink".
> For example, if the log is 10GB and the used part of it is 1GB, yet the
> active portion is 100MB, then truncating it will result in the physical
> size
> remaining at 10GB, but the used portion becoming 900MB. Thus, truncating
> had no effect on the physical size of the log.
>
> If you want the log to shrink, then you run DBCC SHRINKFILE on the log.
>
> You may want to run a profiler trace and include the Autogrow event
> hopefully, you can find the SQL that was fired before an Autogrow and
> correlate it.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:C5B2FCBC-81C2-4850-AD13- EC1CE82D0735@microso
ft.com...
> If that's the case shouldn't the log eventually truncate by itself? What
> we're seeing here is that once the log starts growing it never truncates
> again (until the server is rebooted). I can manually truncate it or set up
> an
> agent job to do it at any time and it never fails to free up space.
>
> To give you an idea the log file size is normally 128MB and I've seen it
> grow as large as 30GB (over a number of hours) - at any point I've been
> able
> to manually truncate with no problem.
>
> I'm not trying to be argumentative and I really appreciate your help, but
> I
> can't believe this is normal behaviour.
>
> We've been storing the results of ::fn_virtualfilestat
s to see if that
> will
> help us identify the catalyst. We can see the behaviour happening, but not
> what might be starting it.
>
> Thanks again,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
|
| Hi Tom
All 4 servers have recovery interval set to 0.
Cheers,
Matt
"Tom Moreau" wrote:
> Try running:
>
> sp_configure 'recovery interval'
>
> I'm wondering if it got set to something other than the default. If it's
> non-zero, I'd wonder why it was reset.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:7CAFED0F-4078-42CE-A79A- D082C3235244@microso
ft.com...
>
> I guess that's my point, it's never truncating. I can issue a checkpoint
> manually which frees up space in the file but it isn't doing this
> automatically - so the file continues to grow forever.
>
> Unfortunately because this happens sporadically (every week or two) and
> these are production boxes I can't really run a long term trace on them to
> see what's causing it.
>
> Thanks,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
| Steen Persson (DK) 2006-03-14, 7:23 am |
| Matt wrote:
> I guess that's my point, it's never truncating. I can issue a checkpoint
> manually which frees up space in the file but it isn't doing this
> automatically - so the file continues to grow forever.
>
> Unfortunately because this happens sporadically (every week or two) and
> these are production boxes I can't really run a long term trace on them to
> see what's causing it.
>
> Thanks,
> Matt
>
Hi Matt
Are you sure that the file "grows forever" ? Since you say it's
happening occasionally, it could be that there are an operation in your
DB that causes a big grow of the tempdb. If this operation causes the
tempdb to grow to 30 GB, it can very well be that it's being truncated
fine afterwards (both automatically and by you) but the physical file
size will remains the same. As Tom mentions, truncate it not the same as
shrink and what SQL server does is only a trunate - not a shrink.
I think you only option is to either run a trace to see what causes the
growth or e.g. run DBCC SQLPERF(LOGSPACE) every 5 minutes and drop the
result into a table. The latter might not tell you what's causing the
growth, but it might give you an indication of our suspicion that it's
actually a long running that are causing the growth, and that the tempdb
is being truncated fine afterwards, but just stays at the same physical
size.
Regards
Steen
| |
| Tom Moreau 2006-03-14, 7:23 am |
| To add to Steen's comments, you could also do a profiler trace that captures
just the autogrow event. That won't have much impact on your server but it
will at least tell you when something happened. That can help to narrow it
down.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message
news:ucl4c00RGHA.256@TK2MSFTNGP14.phx.gbl...
Matt wrote:
> I guess that's my point, it's never truncating. I can issue a checkpoint
> manually which frees up space in the file but it isn't doing this
> automatically - so the file continues to grow forever.
>
> Unfortunately because this happens sporadically (every week or two) and
> these are production boxes I can't really run a long term trace on them to
> see what's causing it.
>
> Thanks,
> Matt
>
Hi Matt
Are you sure that the file "grows forever" ? Since you say it's
happening occasionally, it could be that there are an operation in your
DB that causes a big grow of the tempdb. If this operation causes the
tempdb to grow to 30 GB, it can very well be that it's being truncated
fine afterwards (both automatically and by you) but the physical file
size will remains the same. As Tom mentions, truncate it not the same as
shrink and what SQL server does is only a trunate - not a shrink.
I think you only option is to either run a trace to see what causes the
growth or e.g. run DBCC SQLPERF(LOGSPACE) every 5 minutes and drop the
result into a table. The latter might not tell you what's causing the
growth, but it might give you an indication of our suspicion that it's
actually a long running that are causing the growth, and that the tempdb
is being truncated fine afterwards, but just stays at the same physical
size.
Regards
Steen
| |
| Tom Moreau 2006-03-14, 7:23 am |
| Could you also run:
SELECT @@VERSION
I don't know if this is a SQL Serer bug or something else is going on here.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:8B600A3B-D1A1-4818-B41A- 66EB1F3EF05E@microso
ft.com...
Hi Tom
All 4 servers have recovery interval set to 0.
Cheers,
Matt
"Tom Moreau" wrote:
> Try running:
>
> sp_configure 'recovery interval'
>
> I'm wondering if it got set to something other than the default. If it's
> non-zero, I'd wonder why it was reset.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:7CAFED0F-4078-42CE-A79A- D082C3235244@microso
ft.com...
>
> I guess that's my point, it's never truncating. I can issue a checkpoint
> manually which frees up space in the file but it isn't doing this
> automatically - so the file continues to grow forever.
>
> Unfortunately because this happens sporadically (every week or two) and
> these are production boxes I can't really run a long term trace on them to
> see what's causing it.
>
> Thanks,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
|
| Here is the the result of @@version:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Cheers,
Matt
"Tom Moreau" wrote:
> Could you also run:
>
> SELECT @@VERSION
>
> I don't know if this is a SQL Serer bug or something else is going on here.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:8B600A3B-D1A1-4818-B41A- 66EB1F3EF05E@microso
ft.com...
> Hi Tom
>
> All 4 servers have recovery interval set to 0.
>
> Cheers,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
| Tom Moreau 2006-03-14, 1:23 pm |
| There may be a known bug. I went to Aaron's website:
http://aspfaq.com/SQL2000Builds.asp
.... and clicked on the link for KB909369:
http://support.microsoft.com/kb/909369
A hotfix is available.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:E29384C4-C9A8-466C-B5AD- 1A1FECCB42E9@microso
ft.com...
Here is the the result of @@version:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Cheers,
Matt
"Tom Moreau" wrote:
> Could you also run:
>
> SELECT @@VERSION
>
> I don't know if this is a SQL Serer bug or something else is going on
> here.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:8B600A3B-D1A1-4818-B41A- 66EB1F3EF05E@microso
ft.com...
> Hi Tom
>
> All 4 servers have recovery interval set to 0.
>
> Cheers,
> Matt
>
> "Tom Moreau" wrote:
>
>
>
| |
|
| Hi Steve
Thanks for your response. I am sure the file grows forever, once the first
autogrow happens, it never truncates again (unless I do it manually). For the
last few weeks we have been running dbcc sqlperf(logspace) and
fn_virtualfilestats every minute and dumping the results into a table for
analysis so we can see that the file is not being automatically truncated.
Cheers,
Matt
"Steen Persson (DK)" wrote:
> Matt wrote:
>
> Hi Matt
>
> Are you sure that the file "grows forever" ? Since you say it's
> happening occasionally, it could be that there are an operation in your
> DB that causes a big grow of the tempdb. If this operation causes the
> tempdb to grow to 30 GB, it can very well be that it's being truncated
> fine afterwards (both automatically and by you) but the physical file
> size will remains the same. As Tom mentions, truncate it not the same as
> shrink and what SQL server does is only a trunate - not a shrink.
>
> I think you only option is to either run a trace to see what causes the
> growth or e.g. run DBCC SQLPERF(LOGSPACE) every 5 minutes and drop the
> result into a table. The latter might not tell you what's causing the
> growth, but it might give you an indication of our suspicion that it's
> actually a long running that are causing the growth, and that the tempdb
> is being truncated fine afterwards, but just stays at the same physical
> size.
>
> Regards
> Steen
>
| |
|
| Hi Tom
We are capturing some metrics that alert us when it happens, but we're
unable to identify a pattern/catalyst. I guess my next options are either
open a case with PSS or rebuild the servers...
Cheers,
Matt
"Tom Moreau" wrote:
> To add to Steen's comments, you could also do a profiler trace that captures
> just the autogrow event. That won't have much impact on your server but it
> will at least tell you when something happened. That can help to narrow it
> down.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message
> news:ucl4c00RGHA.256@TK2MSFTNGP14.phx.gbl...
> Matt wrote:
>
> Hi Matt
>
> Are you sure that the file "grows forever" ? Since you say it's
> happening occasionally, it could be that there are an operation in your
> DB that causes a big grow of the tempdb. If this operation causes the
> tempdb to grow to 30 GB, it can very well be that it's being truncated
> fine afterwards (both automatically and by you) but the physical file
> size will remains the same. As Tom mentions, truncate it not the same as
> shrink and what SQL server does is only a trunate - not a shrink.
>
> I think you only option is to either run a trace to see what causes the
> growth or e.g. run DBCC SQLPERF(LOGSPACE) every 5 minutes and drop the
> result into a table. The latter might not tell you what's causing the
> growth, but it might give you an indication of our suspicion that it's
> actually a long running that are causing the growth, and that the tempdb
> is being truncated fine afterwards, but just stays at the same physical
> size.
>
> Regards
> Steen
>
>
| |
| Tom Moreau 2006-03-14, 8:23 pm |
| Try that KB article first. The hotfix may be the cure.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:D56CDEB5-6CFC-4763-B689- 3A442F6C5821@microso
ft.com...
Hi Tom
We are capturing some metrics that alert us when it happens, but we're
unable to identify a pattern/catalyst. I guess my next options are either
open a case with PSS or rebuild the servers...
Cheers,
Matt
"Tom Moreau" wrote:
> To add to Steen's comments, you could also do a profiler trace that
> captures
> just the autogrow event. That won't have much impact on your server but
> it
> will at least tell you when something happened. That can help to narrow
> it
> down.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message
> news:ucl4c00RGHA.256@TK2MSFTNGP14.phx.gbl...
> Matt wrote:
>
> Hi Matt
>
> Are you sure that the file "grows forever" ? Since you say it's
> happening occasionally, it could be that there are an operation in your
> DB that causes a big grow of the tempdb. If this operation causes the
> tempdb to grow to 30 GB, it can very well be that it's being truncated
> fine afterwards (both automatically and by you) but the physical file
> size will remains the same. As Tom mentions, truncate it not the same as
> shrink and what SQL server does is only a trunate - not a shrink.
>
> I think you only option is to either run a trace to see what causes the
> growth or e.g. run DBCC SQLPERF(LOGSPACE) every 5 minutes and drop the
> result into a table. The latter might not tell you what's causing the
> growth, but it might give you an indication of our suspicion that it's
> actually a long running that are causing the growth, and that the tempdb
> is being truncated fine afterwards, but just stays at the same physical
> size.
>
> Regards
> Steen
>
>
| |
|
| That looks as though it could be the ticket - I'll get in touch with PSS and
let you know how I get on.
Thanks so much for your help Tom, I've been tearing my hair out on this one!
Cheers,
Matt
"Tom Moreau" wrote:
[color=darkred]
> There may be a known bug. I went to Aaron's website:
>
> http://aspfaq.com/SQL2000Builds.asp
>
> .... and clicked on the link for KB909369:
>
> http://support.microsoft.com/kb/909369
>
> A hotfix is available.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:E29384C4-C9A8-466C-B5AD- 1A1FECCB42E9@microso
ft.com...
> Here is the the result of @@version:
>
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
> Cheers,
> Matt
>
> "Tom Moreau" wrote:
>
| |
| Tom Moreau 2006-03-14, 8:23 pm |
| Hey, I know the feeling.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:91E1A045-6173-4333-9452- 4EAB4E9EAEF8@microso
ft.com...
That looks as though it could be the ticket - I'll get in touch with PSS and
let you know how I get on.
Thanks so much for your help Tom, I've been tearing my hair out on this one!
Cheers,
Matt
"Tom Moreau" wrote:
[color=darkred]
> There may be a known bug. I went to Aaron's website:
>
> http://aspfaq.com/SQL2000Builds.asp
>
> .... and clicked on the link for KB909369:
>
> http://support.microsoft.com/kb/909369
>
> A hotfix is available.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:E29384C4-C9A8-466C-B5AD- 1A1FECCB42E9@microso
ft.com...
> Here is the the result of @@version:
>
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
> Cheers,
> Matt
>
> "Tom Moreau" wrote:
>
|
|
|
|
|