As my planning for this maintenance task below steps are using in script:
(1) First we set batch 1 -10 loop run in short .
(2) Condition we capture mix date and getting top 10000 rows per batch .
(3) Delete rows as counts in batch loop.
(4) After end the delete batch transaction execute sp_updatestats
(5) Initial set up 2-3 batch count and check the loads of transaction .
(6) We will run the script in the job and job would be run non business hours on weekends
(7) This activity might long for an hour we need to monitor
use MSDB
go
DECLARE @rundate int, @CleanupDate datetime, @Batch int, @Retention int
set nocount on
set @Batch = 1
set @Retention = -7 -- days
while (@Batch <= 20) ---Increment a counter Batch 1-10
begin
select @rundate = min(run_date) from (select top 1000 run_date from sysjobhistory order by run_date) a_
select @CleanupDate = cast(left(@rundate, 4) + '-' + substring(cast(@rundate as varchar(8)), 5, 2) + '-' +
substring(cast(@rundate as varchar(8)), 7, 2) as datetime)
print 'Cleanup Date : ' + cast(@cleanupDate as varchar(30))
if @CleanupDate < dateadd(d, @Retention, getdate())
delete FROM sysjobhistory WHERE run_date = @rundate
else
print 'History to be maintained for Retention period : ' + cast(abs(@Retention) as varchar(5)) + ' days'
set @Batch = @Batch + 1
end
exec sp_updatestats
*****************************************************************************************
REMOVE TEMPDB LOG FILES
*******************************************************************************************
One of the Microsoft’s recommendations for optimizing the tempDB performance is to make each tempdb data file the same size.
My target here is to configure tempdb with 8 equal sized data files and one log file and to delete those 7 extra data files and re-size the remaining 8 files equally.
I followed the below simple three step process.
File can only be removed if it is empty so first we empty the file
-- Step1: First empty the data file
USE tempdb
GO
DBCC SHRINKFILE (tempdev12, EMPTYFILE); -- to empty "tempdev12" data file
GO
--Step2: Remove that extra data file from the database
ALTER DATABASE tempdb
REMOVE FILE tempdev12; --to delete "tempdev12" data file
GO
--Step3: Re-size the data files to target file size
-- Use ALTER DATABASE if the target file size is greater than the current file size as example size mentioned
USE [master]
GO
ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = N'tempdev', SIZE = 3072000KB ) --grow to 3000 MB
GO
--Use DBCC SHRINKFILE if the target file size is less than the current file size
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 3000) --shrink to 3000 MB
GO
PS: No need to restart SQL Service for removing TempDB files