Sql-server – Temp DB is growing continuously in SQL server

sql servertempdb

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?

Best Answer

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.

Related Solutions

Sql-server – Slow temp table drops in sql 2005

You didn't mention the IOPS used by the tempdb LUN. Are you seeing any IO waits on the LUN device? Is anything else on that LUN? Is another system using the disk group/pool that the LUN is carved from?

Ditto master db -- same q's.

Also, are you using local temp tables (#tablename) or global temp tables (##tablename)? If global could another SPID be keeping the shared lock on the global temp tables when you're trying to delete them?

Sql-server – Why does OBJECT_ID return NULL in a computed column

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054

Best Answer

Related Solutions

Sql-server – Slow temp table drops in sql 2005

Sql-server – Why does OBJECT_ID return NULL in a computed column

Related Question