How to Shrink TempDB Without SQL Server Restart?
Sometimes it is not possible to shrink Tempdb directly. People blindly used to restart the SQL Service. When you restart the service Tempdb Size will automatically become 0.
Without restarting service also, we are able to shrink the tempdb using the below method. Restarting Services will cause the business down which is dangerous
Common reasons of Tempdb getting filled
1) Usually, tempdb fills up when you are low on disk space, or when you have set an unreasonably low maximum size for database growth.
Many people think that tempdb is only used for #temp tables. When in fact, you can easily fill up tempdb without ever creating a single temp table. Some other scenarios that can cause tempdb to fill up:
1) any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;
2) if the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;
3) DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;
4) DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;
5) large result sets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb;
6) any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;
The following will tell you how tempdb's space is allocated:
USE tempdb
GO
EXEC sp_spaceused
The following should give you some clues as to which table(s) consume most of the space in the data file(s) --this will help you narrow down any transactions that are either taking a long time or repeatedly being left in limbo:
USE tempdb
GO
SELECT * FROM tempdb..sysobjects
What you need to do, when Tempdb not able to shrink?
Run below query
USE [TEMPDB];
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREESESSIONCACHE;
GO
DBCC SHRINKFILE (TEMPDEV, 1024) -use script mention at the last
GO
DBCC DROPCLEANBUFFERS
Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, to flush everything to disk.
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE
Clears the procedure cache, which may free up some space in tempdb, although at the expense of your cached execution plans, which will need to be rebuilt the next time. This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.
DBCC FREEPROCCACHE;
GO
DBCC FREESYSTEMCACHE
This operation is similar to FREEPROCCACHE, except it affects other types of caches.
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREESESSIONCACHE
Flushes the distributed query connection cache. This has to do with distributed queries (queries between servers), but I’m really not sure how much space they actually take up in tempdb.
DBCC FREESESSIONCACHE;
GO