Tempdb is growing bigger
-
Hi,
My Tempdb grows bigger everyweek.
Is there any way to find out the cause? How can I avoid this problem?
Thanks,
R.Prakash
Kindest Regards,R
-
Hello Prakash,
The way to find out why your tempDB is growing is to use the profiler to create a trace you can use to monitor it.
The tempDB is used as general working space by the Sql Server. All temp tables, sort operations (order by and joins) and workspaces required by the service are actualized in the tempDB.
If it is growing you are probably using these types of objects. It is possible that through poor application design objects can be orphaned and the space not released. But in general tempDB will grow to support the types of activity in your server.
I recommend you monitor the tempDB file sizes during a normal working cycle and then set it to the max size it autogrows to plus a percentage for fudge factor.
Leave autogrow turned on, but set it to some finite megabyte limit (100 or 200 MB for example.) Autogrowing any database by the default 10% only works until it gets to about 2 or 3 GB. Any larger and you can start getting timeouts.
If the tempDB is still growing beyond what you feel is appropriate or filling your disk space you may need to move it to its own disk. Because of what it is used for this is a good general recommendation on a busy system anyway. In some cases you can increase the number of users your server can support by increasing concurrency on tempDB. Another thing you can do is to create a job that will run periodically to shrink the tempDB back to the size you had set earlier. What period this should run on will depend on your environment.
Hope this answers your question and that it helps.
If you have additional questions about this reply directly to me. Unfortunately, I don't get to read the forums every day.
Richard L. Dawson
Microsoft Sql Server DBA/Data ArchitectI can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
-
Thanks Richard!
I have created a job to run shinkdb periodically.
I have one doubt. I am having union views on cross databases. Will it be a problem? I could find many a stored procedures use Temporary Tables and Table Variables. Will it cause TempDB to grow enormously?
R.Prakash.
Kindest Regards,R
-
Prakash,
In regard to your last question, as Richard mentioned, various operations use tempdb. What determines if tempdb grows in size is not so much that you are doing these operations, but rather the size of the tables and datasets, both in number of rows and size in bytes. So for example if you are doing a sort (ORDER BY) on a table of 10,000 rows it may not have much of an impact on tempdb's size. But the same operation on a table of 2,000,000 rows would have a much greater impact. And sub-queries and other more complex queries will compound the effect.
Paul
- Paul
http://paulpaivasql.blogspot.com/
-
Tempdb fills up when there are low on disk space, or you have set an unreasonably low maximum size for a database growth.
Usually people think that tempdb is only used for ## tables. The fact is you can easily fill up tempdb without ever creating a single temp table. These scenarios can cause tempdb to fill up:
- Any sorting that requires more memory than has been allocated will use tempdb.
- DBCC CheckDB('All database') perform it's work in Tempdb Database.
- DBCC DBREINDEX or similar DBCC will fill up your Tempdb.
Use the command DBCC SHRINKFILE to shrink the individual tempdb files:
use tempdb
go
dbcc shrinkfile (tempdev, 'target size in MB')
go -- this command shrinks the primary data file
dbcc shrinkfile (templog, 'target size in MB')
go -- this command shrinks the log file.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply