April 9, 2004 at 4:47 am
Hi!
Today I had tempdb log growing to very large size (~17GB). When I asked one of the developers to close QA with a script that finished yesterday, the Percent Log Used counter for tempdb dropped from 99% to 5%. This was just a guess of who could do this. Is there a way to determine what process is responsible for tempdb log growth with a more "scientific" approach?
Thanks.
April 9, 2004 at 6:26 am
There are a LOT of things that cause TEMPDB to grow. That's it's purpose in life.
It's used for temporary tables explicitly created by users, for worktables to hold intermediate results during query processing and sorting; among other things. Operations with tempdb are logged so the transactions on temp tables can be rolled back.
Stopping and starting SQL Server will shrink the TEMPDB.
-SQLBill
April 9, 2004 at 7:57 am
This time it was because of lack of commit statement or something like that, cause the script finished yesterday, but the process was still holding data in tempdb log and for some reason making it to grow. Right after the QA window was closed, most of 17GB was freed. I am trying to figure out how to identify such processes.(stopping MS SQL Server was NOT a choice)
April 9, 2004 at 10:10 am
You can write a script that runs continuously, basically something to read sp_who2 and watch the last batch and the CPU and/or diskio values. If they are growing beyond some threshhold, alert you (page, email,whatever). You'd have to play with the values.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply