MS SQL Server - TempDB Log Growth

Author

TempDB Log Growth

cbrichards

2006-12-13, 7:12 pm

We are running SQL 2000, SP4, on Windows 2003.

We have about 150 databases spread across about 5 servers. These databases
are identical, except may differ in size, depending on the client. Yet, each
server is load balanced.

Recently, without any known changes on the server (let's call it Server 1),
the tempdb log file, which had been set at 1500 MB, and had been that way for
several months, began growing throughout the day, and when it reached 12 GB,
and some of our monitoring alarms went off, we performed a shrink. Keep in
mind, that if any changes were made to the server, then those same changes
would have been made to the other 4 servers as well. All the other 4 servers
live happily with a TempDB Log size of 1500 MB.

These same symptoms are now occuring daily, where there is gradual growth
throughout the day (TempDB is set to auto grow by 10%).

Our historical counter data does not show any great change in IO, CPU, or
Memory usage.

DBCC OpenTran shows no open transactions.

I have run SQL Profiler to try to determine anything abnormal on reads,
writes, duration, but nothing stands out. I also had Perfmon running to track
when the Logfile grows, matching this up to the Profiler trace, and there
were no transactions out of the norm that caused the Logfile to grow, when it
did grow.

We have stopped and restarted MSSQLServer service, but the TempDb Log still
continues it steady growth, unlike the other 4 servers whose Load, IO,
hardware and memory are all close to the same. These other 4 servers have
TempDB Log set at 1500 MB, just like Server 1 had its TempDB Log set for 1500
MB for several months, before it began to exhibit this strange behavior.

I came across the following link (KB article) which may hold some value:

http://www.kbalertz.com/Feedback_909369.aspx

In light of the above link, is there a way to determine if the checkpoint
process is indeed working?

Also, running dbcc loginfo(tempdb) pretty much shows all returns with a
Status = 2. But it does shrink without fail to whatever size we specify down
to 1500 MB.

Periodically I run the following, but the returns are similar to the other 4
servsers:

SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC

Our indexes are identical across all databases on all servers, and they have
all been rebuilt, and have even had statistics updated with FULLSCAN.

Any other ideas on how to troubleshoot this behavior before implementing the
HotFix recommended in the link above would be appreciated.

FYI...I have read the following two links often recommended on these type of
threads, but they do not address the troubleshooting aspect, although I want
to come to a resolution and stop the shrinking.

http://www.aspfaq.com/2446
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200612/1

Uri Dimant

2006-12-13, 7:12 pm

Hi
http://sqlserver2000.databases.aspf...-happening.html

"cbrichards" <u3288@uwe> wrote in message news:6aa82bd34f625@u
we...
> We are running SQL 2000, SP4, on Windows 2003.
>
> We have about 150 databases spread across about 5 servers. These databases
> are identical, except may differ in size, depending on the client. Yet,
> each
> server is load balanced.
>
> Recently, without any known changes on the server (let's call it Server
> 1),
> the tempdb log file, which had been set at 1500 MB, and had been that way
> for
> several months, began growing throughout the day, and when it reached 12
> GB,
> and some of our monitoring alarms went off, we performed a shrink. Keep in
> mind, that if any changes were made to the server, then those same changes
> would have been made to the other 4 servers as well. All the other 4
> servers
> live happily with a TempDB Log size of 1500 MB.
>
> These same symptoms are now occuring daily, where there is gradual growth
> throughout the day (TempDB is set to auto grow by 10%).
>
> Our historical counter data does not show any great change in IO, CPU, or
> Memory usage.
>
> DBCC OpenTran shows no open transactions.
>
> I have run SQL Profiler to try to determine anything abnormal on reads,
> writes, duration, but nothing stands out. I also had Perfmon running to
> track
> when the Logfile grows, matching this up to the Profiler trace, and there
> were no transactions out of the norm that caused the Logfile to grow, when
> it
> did grow.
>
> We have stopped and restarted MSSQLServer service, but the TempDb Log
> still
> continues it steady growth, unlike the other 4 servers whose Load, IO,
> hardware and memory are all close to the same. These other 4 servers have
> TempDB Log set at 1500 MB, just like Server 1 had its TempDB Log set for
> 1500
> MB for several months, before it began to exhibit this strange behavior.
>
> I came across the following link (KB article) which may hold some value:
>
> http://www.kbalertz.com/Feedback_909369.aspx
>
> In light of the above link, is there a way to determine if the checkpoint
> process is indeed working?
>
> Also, running dbcc loginfo(tempdb) pretty much shows all returns with a
> Status = 2. But it does shrink without fail to whatever size we specify
> down
> to 1500 MB.
>
> Periodically I run the following, but the returns are similar to the other
> 4
> servsers:
>
> SELECT OBJECT_NAME(id), rowcnt
> FROM tempdb..sysindexes
> WHERE OBJECT_NAME(id) LIKE '#%'
> ORDER BY rowcnt DESC
>
> Our indexes are identical across all databases on all servers, and they
> have
> all been rebuilt, and have even had statistics updated with FULLSCAN.
>
> Any other ideas on how to troubleshoot this behavior before implementing
> the
> HotFix recommended in the link above would be appreciated.
>
> FYI...I have read the following two links often recommended on these type
> of
> threads, but they do not address the troubleshooting aspect, although I
> want
> to come to a resolution and stop the shrinking.
>
> http://www.aspfaq.com/2446
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200612/1
>

Hari Prasad

2006-12-13, 7:12 pm

Hello,

Looks like the automatic check point is not occuring in Tempdb. This might
cause the log file to grow a lot. Mostly this will happen if you do a bulk
isert/update/delete operation
and some time SQL Server engine will stop the automatic checkpoint for the
database you ran the bulk operation. Take a look into the below URL:-

"cbrichards" <u3288@uwe> wrote in message news:6aa82bd34f625@u
we...
> We are running SQL 2000, SP4, on Windows 2003.
>
> We have about 150 databases spread across about 5 servers. These databases
> are identical, except may differ in size, depending on the client. Yet,
> each
> server is load balanced.
>
> Recently, without any known changes on the server (let's call it Server
> 1),
> the tempdb log file, which had been set at 1500 MB, and had been that way
> for
> several months, began growing throughout the day, and when it reached 12
> GB,
> and some of our monitoring alarms went off, we performed a shrink. Keep in
> mind, that if any changes were made to the server, then those same changes
> would have been made to the other 4 servers as well. All the other 4
> servers
> live happily with a TempDB Log size of 1500 MB.
>
> These same symptoms are now occuring daily, where there is gradual growth
> throughout the day (TempDB is set to auto grow by 10%).
>
> Our historical counter data does not show any great change in IO, CPU, or
> Memory usage.
>
> DBCC OpenTran shows no open transactions.
>
> I have run SQL Profiler to try to determine anything abnormal on reads,
> writes, duration, but nothing stands out. I also had Perfmon running to
> track
> when the Logfile grows, matching this up to the Profiler trace, and there
> were no transactions out of the norm that caused the Logfile to grow, when
> it
> did grow.
>
> We have stopped and restarted MSSQLServer service, but the TempDb Log
> still
> continues it steady growth, unlike the other 4 servers whose Load, IO,
> hardware and memory are all close to the same. These other 4 servers have
> TempDB Log set at 1500 MB, just like Server 1 had its TempDB Log set for
> 1500
> MB for several months, before it began to exhibit this strange behavior.
>
> I came across the following link (KB article) which may hold some value:
>
> http://www.kbalertz.com/Feedback_909369.aspx
>
> In light of the above link, is there a way to determine if the checkpoint
> process is indeed working?
>
> Also, running dbcc loginfo(tempdb) pretty much shows all returns with a
> Status = 2. But it does shrink without fail to whatever size we specify
> down
> to 1500 MB.
>
> Periodically I run the following, but the returns are similar to the other
> 4
> servsers:
>
> SELECT OBJECT_NAME(id), rowcnt
> FROM tempdb..sysindexes
> WHERE OBJECT_NAME(id) LIKE '#%'
> ORDER BY rowcnt DESC
>
> Our indexes are identical across all databases on all servers, and they
> have
> all been rebuilt, and have even had statistics updated with FULLSCAN.
>
> Any other ideas on how to troubleshoot this behavior before implementing
> the
> HotFix recommended in the link above would be appreciated.
>
> FYI...I have read the following two links often recommended on these type
> of
> threads, but they do not address the troubleshooting aspect, although I
> want
> to come to a resolution and stop the shrinking.
>
> http://www.aspfaq.com/2446
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200612/1
>

Hari Prasad

2006-12-13, 7:12 pm

Hello,

Looks like the automatic check point is not occuring in Tempdb. This might
cause the log file to grow a lot. Mostly this will happen if you do a bulk
insert/update/delete operationand some time SQL Server engine will stop the
automatic checkpoint for the
database you ran the bulk operation. Take a look into the below URL:-

http://support.microsoft.com/kb/909369/en-us

Thanks
Hari

>
>
>
>
> "cbrichards" <u3288@uwe> wrote in message news:6aa82bd34f625@u
we...
>
>

Sponsored Links