Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 tempdb growing
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Flowing Fount of Yak Knowledge

7266 Posts

Posted - 04/29/2008 :  22:37:45  Show Profile  Reply with Quote
Did you check what was blocked?
Go to Top of Page

Flowing Fount of Yak Knowledge

4110 Posts

Posted - 05/27/2008 :  09:24:33  Show Profile  Send jen a Yahoo! Message  Reply with Quote
exclude tempdb from your monitoring... all you need to monitor on this system db is growth and make sure it has enough disk space to grow into since it's very dynamic

keeping it simple...
Go to Top of Page

Starting Member

1 Posts

Posted - 09/19/2012 :  00:50:09  Show Profile  Reply with Quote
[quote]Originally posted by jen

you will not be able to control the tempdb growth like the other databases

you need to find out what's making it grow to 2gb, if there are batch jobs, then you may need to schedule them evenly across the day,
if there are transactions requesting for very large datasets, you may want to advise your developers to divide the results
otherwise, expand your hd to accomodate the growth,

once the transactions have been committed, you will see that it will shrink back



When you says, "once the transactions have been committed, you will see that it will shrink back", you mean we dont need to manually shrink the tempdb?

If so, why its not shrinking for below example.

Tempdb - datafile initial size is 5 MB
Autogrow - 10 %

Create temp table
insert data into temp table till tempdb datafile reach 15 MB
drop temp table

Now if I checks the size of tempdb data file its still 15 MB

Restart the SQL instance

Tempdb data file is 5 MB.

Go to Top of Page

Starting Member

4 Posts

Posted - 03/20/2013 :  02:24:08  Show Profile  Reply with Quote
Paul, take it away...


Edited by - jayismyson on 03/20/2013 02:25:28
Go to Top of Page

Aged Yak Warrior

737 Posts

Posted - 03/26/2013 :  08:38:03  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
Originally posted by ansz5

We have a daily monitoring tool for SQL Servers. It cannot fetch details for tempdb. E.g. one of the queries fired is :

select b.groupname,sum(a.used)
from sysindexes a, sysfilegroups b
where a.groupid =* b.groupid
and (a.indid = 0 or a.indid = 1 or a.indid = 255)
group by b.groupname

It causes blocking. Next tiem (after 5 mins) when the same query is piles up the lock requests and tempdb does not respond to any request.

Please note that the query works fine,when used with NOLOCK hint.
ALso, the same query is working fine with all other databases on the server.

Please suggest.

what does "sp_who2" show??
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000