I have used temp tables and drop the same in the every stored procedure in my database. When I try to check my tempdb data using Select * from tempdb.sys.tables, I am getting many tables with names like #A002FF4B,#A08A53F2...etc (name with some hex characters).
These tables are continuously getting added in my tempdb and it is getting full. So sometimes my application stops due to it is not able to access SQL server once tempdb is full.
How can I resolve this issue?
If you do a quick search on this same site, you will find lot of similar questions. Basically, some process is taking to long and is making your temdb grow until you run out of space. According to Brent Ozar "Tempdb is like a public toilette", everyone (all kind of processes) use it and usually to do filthy things.
What you should do is locate what is making your tempdb grow, search that root cause and solve it. If you don't do that, you will still deal with continues issues like this one. You can, as a temporal solution, run a shrink on the tempdb to free some space. Please please avoid having an automated job to do shrinks.
You have several options to find the cause, running a SQL Server Profiler trace to track down Tempdb activity. Also you can use sp_whoisactive to log activity on the server while whatever causes the issue is running, so you can check that log afterward and see possible queries or processes that cause it. Kendra Little has a full blog entry on how to do this.
For some specific code samples, look here and here. Those are just some samples, as commented on first line, this a common issue, so you should find tons of useful answers here on Stackexchange site.
Your application likely has a temp table leak. If a session creates a temp table but does not drop it, that temp table will remain in tempdb until the session is terminated (or SQL Server is restarted). So if the application does not specifically drop a tempdb table, but keeps creating new ones every time a certain query is run, then you end up with numerous tables in tempdb that are "orphaned," so to speak.
This can occur if the code includes a DROP statement to drop the temp table, but it never reaches the DROP statement because the code takes another path, or throws an error prior to the DROP statement, and the error handling does not include the DROP statement. So the code needs to be analyzed to ensure that all temp tables are dropped no matter what path the code takes, or where it might encounter an error. Just because there is a DROP statement doesn't mean it is getting executed.
Immediate workarounds until the code can be fixed:
If none of these are possible and you are desperate, you might run something to periodically drop old table (see Cleaning Up TempDB), but please be aware that this could cause problems if you end up deleting temp tables that the application expects to be there.
