Programmatically truncate the tempdb data growth

use tempdb

go

DECLARE @ExistingSize int

SELECT

    name AS FileName,

    size*1.0/128 AS FileSizeinMB,

    CASE max_size

        WHEN 0 THEN 'Autogrowth is off.'

        WHEN -1 THEN 'Autogrowth is on.'

        ELSE 'Log file will grow to a maximum size of 2 TB.'

    END as Filesize,

    growth AS 'GrowthValue',

    'GrowthIncrement' =

        CASE

            WHEN growth = 0 THEN 'Size is fixed and will not grow.'

            WHEN growth > 0 AND is_percent_growth = 0

                THEN 'Growth value is in 8-KB pages.'

            ELSE 'Growth value is a percentage.'

        END

into #tempdbthreshhold

FROM tempdb.sys.database_files

where max_size=-1 and type_desc='Rows'

select @ExistingSize=FileSizeinMB from #tempdbthreshhold

print @ExistingSize

IF @ExistingSize > 10000

Begin

---check the tresh

-- Create the temporary table to accept the results.

CREATE TABLE #OpenTranStatus (

   ActiveTransaction varchar(25),

   Details sql_variant

   );

-- Execute the command, putting the results in the table.

INSERT INTO #OpenTranStatus

EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');

-- Display the results.

SELECT * FROM #OpenTranStatus;

if @@rowcount=0

SELECT

    name AS FileName,

    size*1.0/128 AS FileSizeinMB,

    CASE max_size

        WHEN 0 THEN 'Autogrowth is off.'

        WHEN -1 THEN 'Autogrowth is on.'

        ELSE 'Log file will grow to a maximum size of 2 TB.'

    END as Filesize,

    growth AS 'GrowthValue',

    'GrowthIncrement' =

        CASE

            WHEN growth = 0 THEN 'Size is fixed and will not grow.'

            WHEN growth > 0 AND is_percent_growth = 0

                THEN 'Growth value is in 8-KB pages.'

            ELSE 'Growth value is a percentage.'

        END

into #tempdbfilestats

FROM tempdb.sys.database_files

where max_size=-1 and type_desc='Rows'

Declare @LogFileName sysname

select @LogFileName=filename from #tempdbfilestats

declare @sql varchar(max)

              set @sql ='DBCC SHRINKFILE ([' +@LogFileName+'] ,1024)'

print @SQL

              Exec (@SQL)

End

       ELSE         

Begin

print 'There is no data logs to be trunctaed'

End