Tempdb help ! - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Tempdb help !

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-30 : 08:06:56

Hello

I am experiencing a problem in which the Tempdb grows by up to 40GB
each day and I tried shrinking but that does not seem to work. The only way to clear up the Tempdb in my case is to re-start the SQL Server.

Would anyone happen to know the cause of this, and how to prevent
More importantly, how to correct the initial problem

I would greatly appreciate any assistance you may be able to provide

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 08:10:29

Are you using queries having large number of temporary tables involved and even making bulk insertions into them?

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-30 : 08:16:57

Hi visakh16

Thanks for your responce. The Server does have a table which is about 8GB in size, which is updated each day with
current URL Blocking data. and a second table which is about 15GB in size which stores report data. but thats
about it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 08:24:23

quote:
Originally posted by pharoah35

Hi visakh16

Thanks for your responce. The Server does have a table which is about 8GB in size, which is updated each day with
current URL Blocking data. and a second table which is about 15GB in size which stores report data. but thats
about it

Are they temporary(#) tables? or are you using temp tables in process of populating them?

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-30 : 08:32:26

I also thought this may be related to some sort of temp table function, only the tables noted are not temp tables at all
and are not being updated using temp tables. The tables are updated by a file which is downloaded and imported.
So this is weird, thats why I came to the experts

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-10-02 : 10:47:25

Anyone have any ideas ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-02 : 13:21:49

You should limit your temp table and table variable for Large records as it is consuming lot of space. Try using index in Temp table. Are you using online Reindexing?

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-10-02 : 13:39:02

Question, if you limit the temp table size say to 30gb, and the temp table grows and reaches 30gb what happends?
Does the SQL Server auto dump the Temp table or Dowes the SQL Server crash?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-02 : 13:43:25

It doesn't autodump . Thats why restarting SQL Services created Fresh TempDB. Make sure you enable AUTOGROW on your TEMPDB with simple Recovery model.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-02 : 13:54:35

Is "lock pages in memory" enabled on the server? We had an issue with tempdb on one system where it grew to over 50GB even though on another system with a copy of the databsae, tempdb was never over 4GB. We forgot to enable the "lock pages in memory" setting. After that was done and the SQL Server service was restarted, the issue no longer happened.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-02 : 13:59:01

Thanks Tara.

This is point to be noted.

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-10-02 : 15:05:38

Thank you all for your assistance. I have set the "lock pages in memory" on the server as suggested
and also Autogrow & Simple recovery mode on the Tempdb. I will restart my SQL Server during the next
maintenance cycle and hopefully that will do it

thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources