Sql Server Shrinking temp db mdf and ndf

Question

So my question is that even after the job runs and I'm enforcing the mdf (main tempdb file) to be shrunk to about 10mb or so why is it NOT doing it? I have tried to run this job after my most heavy lifting ETL jobs (that pulls from various sources and preps data for reporting needs). This specific server IS NOT used for reporting procs. I'm just curious as to why the shrink does not happen? Should I be taking a full backup of Temp db? and then delete the backup? Sql job that I did my research here from previous threads and posts and built. Sql server job

dbcc shrinkdatabase (tempdb, 97)

-- Clean all buffers and caches
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
DBCC FREESESSIONCACHE;


DBCC SHRINKFILE (temp2,TRUNCATEONLY);
DBCC SHRINKFILE (temp3,TRUNCATEONLY);
DBCC SHRINKFILE (temp4,TRUNCATEONLY);
DBCC SHRINKFILE (temp5,TRUNCATEONLY);
DBCC SHRINKFILE (temp6,TRUNCATEONLY);
DBCC SHRINKFILE (temp7,TRUNCATEONLY);
DBCC SHRINKFILE (temp8,TRUNCATEONLY);
DBCC SHRINKFILE (templog,TRUNCATEONLY);
DBCC SHRINKFILE (tempdev,TRUNCATEONLY);



DBCC SHRINKFILE (temp2,10);
DBCC SHRINKFILE (temp3,10);
DBCC SHRINKFILE (temp4,10);
DBCC SHRINKFILE (temp5,10);
DBCC SHRINKFILE (temp6,10);
DBCC SHRINKFILE (temp7,10);
DBCC SHRINKFILE (temp8,10);
DBCC SHRINKFILE (templog,10);
DBCC SHRINKFILE (tempdev,10);
dbcc shrinkdatabase (tempdb, 10);

ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = 10);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 10);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp2', SIZE = 10);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp3', SIZE = 10);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp4', SIZE = 10);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp5', SIZE = 10);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp6', SIZE = 10);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp7', SIZE = 10);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp8', SIZE = 10);

i also want to point out that i went to System Databases -> tempdb (right clicked [tasks] then shrink db to 98% . Does not do anything. — junketsu
– junketsu, Commented Apr 8, 2019 at 12:25
Are you sure there are no open connections with which temporary objects have been created? And that there are no global temporary objects stored in the tempdb database (using '##' in front of the object names)? In the latter case you may have to cleanup those objects by hand or restart your SQL Server service before truncating. — Bart Hofland
– Bart Hofland, Commented Apr 8, 2019 at 12:31
There could also be permanent objects there, either that you've created manually since the last restart, or that get created automatically because they are in model. But what are you gaining by shrinking to 10 MB anyway? That seems way too tiny for any workload. And they're just going to need to grow again the next time you run your ETL job. So what were you able to do with the freed up space in the meantime? — Aaron Bertrand
– Aaron Bertrand, Commented Apr 8, 2019 at 12:34
But again, if you shrink to 20 GB and then it just grows back to 50 GB again, WHAT ARE YOU ACCOMPLISHING? — Aaron Bertrand
– Aaron Bertrand, Commented Apr 8, 2019 at 12:38
Why do you need to "keep it in check"? Set an alert for total size > some reasonable % higher than your typical result after your heavy ETL, and focus on more important things than this repetitive, useless shrink operation. — Aaron Bertrand
– Aaron Bertrand, Commented Apr 8, 2019 at 12:57

Jean Karlo Huiza Ramos · Accepted Answer · 2019-06-01 02:11:52Z

0

Verify that after executing the JOB, other transactions are not being executed. Apparently other transactions are being executed after you reduce the TEMPDB.

answered Jun 1, 2019 at 2:11

Jean Karlo Huiza Ramos

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sql Server Shrinking temp db mdf and ndf

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related