Tempdb log not freeing up space in SQL 2000 SP4

Question

Tempdb log not freeing up space in SQL 2000 SP4

Shark Energy

SSCrazy Eights

Points: 8389
More actions
September 16, 2011 at 4:27 am

#247862

Any ideas on this?
Our tempdb size was regularly around 30gb. Suddenly it keeps ballooning to 150gb and killing the drive it sits on.
We have a job that runs and creates around 30gb of tempdb objects (expected). But the log is slowly climbing up each night (80gb, 100gb, 110, 120, 150, BOOM!) so it is asif the log is no longer freeing up the committed transations and is instead adding on top.
I have checked and there are no open transactions. The temp tables are disappearing and are marked # so are dropped when the job connection ends (we are going to explicitly drop them in code to be sure, but they are not listed under sysobjects once the job ends in a tracking job I run).
So I think I am missing something here. Tempdb just seems to be adding disk space when it should be reusing the empty space (spaceused actually seems to think there is only 5gb free of the current 95gb so something is stuck).
Any tips/help?
Is there a bug that causes this in SP4? We installed that in the last 2 months and this may have been happening since then. I have heard reference to it on some forums previously but cannot find a hotfix.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

do you have nightly maintainence jobs? index defrag/dbcc checkdb? are they scheduled to run in parallel? they can consume good amount of tempdb. also, once any data/log file reaches a particular size, it wont free up space on it's own.

Pradeep Singh

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 2

also, once any data/log file reaches a particular size, it wont free up space on it's own.

Can you elaborate on this please?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ps. (9/16/2011)
also, once any data/log file reaches a particular size, it wont free up space on it's own.

Errr, what???

Doesn't matter how large a log gets, assuming nothing is preventing log reuse (log backup, open transaction, replication) the space will be marked as reusable

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29422 More actions · Answer 4

Once a file reaches to a particular size, say 10 GB; even though the used space in the file is few MBs, the physical file size will remain at 10 GB. It will not release free space present within the file to operating system on it's own (unless you shrink or keep auto -shrink on - both are not a good option)

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 5

GilaMonster (9/21/2011)
ps. (9/16/2011)
also, once any data/log file reaches a particular size, it wont free up space on it's own.
Errr, what???
Doesn't matter how large a log gets, assuming nothing is preventing log reuse (log backup, open transaction, replication) the space will be marked as reusable

Thanks Gail. I was referring to space release to operating system (physical file size) not the reusable space within the file.

Bad English:sick:

Pradeep Singh

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 6

Yeah unless my English is bad I explained that in my original question but I will clear it up -

There is 1 job every night that uses probably 30gb of tempdb log space. But over the last few months the space doesn't seem to be reused anymore. I.e. if I reduce the physical size with a shrinkfile/db to 30gb., the size will continue to get physically bigger until maxing out the drive, rather than reusing the space that has been freed up within the file.

EDIT: In fact, reading my original post I made it very clear.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

ps. (9/21/2011)
GilaMonster (9/21/2011)
ps. (9/16/2011)
also, once any data/log file reaches a particular size, it wont free up space on it's own.
Errr, what???
Doesn't matter how large a log gets, assuming nothing is preventing log reuse (log backup, open transaction, replication) the space will be marked as reusable
Thanks Gail. I was referring to space release to operating system (physical file size) not the reusable space within the file.

Free space within the file will never be released to the OS unless someone actually shrinks the file. Again, it has nothing at all to do with the size of the file. The behaviour is the same for any size of file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Shark Energy (9/21/2011)
There is 1 job every night that uses probably 30gb of tempdb log space. But over the last few months the space doesn't seem to be reused anymore. I.e. if I reduce the physical size with a shrinkfile/db to 30gb., the size will continue to get physically bigger until maxing out the drive, rather than reusing the space that has been freed up within the file.

TempDB is in simple recovery, so there's no worry about log backups, and you can't replicate TempDB either.

So... Active transactions is likely.

DBCC OPENTRAN

Also monitor DBCC SQLPERF(LogSpace) and see if a manual CHECKPOINT makes any difference.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 9

Thanks Gail. Had already done all of that. It still grows now even with explicit dropping of the # temp tables (not needed).

Every day it is back to just 5gb in use but it keeps growing. I'm almost 100% convinced we have hit a SQL 2000 SP4 bug (hope not!)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Monitor SQLPerf(LogSpace). That will give you a view of used space.

If it keeps growing check open transactions (while it's growing) and see if a checkpoint helps.

Edit: Additionally, check DBCC LOGINFO. It'll show you what VLFs are active and inactive, so you can see where the oldest active one is and if it remains active (indicating that something is holding the log active) or if it moves (indicating you just have massive TempDB log usage)

I have never seen a log file grow without a reason for the growth. Often hard to find, but there's always been a reason.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

GilaMonster (9/21/2011)
Monitor SQLPerf(LogSpace). That will give you a view of used space.
If it keeps growing check open transactions (while it's growing) and see if a checkpoint helps.
I have never seen a log file grow without a reason for the growth. Often hard to find, but there's always been a reason.

I had a very similar issue in sql 7 RTM.

A single query with 2-3 joins (1 of them to a big view) was building a whacky intermediary table and it blew temp db every time.

I had developped the qry on sql 2k8 (for intellisense), then I was moving it to prod for final testing where it promtly started to fail & kill the server 20-30% of the time.

The query was really small. Less than 15K rows processed to return only 15-20 rows.

Don't assume here, any small query could be the culprit.

You can use the default trace to see at what time the db starts growing and then you can zoom in on the jobs that run at that time to find the guilty.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Ninja's_RGR'us (9/21/2011)
You can use the default trace to see at what time the db starts growing and then you can zoom in on the jobs that run at that time to find the guilty.

On SQL 2000?

Trace, yes. Default trace, no.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

GilaMonster (9/21/2011)
Ninja's_RGR'us (9/21/2011)
You can use the default trace to see at what time the db starts growing and then you can zoom in on the jobs that run at that time to find the guilty.
On SQL 2000?
Trace, yes. Default trace, no.

Doh! :pinch:

see how fast you forget the previous limitations when you have a new tool :-).

Thanks for the edit Gail!

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 14

This is fixed for anyone that comes across the post. Issue in SQL 2000 SP4 if you are using a SAN/iScsi drive that loses sight temporarily. The log stops freeing up space and you need to stop and start the SQL Server service to clear tempdb down. Then the normal behaviour returns.