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 |
|
krishnarajeesh
Yak Posting Veteran
67 Posts |
 Posted - 2008-05-05 : 13:12:46
|
| Hi all,How to solve tempdb full error with out restarting sql server in live production environment.ThanksivykrisThanks & Best Regards,Krishna |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-05 : 13:22:18
|
| Try to shrink it.Your TempDB grows as you do following actions:1)any sorting that requires more memory than has been allocated to SQL Server 2)DBCC CheckDB3)DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb 4)large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb 5)any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb 6)use of an ODBC DSN with the option 'create temporary stored procedures' set can leave objects there for the life of the connectionTry to relocate TempDB to different drive. |
 |
|
|
krishnarajeesh
Yak Posting Veteran
67 Posts |
Posted - 2008-05-05 : 13:26:34
|
| By shrinking we will loose any uncomitted transactions ?Thanks & Best Regards,Krishna |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-05 : 13:34:25
|
| Check uncomitted tran with DBCC opentran and with SPID (DBCC inputbuffer to check statement) and kill it. Your TempDB is already full. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-05 : 22:19:10
|
| Shrink it? The db is full already, how can you shrink it? Should manually extend db files instead. |
|
|
|
|
|
|
TembDB full error