Archive for the ‘TemDb Problems’ Category

Tempdb Growing unexpectadly with recovery model Simple

Short-term fix Restarting SQL Server will re-create tempdb from scratch, and it will return to its usually allocated size. In and of itself, this solution is only effective in the very short term; assumedly, the application and/or T-SQL code which caused tempdb to grow once, will likely cause it to grow again. To shrink tempdb, you can consider using DBCC ShrinkDatabase, DBCC ShrinkFile (for the data or the log file), or ALTER DATABASE. See KB #256650, KB #272318 and KB #307487 for more information. If you can’t shrink the log, it might be due to an uncommitted transaction. See if you have any long-running transactions with the following command:


Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with ‘SPID (Server Process ID) : <number>’). Use that <number> in the following:


This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine if you want to end this process with:

Categorias:TemDb Problems