Sqlagent job to shrink tempdb datafile

Question

Sqlagent job to shrink tempdb datafile

Avyayah 1,291

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

LiHongMSFT-4306 31,621 Reputation points

2024-11-27T05:34:31.5566667+00:00

Hi @Avyayah

Please check the job history in SSMS to see if any step failed and the error messages to identify the issue.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-11-27T22:55:16.6466667+00:00

Schedule a job to shrink tempdb? Why? You only shrink something when something exceptionally has happened, and you know it will happen again. Shrinking something that will grow again is just a waste of resources.
Naveen Kumar M 175 Reputation points

2024-12-24T03:14:15.01+00:00
Hi @Avyayah

The issue you’re facing is likely due to how SQL Server Agent handles the execution of your steps in the SQL Agent Job. When the commands work individually but not as part of a job.

My suggestion, combine all logic into a single step in the SQL Agent job to avoid context switching issues.

Here's a revised version of your script:

-- Combine all steps into one SQL Agent job step DECLARE @size NVARCHAR(10) = 20000 DECLARE @info 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 -- Build the DBCC SHRINKFILE command SET @q1 = 'USE [tempdb]; DBCC SHRINKFILE (''' + @info + ''', ' + @size + ')' -- Execute the command EXEC sp_executesql @q1 FETCH NEXT FROM tempdb_cursor INTO @info END CLOSE tempdb_cursor DEALLOCATE tempdb_cursor

SQL Agent Job Configuration Steps:

Step 1:

Use the revised script above as the single job step.

Set the step type to T-SQL Script.

Use the master database as the context (the script switches to tempdb internally).

Job Permissions:

Ensure the SQL Agent service account has sysadmin privileges to execute DBCC commands.

Scheduling:

Schedule the job during a maintenance window when tempdb usage is minimal to avoid file lock conflicts.

Verify tempdb activity before running the job using:

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2;