0
(0)

Error Description:

Failed to allocate disk space for a work table in database 'tempdb'.  You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command.

Resolution:

When tempdb is 100% full, many a times it may happen that we are not able to run any command on the database server to check which process has caused tempdb to full,even sp_who gives tempdb full error and in such cases, we can use mon table queries to get the culprit process.

select SPID, DBName, ObjectName, PartitionSize from master..monProcessObject
where DBID = tempdb_id(SPID) order by SPID

or

select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText

below is the query to abort all the transactions under tempdb database.

select lct_admin("abort",0,2)

here, "0" — stands for all transactions
and "2" — stands for tempdb database

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.