Script:Code to Find the Size and Growth Percentage of the tempdb Data and Log Files

Code to Find the Size and Growth Percentage of the tempdb Data and Log Files

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,
  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
FROM tempdb.sys.database_files;
GO

Script: Log file size to a user table

DECLARE @rowcnt INT
DECLARE @iterator INT
DECLARE @dbname VARCHAR(200)
DECLARE @exec_sql VARCHAR(500)
SET @rowcnt = 0
SET @iterator = 1
CREATE TABLE #db_file_info ( [Database_Name] SYSNAME NOT NULL, [File_ID] SMALLINT NOT NULL, [File_Type] VARCHAR(10) NOT NULL, [File_Name] SYSNAME NOT NULL, [File_Path] VARCHAR(500) NOT NULL, [File_Size_in_MB] INT NOT NULL, [Space_Used_in_MB] INT NOT NULL, [Space_Left_in_MB] INT NOT NULL, [Time_collected] smalldatetime )
CREATE TABLE #db ( dbid INT, name VARCHAR(200))
INSERT INTO #db
SELECT database_id, name FROM sys.databases
WHERE --database_id <> 11 AND [state] <> 6
SET @rowcnt = @@ROWCOUNT
WHILE @iterator <= @rowcnt
BEGIN
SELECT @dbname = '['+ name + ']' FROM #db
SET @exec_sql = ' USE ' + @dbname + ';
Insert into #DB_FILE_INFO Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end, name,filename, [file_size] = convert(int,round ((sysfiles.size*1.000)/128.000,0)), [space_used] = convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)), [space_left] = convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0)), getdate() from dbo.sysfiles; '
EXEC( @exec_sql)
SET @iterator = @iterator + 1
END
INSERT INTO SQLAdmin_Monitor_db_file_info
SELECT * FROM #db_file_info
DROP TABLE #db
DROP TABLE #db_file_info

Modifying database file

All the database files are set to autogrowth to 10% from the initial sizes. But,the auto growth can happen any time during business hours causing the database slow down. Therefore I am taking the following approach -

1 I have set up a sql job ( calls a script listed here) that logs the current datafile and the log file size in a table in interval of 4 hours daily. I use that historical data to research when the data file needs to grow.
2. Then I run this script from the table to find which database file has more than 95% filled up and those are the candidate for me to manually resize ( expand) the data file.
3. I use the follwing script to resize the datafile
use db1
Go

---select * from sys.sysfiles

alter database client_343
modify file
(
name = client_343,
size = 1024MB
);

Script: To find the database file where the file space is used more than 94 % of the current file size

--The historic data is logged in a table in every 4 hours and the following query in run in the table to find the current grown rate of the databse file

SELECT Database_Name,Space_Used_in_MB,File_Size_in_MB,(cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 as newval
FROM Monitor_db..SQLAdmin_Monitor_db_file_info
WHERE FILE_ID = 1 AND (cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 >= 95 AND Time_collected IN (SELECT MAX(time_collected) FROM hCFS_SQLMonitor..SQLAdmin_Monitor_db_file_info )
ORDER BY File_Size_in_MB