Growing tempdb problem
-
HELP!!!!!!
We have just switched our SQL Server to an all singing, all dancing new server. Performance hasn't been better, well, until today anyhow.
We are seeing the tempb database growing beyond all recognition, something we never saw on the old server.
If I watch the temp tables being created in tempdb (sitting there with an sp_tables and gratuitously pressing F5), I can see small temp tables come and go - and go they certainly do.
However, the space allocated to the tempdb is not shrinking, in fact, the log file continues to grow.
I'm having to run a BACKUP LOG WITH TRUNCATE_ONLY followed by a DBCC SHRINKDATABASE almost every hour to stop it from filling the hard drive.
Any suggestions, please, before I have to bring in my sleeping bag and start monitoring this thing 24/7 with no sleep!
---------------------------------------
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 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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply