Solved

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

Posted on 2012-04-11
6
636 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 50
MS SQL 2014 get SPIDs of users 6 26
INSERT INTO SELECT JOIN THING 2 26
c# code 19 59
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now