junketsu
junketsu

Reputation: 531

Sql Server Shrinking temp db mdf and ndf

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

Upvotes: 0

Views: 6137

Answers (1)

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

Upvotes: 0

Related Questions