Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-04-11
6
Medium Priority
?
650 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 2000 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

705 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