SCRIPTS :Job History Cleanup script

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