Sqlagent job to shrink tempdb datafile

Avyayah 1,291 Reputation points
2024-11-26T23:05:20.79+00:00

Checkpoint;

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE

DBCC FREESYSTEMCACHE('ALL');

DBCC FREESESSIONCACHE

Job created with this code works.

DECLARE @size NVARCHAR(10) = 10000

DECLARE @info nvarchar(max)

DECLARE @file nvarchar(max)

DECLARE @q1 nvarchar(max)

DECLARE tempdb_cursor cursor for

SELECT NAME FROM sys.master_files WHERE database_id = 2 AND NAME !='templog';

OPEN tempdb_cursor

FETCH NEXT FROM tempdb_cursor into @info

while @@fetch_status = 0

BEGIN

SET @info = @info

SET @q1 = 'USE [tempdb] DBCC SHRINKFILE (''' + @info + ''' , ' + @size + ')'

--EXEC @Q1

PRINT @q1

FETCH NEXT FROM tempdb_cursor

INTO @info

END

CLOSE tempdb_cursor;

DEALLOCATE tempdb_cursor;

This also works

USE [tempdb] DBCC SHRINKFILE ('tempdev', 20000)

USE [tempdb] DBCC SHRINKFILE ('temp2', 20000)

This also works but when create a single job with 3 steps it does not shrink the file

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,214 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.