SQLDBA_ Shrink tempDB

-


  

create proc dbo.sp_chi_shrinkdata  

(  

 @dbname  sysname = null,  

 @freed_space  int = null, --  the portion of free space (MB) to reclaim, eg. 90% of 1 gb is 900 mb  

 @stats   int = null --  Display status message every @stats MB.  

)  

as  

  

/*  Author: 

**  Created on:

**  Modified on: 

**  Purpose: Shrinks data file (not log).  

**  Features:  

**    1. Can shrink data size down to a specified value (optional). If desired size not provided, it   

**       will shrink all the way down until there's 200 MB free space left;  

**    2. Can report time, data size and cycle number after each run. If echo interval not supplied,   

**       it will report at every 20 MB interval.  

**  3. No need to be in the current database to shrink. It is in master :-)  

**  Test code:  

**    sp_chi_shrinkdata choice, 200, null  

**    (in waste_mgmt)hi  

*/  

begin  

   

 set nocount on  

  

 declare @start_data_total int,   

  @end_data_total int,   

  @message varchar(150),   

  @count int,   

  @start_data_used int,   

  @cmd varchar(500),   

  @string sysname,   

  @start_data_free int  

  

 if object_id ('tempdb..##datafilesize') is not null  

   drop table ##datafilesize  

  

 create table ##datafilesize   

 (   

  Fileid   tinyint,   

  [FileGroup]  tinyint,   

  TotalExtents  dec (8, 1),   

  UsedExtents  dec (8, 1),   

  [Name]   varchar(50),   

  [FileName]  sysname   

 )  

   

 if @dbname is null  

   set @dbname = db_name()  

  

 set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS with no_infomsgs'  

  

 insert into ##datafilesize exec (@string)  

  

 set @count = 1  

 --set @datafile = (select rtrim(name) from sysfiles (nolock) where fileid = 1)   

 set @start_data_total = (select TotalExtents * 8 * 8192.0 / 1048576.0 from ##datafilesize )  

     --  Trims away trailing blanks or will NOT work!!  

 --set @start_data_total = @datafile  

 set @start_data_used = (select UsedExtents * 8 * 8192.0 / 1048576.0 from ##datafilesize )  

   

 if @stats is null   

   set @stats = 20 -- default to 20 mb at each interval  

 if @freed_space is null  

   set @freed_space = @start_data_total - @start_data_used - 200   

       --  default to 200 MB free space left after shrinking  

 set @end_data_total = @start_data_total - @freed_space   

   

 print''  

 set @start_data_free = (@start_data_total-@start_data_used)  

 print '*** Before shrinking ***' + char(10) +   

       'Data total:   ' + convert(char(5),@start_data_total) + ' MB' + char(10) +  

       'Data used:    ' + convert(char(5),@start_data_used) + ' MB' + char(10) +   

       'Data free:    ' + convert(char(5),@start_data_free) + ' MB' + char(10)  

 print ''  

 print 'Starting shrinking data file for ' + @dbname + '......' + char(10)  

 while @start_data_total > @end_data_total  

   begin  

     set @start_data_total = @start_data_total - @stats  

 --    set @cmd = N' dbcc shrinkfile ('+@datafile+', '+convert(varchar(5), @start_data_total)+') WITH NO_INFOMSGS'  

   set @cmd = N' use ' + @dbname + ' dbcc shrinkfile (1, '+convert(varchar(5), @start_data_total)+') WITH NO_INFOMSGS'  

     exec (@cmd)  

     truncate table ##datafilesize  

     insert into ##datafilesize exec (@string)  

     set @start_data_total = (select TotalExtents * 8 * 8192.0 / 1048576.0 from ##datafilesize )  

 --    set @start_data_used = (select UsedExtents * 8 * 8192.0 / 1048576.0 from ##datafilesize )  

     set @message = convert(varchar(30), getdate(), 20) + '       - cycle ' + convert (varchar(6), @count) +   

       '        - Free space left: ' + convert(char(5),(@start_data_total - @start_data_used)) + ' MB'    

     print @message  

     set @count = @count + 1  

   end  

 print ''  

 print 'Shrinking data complete. Total reclaimed space: ' + cast((@start_data_free - (@start_data_total - @start_data_used)) as varchar(10)) + ' MB'   

       + char(10) + char(10) + '*** After shrinking ***' + char(10) +   

       'Data total:   ' + convert(char(5),@start_data_total) + ' MB' + char(10) +  

       'Data used:    ' + convert(char(5),@start_data_used) + ' MB' + char(10) +   

       'Data free:    ' + convert(char(5),(@start_data_total - @start_data_used)) + ' MB' + char(10)  

  

 drop table ##datafilesize  

  

 return (0)  -- sp_chi_shrinkdata  

end  



USE [msdb]

GO


/****** Object:  Job [ADM - Shrink TempDB]    Script Date: 2/20/2026 2:39:41 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [Database Maintenance]    Script Date: 2/20/2026 2:39:41 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


END


DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ADM - Shrink TempDB', 

@enabled=1, 

@notify_level_eventlog=2, 

@notify_level_email=0, 

@notify_level_netsend=0, 

@notify_level_page=0, 

@delete_level=0, 

@description=N'Shrinks TempDB', 

@category_name=N'Database Maintenance', 

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [shink]    Script Date: 2/20/2026 2:39:41 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'shink', 

@step_id=1, 

@cmdexec_success_code=0, 

@on_success_action=1, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=1, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'sp_chi_shrinkdata tempdb, 2000, 1000', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO



Comments

Post a Comment

Popular posts from this blog

DBA- MAXDOP

-
Image
  Have your SQL queries been eating up CPU like it's a buffet? Then it’s time to talk about the most underrated setting in SQL Server:  MAXDOP . Let’s break it down in simple, real-world terms — with best practices, optimized queries, and use cases that matter. What is MAXDOP? MAXDOP stands for Maximum Degree of Parallelism — it controls how many CPU cores SQL Server uses to run a single query in parallel. Sounds powerful? It is — but misconfigured, it can hurt more than help. Too low? Queries run slower, underutilizing hardware. Too high? CPU gets overwhelmed, blocking other tasks. 🔧  Where to Configure MAXDOP : Instance Level (Recommended for Most) EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE; Database Level (Overrides Instance Setting) ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2; Query Level (...
Read more

DBA - Check health and status of Always On or Availability Group using DMVs

-
 -- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum SELECT * FROM sys.dm_hadr_cluster SELECT * FROM sys.dm_hadr_cluster_members -- Health of the AGs SELECT ag.name agname, ags.* FROM sys.dm_hadr_availability_group_states ags  INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id -- Health and status of AG replics from the WsFC perspective SELECT ar.replica_server_name,harc.* FROM sys.dm_hadr_availability_replica_cluster_states harc  INNER JOIN sys.availability_replicas ar ON ar.replica_id = harc.replica_id -- Health and status of AG replicas, run this on the primary replica.  -- On secondary this will only show info for that instance SELECT * FROM sys.dm_hadr_availability_replica_states  -- Health and status of AG databases from the WSFC perspective SELECT * FROM sys.dm_hadr_database_replica_cluster_states  -- Health and status of AG databases, run this on the primary replica.  -- On secondary this wi...
Read more

DBA - Script out the Linked server objects.

-
  Script out the Linked server objects.        CREATE  PROCEDURE [dbo].[win_linked_server_objects_extract_select]     AS       /***********************************************************************************************************    --Purporse: To extract Linkded server connection details      --Created By: kovvuru subbareddy        Purpose:    TEST:        EXEC [dbo].[win_linked_server_objects_extract_select]       ***********************************************************************************************************/     BEGIN   SET NOCOUNT ON      CREATE TABLE #temp_linkedserver_detail   ( ID INTEGER identity(1,1) primary key NOT NULL,   create_linked_server_command VARCHAR(5000),   linked_server_name varchar(100) ...
Read more