Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

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

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
printmedia
Asked:
printmedia
1 Solution
 
deightonCommented:
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
 
Barry CunneyCommented:
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
 
printmediaAuthor Commented:
We are using SQL Server 2000
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
printmediaAuthor Commented:
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
 
Anthony PerkinsCommented:
How many users do you have 1GB is a very small tempdb and setting it to 10MB is IMHO pointless.
0
 
printmediaAuthor Commented:
We have 12 users.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now