As a DBA, it’s common to encounter production or live servers where the tempdb database is growing too large and affecting server performance. In such cases, you may need to shrink the tempdb database without restarting the SQL Server instance. This is where a combination of DBCC commands comes in handy. By using DROPCLEANBUFFERS, FREEPROCCACHE, FREESYSTEMCACHE, and FREESESSIONCACHE to clear caches and flush connections, you can free up space in the tempdb database file. Finally, using DBCC SHRINKFILE can shrink the tempdb file size and resolve the performance issue.
It’s important to note that shrinking a database file can be a time-consuming and resource-intensive process, so it’s crucial to carefully plan and test this operation before executing it on a production or live server. By mastering this technique, you can optimize your server’s performance and maintain its stability, without the need for a SQL Server restart. This technique can be a lifesaver in critical situations, where downtime is not an option. So, if you’re facing issues with tempdb growth and server performance, give this technique a try and see how it works for you!
--DBCC DROPCLEANBUFFERS
/*Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order 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
--.. and finally, DBCC SHRINKFILE
/*DBCC SHRINKFILE is the same tool used to shrink any database file, in tempdb or other databases. This is the step that actually frees the unallocated space from the database file.
Warning: Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the DBCC operation to fail, and possibly corrupt your tempdb!
*/
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 1597) --- New file size in MB
GO
