Growing tempdb problem
-
A bit more information.
We've isolated part of the problem.
One of our programs uses local temporary tables to help reduce the data sets needed for comparisons. The system has work efficiently on the old server, it has DROP TABLE commands implicitly coded to drop the temp tables when they are no longer used. We have watched the tempdb and the tables are in fact being dropped, but the space the temp tables used is not being released. I'm not talking about the tempdb not shrinking back and releasing unused space, instead, the temp table is being dropped, but the log file is still showing the space as being used !
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion. -
A couple questions:
Is the space actually showing as 'used' or is it showing as 'allocated'?
If allocated, do you see the size of the temp table(s) created getting larger (perhaps exponentially) with each iteration of the program that creates it(them)?
SJT
-
This is the problem, the log space is showing as being 'used'.
We are watching temporary tables being created and dropped, and the 'used' data size of the tempdb is growing and shrinking as you'd expect. But the 'used' log file is continually growing.
On a hunch I've forced SQL to move the tempdb last night, and this morning, stopped the server, deleted the files and restarted it in the hopes that it is a glitch.
Whatever the outcome, I'll post up here, but if anyone has any suggestions I'd be very greatly appreciated.
I've noticed on other sites that other people have experienced this problem, and all they are told is to run a BACKUP LOG and DBCC SHRINKDATABASE, but if this problem persists over the weekend I can see me having to set an hourly job in place to do just this to keep it in check
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion. -
Sorted, looks like there was a glitch in the transaction log. Destroying the tempdb and recreating it has solved the problem.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply