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.
| Author |
Topic |
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
 Posted - 2009-06-02 : 07:22:52
|
| Experts,Recently on a day TEMPDB database has grown full and I rectified on moving the FILES to another location and restarted the SQL Server and it worked fine and doing fine.Reason for that was there were two simultaneously running query which fetches more than 1 million rows and it eventually draws these rows from the Remote Server through Link Server setup but Source Server has less in memory and depended on the Remote Server (this remote server is Newly setup) has more in memory and subsequently the rows driven from this Remote server has taken heavy sorting taken place which requires TEMPDB database space. Ultimately Remote Server TempDB Server has been full.Error Log File says the evidence of query which fetches the rows during that TEMPDB has become full apart from that there is no evidence for me to unearth.I knew there is no Stored Procedures were running during that time, where the Temp Tables in the Stored Procedures to make the TEMPDB to full. This is Ruled out during the execution time.May I request all the EXPERTS in the FORUM, as what made the TEMPDB to grow; (based on the above scenario)and how to avoid that in future;What are the remedies for TEMPDB not to grow;How to surveillance the TEMPDB.Thanks for your patience in reading this lengthy problem and advance thanks for all the participated with relevant answers. |
|
|
SQLRatankalwa
Starting Member
42 Posts |
Posted - 2009-06-02 : 08:48:52
|
| HiPlease Post the SQL you are running. Based on which we can suggest what changes you need to do to your query, You are talking about a million records.I will definitely help you out if you post the SQL.Ratan KalwaSQL Server Professionals User Grouphttp://www.revalsys.com |
 |
|
|
SQLRatankalwa
Starting Member
42 Posts |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-06-02 : 10:08:20
|
| I've seen queries totally fill TEMPDB, usually created by a newbie but not always, and the system would grind to a halt. Post your code and the DDL for the tables in that query, and as Ratan said, we can probably help.Edit - how big is your tempdb?Terry-- Procrastinate now! |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-06-09 : 17:39:31
|
| Thanks all. Yes it was the Stored Procedure which caused the TEMP space filled out and TEMPDB was restored on moving and rebooting server. |
|
|
|
|
|
|
Tempdb Problem