July 14, 2009 at 11:59 pm
Hi Expertz,
I have a scheduled job that contains
USE master
DBCC SHRINKDATABASE ('tempdb',0)
as one of the steps when i check the log i find that this step executed successfully.
But when i check i find that only templog file to be shrunk and not the tempdev the data file.
but when i check the tempdb space used
USE tempdb
GO
EXEC sp_spaceused
i get the attached result.
What should i do to shrink the tempdb?
Since its in a scheduled task i dont want to restart the server each time the tempdb files fill up.
What do you think would have caused the tempdb to grow so much.........
Please help..........
Tanx 😀
July 15, 2009 at 6:30 am
Try with shrinkfile:
USE TempDB
DBCC SHRINKFILE('tempdev', 0)
If it does not work, the TempDB contains objects that are in use by some connection or were not deleted (temp tables for instance).
For your reference try reading this:
http://support.microsoft.com/kb/307487/en-us
Regards
Gianluca
-- Gianluca Sartori
July 15, 2009 at 10:40 pm
Ya had tried that
I killed the processes accessing the tempdb and the shrunk the data file...
And that worked....
Is this the case for every other databases should we kill the processes before shrinking the files..............
Tanx 😀
July 15, 2009 at 11:18 pm
The question to ask yourself is why are you shrinking tempdb? If it grows every day, and you shrink it with a job, you're wasting cycles and disk I/O. Tempdb should be set to the size needed for your server and it shouldn't need to grow or shrink on a regular basis.
July 15, 2009 at 11:22 pm
Do you really need to shrink TempDB at all?
It has grown because the SQL Server database engine needs to use, either because of explicit temporary objects or because of implicit ones as a result of query joins, sorts, etc. If you shrink it then re-run the same set of queries it's only going to grow again to the previous size.
The total size of the TempDB data files (the current recommendation from Microsoft is one per processor core up to a maximum of 8) should be set to as large as you expect it to be, so that the files are pre-allocated each time SQL Server starts. If you find you've underestimated the size and they're still growing then increase the defined size of the files.
The only time I'd consider shrinking TempDB would be if I migrated significant data and/or databases off the server and never expected the remaining databases' use/growth to get up to that approximating the previous situation prior to the data/database migration.
July 16, 2009 at 1:15 am
The issue was caused by a an sp that was running for a long time which did some indexes and some sortings....
Couldn't figure out what exactly it does...
But once i killed the process running the sp i was able to shrink the tempdb file.
Tanx 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply