So I have a database that is acting weird. I am watching all activity on the server, and the tempdb is constantly growing. It has grown by 30gb in about 45 minutes. I keep checking the allocated space in the tempdb, and it is always about 8mb. I know that it is not needing all the space it is allocating, I have watched 1 transaction happening with the tempdb essentially empty and it still growing. It appears to me that the engine instead of using previously allocated space is instead choosing to use more of the hard drive space. I noticed our tempdb was extremely large earlier today and restarted SQL, that brought the tempdb down in size to a good size, but it has been growing again ever since, and constantly restarting SQL is not an option as this is a production environment. I have limited hd space on this server so I need to keep the tempdb at a reasonable size.
+1
A:
Have you done an analysis on the scripts that are running? Have you used the profiler to determine SQL activity?
My first thoughts are scripts using temp tables (#table) and a possible Cartesian Product join?
As a note the tempdb is recreated on startup of SQL server so thats why it will be truncated when you restart the service.
Wayne
2009-04-23 23:17:44
None of the databases that I have control over use temp tables. The only Cartesian joins that I know of are run once a week. I am running the profiler now to try to figure out what is going on.I knew that it truncates the tempdb on startup. We restarted it specifically for that because we were out of hard drive space.
2009-04-23 23:26:09
Ok, lets wait for your profile to complete and maybe that will offer some insight as to what is going on. Have you run any DMV's?
Wayne
2009-04-23 23:36:50
Also joins, sorting (order by) and certain dbcc commands use the tempdb for storage to create the result set. Look for these in your profile output too.
Wayne
2009-04-23 23:47:25
Well, it was figured out that one of the jobs had a step that had a lot of weird data manipulations in it that is eating up the tempdb. Once this is fixed everything should work again. Thanks for the help.
2009-04-24 00:49:19
Glad the problem was found. Should mark this as resolved if that is the case.
Wayne
2009-04-24 03:40:27