Hi,

Application is throwing errors.Is there anyway to fix this with out restarting the server on a newly migrated s

Hi,

I recall at least 4 reasons that explain why tempdb would fill up :

1. Long running transactions (SQL writes in tempdb until the transaction is either commited or rolled back)

2. Your application is creating large temporary tables (#table or ##table) that, by some reason, are not being

cleaned up properly (or they're kept alive for too long).

3. Your indexes have sort_in_tempdb enabled and some massive sort operations are being executed

4. Everything is OK but your tempdb storage requirements have not been properly estimated

@Sebastian:- How you identified its a TempDB full issue ? Is it from title.. I am just curious as I don't see any error information inside  nothing offensive

@Arun:- Please be descriptive with error

Assuming this as a TEMPDB full error, You have your transactions rolled back when it got full and doesn't had space to hold further.

You can get an idea on this by checking the Error log.

Make sure you are running your transactions on few batches rather than running it at a shot. I'm saying this because I have dealt with this issues where Application team was running ,long transactional batches. If you think that's not what is happening, Examine you TempDB Disk space and Add more storage if you could.

When you have this issue you can run the query to see what objects are existing in the tempdb.

select * from tempdb.sys.objects

increase the Tempdb size is one quick releaf.

Add more secondary files to your tempdb.

Hi,

Application is throwing errors.Is there anyway to fix this with out restarting the server on a newly migrated s