July 9, 2007 at 4:46 am
Hi,
I have a tempdb who's log is continuously growing (at a slow rate) for a few weeks now. I have tried checking wherether there are any open transactions (which there isn't on the tempdb or any of the DBs) and I have checked for locks (sp_lock) and traced what they are via sysproceses and inputbuffer, it has not given me the best of info as there seems no long running spids.
I have ran DBCC Log info on the TempDB and every row the status is showing 2, meaning active. As far as I know all temp tables should be dropped after they have been used. It is really puzzling me as this has only just started to happen recently after being in production for over a year.
The database is pretty much constantly in use and there are a lot of stored procedures that use temp tables (some unavoidingly). I could manually truncate the log but I see that as a messy fix. Is there any reason that I'm missing as to why the checkpoint is not happening on TempDB?
Thanks.
July 9, 2007 at 5:19 am
Do you mean to say that tempdb is continously growing in size? You can schedule a shrinkdatabase or shrinkfile job against tempdb to clear the unused space.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 9, 2007 at 5:23 am
Hello James,
There can be other reasons for the tempdb growth. I will insist to check all your database queries.
Extensive use of group by or order by can also lead to tempdb dabase growth. It seems like you application which is using the database is retriving some heavy datasets. This can cause.
If this is the normal growth, then I will recommend to buy more hard drive for ur server because the Tempdb needs more room to grow. But if it happened just for a day then diagnose the issue. May a bad query or a job which is running from the server! Shrugs 😐
Thanks
Sachin
July 9, 2007 at 5:35 am
Thanks for the reply Sachin,
Yes it is just the Log of the TempDB that is growing and it has only just recently started, but it is now grown continuously for the last 2 weeks.
Id rather find the cause before I schedule the truncate statement, as the tempdb is in simple mode and should be checkpointing.
What I'm after is more transparency in the tempdb, to find out what is not releasing the log in order to let the checkpoint happen.
The databases are not that big on the server but the activity is high but not really excessive but rather constant.
What is perculiar is that we have mirror dbs at different sites (totally independant (no replication involved etc..) and all have the same connections and 2 out of the 3 seem to have inherited the problem, albeit at different extents.
How we became aware of this is that there was a job that was heaviliy dependant on tempdb, and because the tempdb was constantly 'locked' the db log filled. Since, the job has not run and the tempdb still grows.
July 9, 2007 at 6:03 am
Then James,
As you said it was fine before, it might be because of some error in the application... which is causing the same process to re-execute again and again.
Or
new enahancements deployed on application or database?
I will strongly recommend to read this article before you proceed.
http://support.microsoft.com/kb/110139
To fix this,
1) You can turn the database to single user mode and then shrink it.
Don't try to shrink the database when its online and getting used by other processes. You will endup corrupting the TempDB database.
read this article carefuly.
2) Or the easiest way is to restart the database services.
Tempdb is recreated everytime you restart the database server.
You can also check this link to do a quick fix
http://support.microsoft.com/default.aspx/kb/307487
Regards
Sachin
July 9, 2007 at 6:21 am
Thanks satchin I appreciate your advice,
I can truncate the log file, which would work, its already been a scheduled job on one of the dbs (every 4 hours) just to keep it under control. What I'd like to do though, is whilst I know it is still growing is find out which is the guilty spid(s) so that I can stop this from happening in the future. Im after a command like sp_lock that can give me a clearer picture of whats got hold of the TempDB. Then we can fix the app or db.
As far as I know, the tempdb is volatile and transactions only exsist as long as a connection is open. There are .Net apps connected to the DB and although the app is stopped the app still caches the connection. What I would expect is that if I stopped every app writing to the TempDB, the log should just clear. Meaning, that it is not clearing beacause one of the connections is locked open.
July 9, 2007 at 7:36 am
But you will be able to shrink only the unused log file space if suppose the log file is being heavily used and if you are not able to shrink then plan your course.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 9, 2007 at 7:48 am
I am not wanting to shrink the file, that is a completely different task and I have already truncated the log. I am after ideas on trying to track down where the problem is and try to resolve it. I cannot believe that a log file that is in 'simple' mode should grow continuously for a period of 2 weeks. Truncating the log would just mask the issue.
July 10, 2007 at 12:03 pm
James, if you're not a 24/7 shop (or in your next maint window), how about stopping and restarting the instance to reset tempdb, then have SQL Profiler monitor the heck out of it?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 11, 2007 at 10:31 pm
At my old job we used to have problems with TempDB log continuously growing. We never got to the bottom of it, but it only started happening after we moved to SP4.
The log would grow to 10gb or so and then run out of disk space, and we'd have to restart SQL.
We found truncating the log didn't help. We had to restart SQL at the end of the day once we noticed the tempdb log growing. We had autogrow set to 10% and once the log got above 5gb or so the server would almost grind to a halt when it tried to expand the log each time (allocate another 500mb+...)
Similar to what you said, there were no active transactions or anything. Everyone we talked to said it wasn't possible, because tempdb was in simple recovery.
July 12, 2007 at 7:04 am
Sounds like the same symptoms. There must be a way to find or trap the problematic code
July 13, 2007 at 12:48 am
I might be sending you on a wild-goose hunt, but it sounds like your application might not be closing its database connections properly...
July 13, 2007 at 6:59 am
August 9, 2007 at 10:09 am
I have an instance that has developed the exact same symptoms described here. No open transactions. I can manually truncate the tempdb log, but this thing is in simple mode. I have another instance on the same server and two more instances on a different server that support the same application in the exact same manner. None of these other instances are having any issue
This problem started manifesting itself yesterday. Everything looks as normal. No funny looking transactions or transactions "hanging around". I used DBCC opentran on every database on this instance and there are no open transactions. This should be the only thing that can keep the tempdb log from truncating on checkpoint. If there were open transactions, I would not be able to manually truncate the log either.
Has anyone found a culprit for this yet? I truncate and shrink... the log is back to 3 GB and rising within a couple of hours. The tran log on other instances never grows over 300 MB. What gives??
August 10, 2007 at 8:01 am
This may help
USE
TEMPDB
GO
SELECT
sys.tables.NAME,SYSINDEXES.ROWCNT FROM sys.tables
LEFT
JOIN sysindexes
ON
sys.tables.OBJECT_ID = sysindexes.ID
WHERE
ROWCNT > 0 AND rowcnt IS NOT NULL
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply