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

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

rmiao
Flowing Fount of Yak Knowledge

USA
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

jen
Flowing Fount of Yak Knowledge

Sweden
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

j_babaria
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

HTH

--------------------
hi

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.

Jay
Go to Top of Page

jayismyson
Starting Member

China
4 Posts

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

_________________
http://www.mmoggg.de/
http://www.saferunescapegold.com/

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

ahmeds08
Aged Yak Warrior

India
655 Posts

Posted - 03/26/2013 :  08:38:03  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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 fired...it 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
 New Topic  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