0

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);

tempdb_before_after_sql_job_run_Sizes

junketsu
  • 533
  • 5
  • 17
  • 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 Apr 08 '19 at 12:25
  • 1
    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 Apr 08 '19 at 12:31
  • 1
    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 Apr 08 '19 at 12:34
  • hi @BartHofland , whats my alternate to restarting . I man worse case I guess I could restart the server on a Friday night. I'm looking in tempdb -> Temporary Tables and i see 2 tables just sitting there (from about a month ago when I last checked). – junketsu Apr 08 '19 at 12:35
  • hi @AaronBertrand , well space is NOT an issue currently BUT if I dont run this job in about a week tempdb grows 50gb. The above shrinkjob keeps it in check BUT NOT to extent I want. 10mb is my test (yes its low) . I can increase that to 20 gb. – junketsu Apr 08 '19 at 12:38
  • 1
    But again, if you shrink to 20 GB and then it just grows back to 50 GB again, WHAT ARE YOU ACCOMPLISHING? – Aaron Bertrand Apr 08 '19 at 12:38
  • well 1) over time its tempdb memory space that is blocked and not used (say in 2 weeks it has grown to 100gb) . So I just want to keep it in check for weekly ETL's and then monthly process (start of month) is heavy tolling. – junketsu Apr 08 '19 at 12:41
  • Still don't follow what you're gaining by freeing up space just to use it again. This is like putting the milk on the counter to free up space in the fridge, even though you have nothing else to put in its spot. – Aaron Bertrand Apr 08 '19 at 12:44
  • ok I checked with these DBCC opentran() ; exec sp_who2 --spid ; exec sp_lock --spid ; on tempdb and I'm the only one on tempdb currently. I totally get what your saying @AaronBertrand that space is going to get used up regardless. But there has to be a way to keep it in check. Right now its NOT a directive from Managers. But there can be in near future or a year from now when tempdb growth is enforced. – junketsu Apr 08 '19 at 12:48
  • 1
    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 Apr 08 '19 at 12:57
  • @junketsu - what recovery model is your database using? If using "Full", this link may shed some light: https://dba.stackexchange.com/questions/202535/why-cant-i-shrink-log-file-in-full-recovery-mode .Basically, if using "full", you may need to perform regular backups of the transaction log, in order to keep its size from continually growing. – Moe Sisko Apr 09 '19 at 06:00

1 Answers1

0

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