Solved

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

Posted on 2012-04-11
6
648 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
[X]
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
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

615 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