What activity that cause tempdb grow

Posted on 2007-04-04
Last Modified: 2008-01-09
What is the activity causing the tempdb grow?? as I know creating index is one of them
Question by:motioneye
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 75

Accepted Solution

Aneesh Retnakaran earned 150 total points
ID: 18849870
-temp tables , table variables
-GROUP BY / ORDER BY operations
- Index creations
- DBCC commands
LVL 28

Assisted Solution

imran_fast earned 150 total points
ID: 18849877
Creating index.
Bulk Insert
Update statement updating huge number of records.
Creation of temparary # tables.
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18849896
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 200 total points
ID: 18849986
<<-temp tables , table variables
-GROUP BY / ORDER BY operations
- Index creations
- DBCC commands >>

You may add to the above
--> Row Versionning
--> Complex calculations

Hope this helps...

Author Comment

ID: 18855555
what is that mean by Row Versionning?
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 200 total points
ID: 18856665
<<what is that mean by Row Versionning?>>In SQL 2005, MS has implemented ORACLE's read consistency that allows to make sure that at any time the user gets access the last commited version of the data.  That prevents from having users waiting for an update transaction to be committed before they can actually make a select.  To implement that functionnality, SQL Server now perpetually keeps updated the last commited version of data in tempdb.  As a result, tempdb gets a big hit in terms of IO's because it supports all select's instead of the original tables.  It is one of the factors that may strongly influence the growth of temdb.

Hope this helps...

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Star schema daily updates 2 38
SQL Query Task 11 44
user database (login sql or login windows) 3 30
MS SQL Server Management Studio R2 4 32
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question