Solved

tempdb has grown to 1GB when it never did that before in SQL Server 2000

Posted on 2012-04-11
6
644 Views
Last Modified: 2012-05-22
Hi all.

I noticed our tempdb has grown to 1GB when it never did that before in SQL Server 2000. I shrunk it to 10MB yesterday and today it grew to 1GB again. This happened all of a sudden. Any ideas what can be happening or how I can find out what's making it grow so much?

Thank you in advance!
0
Comment
Question by:printmedia
6 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 37832511
are you making large temporary tables or table variables?

It can also grow as the result of queries involving many rows - cartesian products of joins between several tables
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 37832584
Hi Print Media,
What version of SQL Server are you using?
In SQL Server 2008 you can set up Alerts and one of the types of alerts is for database size.
So you could create an alert to automatcially get an e-mail when the the size of tempdb suddenly goes above a certain threshold(maybe set this at 1GB for your case)
So when this sudden growth occurs you will be alerted immediately and then at exactly that point in time you can check was is being run on SQL Server(maybe use Profiler)
It could be a large query that is runnning or some report or something.

There are 3 areas of usage with regards to tempdb - 1. User 2. Internal 3. Version Store

User objects : Mainly meant for storing local and global temp tables and
table variables.
Internal objects : Worktables created for order by /sort /group by
operators/ hash algortihms etc.
Version store : Version store is mainly used at two scenarios. They are
* When indexes are built online, version store when
keeps multiple versions of the same row.
* When the database has snapshot isolation enabled
and if any transaction runs at snapshot isolation
or RCSI

The following query will help get the information to determine what category of usage is highest.
SELECT SUM (user_object_reserved_page_count) *8 AS usr_obj_kb ,
SUM (internal_object_reserved_page_count)*8 AS internal_obj_kb ,
SUM (version_store_reserved_page_count) *8 AS version_store_kb,
SUM (unallocated_extent_page_count) *8 AS freespace_kb ,
SUM (mixed_extent_page_count) * 8 AS mixedextent_kb
FROM sys.dm_db_file_space_usage

This will help you identify if it is use of temp tables/table variables or if it is possibly queries that is the culprit for your temp db  growing lare suddenly.

The following query will return information about tempdb space allocation relating to sessions and programs

SELECT
      sys.dm_exec_sessions.session_id as [Session ID],
      DB_NAME(database_id) as [Database Name],
      host_name as [System Name],
      program_name as [Program Name],
      login_name as [User Name],
      status,
      cpu_time as [CPU Time (in milisec)],
      total_scheduled_time as [Total Scheduled Time (in milisec)],
      total_elapsed_time as    [Elapsed Time (in milisec)],
      (memory_usage * 8)      as [Memory Usage (in KB)],
      (user_objects_alloc_page_count * 8) as [Space Allocated for User Objects (in KB)],
      (user_objects_dealloc_page_count * 8) as [Space Deallocated for User Objects (in KB)],
      (internal_objects_alloc_page_count * 8) as [Space Allocated for Internal Objects (in KB)],
      (internal_objects_dealloc_page_count * 8) as [Space Deallocated for Internal Objects (in KB)],
      case is_user_process
            when 1      then 'user session'
            when 0      then 'system session'
      end         as [Session Type], row_count as [Row Count]
from sys.dm_db_session_space_usage
            inner join
      sys.dm_exec_sessions
            on sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

Run the above queries in tempdb
0
 

Author Comment

by:printmedia
ID: 37832590
We are using SQL Server 2000
0
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.

 

Author Comment

by:printmedia
ID: 37832617
Deighton,

We haven't added any new temp tables in over 3 months and they are not big tables. Is there a way to know which tables, queries or around what time something is running that is causing it to spike?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37835841
How many users do you have 1GB is a very small tempdb and setting it to 10MB is IMHO pointless.
0
 

Author Comment

by:printmedia
ID: 37837414
We have 12 users.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 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