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
No comments:
Post a Comment