How to prevent tempdb database grows so large


Interview questions

SQL Server - How to prevent tempdb database grows so large.

Next>>         SQL Server tutorial  

SQL Server - How to prevent tempdb database grows so large. - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

tempdb database grows so large that it runs out of disk space. What causes this and how can I prevent it?

tempdb is a system database and acts as a global resource which is available to all users connected to SQL Server. It is used to store user objects, internal objects and version stores. Tempdb fills up if there is not much free disk space or the data base has a unreasonably low maximum size for database growth.

Tempdb call fill up by:

  • Sorting that needs more memory than what’s allocated to SQL Server.
  • Large result sets including UNIONS, ORDERBY, GROUPBY clauses.
  • Orphaned objects

Tips to prevent tempdb to go out of space:

  • Set tempdb to auto grow.
  • Ensure the disk has enough free space
  • Set it’s initial size reasonably
  • If possible put tempdb on its separate disk.
  • Batch larger and heavy queries
  • Try to write efficient code for all stored procedures, cursors etc.

SQL Server - How to prevent tempdb database grows so large. - May 05, 2009 at 22:00 PM by Rajmeet Ghai

tempdb database grows so large that it runs out of disk space. What causes this and how can I prevent it?

Most of the times the query design is responsible for increase in size of tempdb database. The query returns a large amount of rows when no predicate or where clause is specified. Often, the server database is open to multiple users to fire queries. This also causes the disk space to run out of memory. To avoid this, either the number of users can be limited to hit the server or bad queries should be avoided. Bad queries can be avoided by limiting the size of tempdb database. It is better to let the users query fail rather than stressing the server to execute bad queries. The temp database should be ideally pre-sized. This will avoid it to auto grow when not in use.

SQL Server - How to prevent tempdb database grows so large. - June 21, 2009 at 09:00 AM by Amit Satpute

tempdb database grows so large that it runs out of disk space. What causes this and how can I prevent it?

  • Mostly a poorly designed query is the reason for causing the tembdb to increase.
    The tempdb database can be used directly via Transact-SQL or indirectly by ORDER BY or GROUP BY clauses.
  • The query causing the problem should be identified. (Profiler can be used to capture the data and analyze it)
  • Another reason the tempdb can grow is due to a query returning heavy data.
    Usually the WHRE clauses are not specific enough in this case.
  • In cases where the number of queries cannot be controlled, the size of the tempdb can be set and limited. This causes a bad query to run out of resources and terminate.


Also read

SQL Server system database

Master Database MSDB Database, TEMPDB Database, Model Database.........

What are the ways to code efficient transactions?

Answer - Database users can have permission to execute a stored procedure without being....

Define temporary and extended stored procedure.

Answer - Temporary Stored Procedure is stored in TempDB database. It is volatile and is deleted once connection gets terminated or server is restarted......

How do you restore the master database on a SQL Server cluster?

Restoring a master database on a SQL server cluster is required in case of a disaster when the previous backup is required. Ideally, restarting the server in single user mode can resolve this problem...........

Describe in brief system database.

Answer - Master database is system database. It contains information about server’s configuration. It is a very important database and important to backup Master.....

Write your comment - Share Knowledge and Experience


Latest placement tests
Latest links
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring