info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
Automate SQL Server Database Data & Log File Growth Sizes
by BF (Principal Consultant; Architecture; Engineering)
2018-04-28
Automate SQL Server Database Data & Log File Growth Sizes:
View Auto-growth Settings for Data and Log files:
select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN -1 THEN 'File will grow until the disk is full'
ELSE CONVERT(VARCHAR, mf.max_size)
END AS [max_size]
, physical_name
from sys.master_files mf
Modify Auto-growth Settings for Data and Log files
Use Master
go
Set Nocount On
SELECT d.name,abc.Size_MBs as DB_SIZE_GB, mf.physical_name as fname,
cast((mf.size * 8 /1024/1024.0) as decimal(10,2)) as File_Size_GB,
case mf.is_percent_growth
when 0 then 'IN MBs'
when 1 then 'IN %'
end as GROWTH_TYPE
INTO tempdb.dbo.FILE_PRE_DETAIL
FROM sys.master_files mf
JOIN sys.databases d ON d.database_id = mf.database_id
JOIN SYSALTFILES SF ON SF.fileid=MF.file_id
JOIN (SELECT d.name,
cast((SUM(mf.size) * 8 /1024/ 1024.0) as decimal(10,2)) as Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name) abc on abc.name=d.name
WHERE d.database_id > 4 -- Skip system databases
and mf.is_percent_growth=1
and d.state_desc = 'ONLINE'
and d.is_read_only <> 1
group by d.name,abc.Size_MBs,MF.physical_name,mf.size,mf.is_percent_growth
order by d.name
GO
-- CURSOR TO CHANGE DATAFILE GROWTH
SET NOCOUNT ON
DECLARE @name VARCHAR(1000)
Declare @TableName VARCHAR(1000)
Declare @insert VARCHAR(1024)
IF OBJECT_ID('tempdb.dbo.Database_growth', 'U') IS NOT NULL DROP TABLE tempdb.dbo.Database_growth;
CREATE TABLE tempdb.dbo.Database_growth (DBName varchar(500),logical nvarchar(1000),physical nvarchar(2000));
DECLARE db_cursor CURSOR FOR
SELECT NAME FROM SYS.databases WHERE name NOT IN ('master','model','msdb','tempdb') and state_desc = 'ONLINE'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
--select @name
SET @TableName = @NAME + '.sys.database_files'
--select @TableName
IF object_id(@TableName) IS NOT NULL
BEGIN
SET @insert = 'insert into tempdb.dbo.Database_growth select '''+@name+''',name,physical_name from ' + @TableName + ''
EXEC (@insert)
END
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SET NOCOUNT ON
DECLARE @size decimal(18,2)
DECLARE @fname NVARCHAR(MAX)
Declare @Command NVARCHAR(MAX)
DECLARE @dbname nvarchar(200)
DECLARE @command1 nvarchar(max)
DECLARE @vi nvarchar(max)
DECLARE db_cursor CURSOR FOR
SELECT abc.Size_MBs as SIZE, mf.physical_name as fname, abc.name as dbname
FROM sys.master_files mf
JOIN sys.databases d ON d.database_id = mf.database_id
JOIN SYSALTFILES SF ON SF.fileid=MF.file_id
JOIN (SELECT d.name,
cast((SUM(mf.size) * 8 /1024/ 1024.0) as decimal(10,2)) as Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name) abc on abc.name=d.name
WHERE d.database_id > 4 -- Skip system databases
and mf.is_percent_growth=1
and d.state_desc = 'ONLINE'
and d.is_read_only <> 1
AND SF.groupid <> 0 --DATA FILES
group by abc.Size_MBs,MF.physical_name,abc.name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @size, @fname,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@size < 50.00)
BEGIN
SET @COMMAND=(SELECT '--DATA FILE: ' + CHAR(10) + 'ALTER DATABASE ['+@dbname+'] MODIFY FILE (NAME = '''+logical+''' , FILEGROWTH = 32MB)' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
Print @command
END
IF (@size between 50.00 and 128.00)
BEGIN
SET @COMMAND=(SELECT '--DATA FILE: ' + CHAR(10) + 'ALTER DATABASE ['+@dbname+'] MODIFY FILE (NAME = '''+logical+''' , FILEGROWTH = 64MB)' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
Print @command
END
IF (@size > 128.00)
BEGIN
SET @COMMAND=(SELECT '--DATA FILE: ' + CHAR(10) + 'ALTER DATABASE ['+@dbname+'] MODIFY FILE (NAME = '''+logical+''' , FILEGROWTH = 256MB)' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
Print @command
END
FETCH NEXT FROM db_cursor INTO @size , @fname, @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
DECLARE db_cursor CURSOR FOR
SELECT abc.Size_MBs as SIZE, mf.physical_name as fname, abc.name as dbname
FROM sys.master_files mf
JOIN sys.databases d ON d.database_id = mf.database_id
JOIN SYSALTFILES SF ON SF.fileid=MF.file_id
JOIN (SELECT d.name,
cast((SUM(mf.size) * 8 /1024/ 1024.0) as decimal(10,2)) as Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name) abc on abc.name=d.name
WHERE d.database_id > 4 -- Skip system databases
and mf.is_percent_growth=1
and d.state_desc = 'ONLINE'
and d.is_read_only <> 1
AND SF.groupid = 0 --LOG FILES
group by abc.Size_MBs,MF.physical_name,abc.name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @size, @fname,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@size < 50.00)
BEGIN
SET @COMMAND=(SELECT '--LOG FILE: ' + CHAR(10) + 'ALTER DATABASE ['+@dbname+'] MODIFY FILE (NAME = '''+logical+''' , FILEGROWTH = 32MB)' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
Print @command
END
IF (@size between 50.00 and 256.00)
BEGIN
SET @COMMAND=(SELECT '--LOG FILE: ' + CHAR(10) + 'ALTER DATABASE ['+@dbname+'] MODIFY FILE (NAME = '''+logical+''' , FILEGROWTH = 64MB)' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
Print @command
END
IF (@size > 256.00)
BEGIN
SET @COMMAND=(SELECT '--LOG FILE: ' + CHAR(10) + 'ALTER DATABASE ['+@dbname+'] MODIFY FILE (NAME = '''+logical+''' , FILEGROWTH = 128MB)' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
Print @command
END
FETCH NEXT FROM db_cursor INTO @size , @fname, @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
--CREATE TEMP TABLE FOR POST DATAFILE GROWTH
IF OBJECT_ID('tempdb.dbo.FILE_PRE_DETAIL', 'U') IS NOT NULL DROP TABLE tempdb.dbo.FILE_POST_DETAIL;
SELECT d.name,abc.Size_MBs as DB_SIZE_GB, mf.physical_name as fname,
cast((mf.size * 8 /1024/1024.0) as decimal(10,2)) as File_Size_GB,
cast((mf.growth * 8/1024.0) as decimal(10,2)) AS GROWTH_MBs,
case mf.is_percent_growth
when 0 then 'IN MBs'
when 1 then 'IN %'
end as GROWTH_TYPE
INTO tempdb.dbo.FILE_POST_DETAIL
FROM sys.master_files mf
JOIN sys.databases d ON d.database_id = mf.database_id
JOIN SYSALTFILES SF ON SF.fileid=MF.file_id
JOIN (SELECT d.name,
cast((SUM(mf.size) * 8 /1024/ 1024.0) as decimal(10,2)) as Size_MBs
FROM sys.master_files mf
and d.state_desc = 'ONLINE'
and d.is_read_only <> 1
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name) abc on abc.name=d.name
JOIN TEMPDB.DBO.FILE_PRE_DETAIL FPD ON FPD.fname=MF.physical_name
WHERE d.database_id > 4 -- Skip system databases
group by d.name,abc.Size_MBs,MF.physical_name,mf.size,mf.is_percent_growth,MF.growth
order by d.name
IF OBJECT_ID('tempdb.dbo.FILE_PRE_DETAIL', 'U') IS NOT NULL DROP TABLE tempdb.dbo.FILE_PRE_DETAIL;
IF OBJECT_ID('tempdb.dbo.FILE_PRE_DETAIL', 'U') IS NOT NULL DROP TABLE tempdb.dbo.FILE_POST_DETAIL;
IF OBJECT_ID('tempdb.dbo.Database_growth', 'U') IS NOT NULL DROP TABLE tempdb.dbo.Database_growth;
go
Recommended:
Data File Growth Increments - 256MB
Log File Growth Increments - 128MB
The above script allows for more flexibility if needed.
Updated: Includes sizes in % and MB and output if before + after
-- CREATE TEMP TABLE FOR PRE DATAFILE GROWTH
IF OBJECT_ID('tempdb.dbo.FILE_PRE_DETAIL', 'U') IS NOT NULL DROP TABLE tempdb.dbo.FILE_PRE_DETAIL;
go
SELECT d.name,abc.Size_MBs as DB_SIZE_GB, mf.physical_name as fname,
cast((mf.size * 8 /1024/1024.0) as decimal(10,2)) as File_Size_GB,
case mf.is_percent_growth
when 0 then 'IN MBs (Currently its: "'+convert(varchar(100),cast((mf.growth * 8/1024.0) as decimal(10,2)))+' MB")'
when 1 then 'IN %'
end as GROWTH_TYPE
INTO tempdb.dbo.FILE_PRE_DETAIL
FROM sys.master_files mf
JOIN sys.databases d ON d.database_id = mf.database_id
JOIN SYSALTFILES SF ON SF.fileid=MF.file_id
JOIN (SELECT d.name,
cast((SUM(mf.size) * 8 /1024/ 1024.0) as decimal(10,2)) as Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name) abc on abc.name=d.name
WHERE d.database_id > 4 -- Skip system databases
and d.state_desc = 'ONLINE' and d.is_read_only <> 1
--and mf.is_percent_growth=1
--or mf.growth < 6400
group by d.name,abc.Size_MBs,MF.physical_name,mf.size,mf.is_percent_growth,mf.growth
order by d.name
GO
-- CURSOR TO CHANGE DATAFILE GROWTH
SET NOCOUNT ON
DECLARE @name VARCHAR(1000)
Declare @TableName VARCHAR(1000)
Declare @insert VARCHAR(1024)
IF OBJECT_ID('tempdb.dbo.Database_growth', 'U') IS NOT NULL DROP TABLE tempdb.dbo.Database_growth;
CREATE TABLE tempdb.dbo.Database_growth (DBName varchar(500),logical nvarchar(1000),physical nvarchar(2000));
DECLARE db_cursor CURSOR FOR
SELECT NAME FROM SYS.databases WHERE name NOT IN ('master','model','msdb','tempdb') and state_desc = 'ONLINE' and is_read_only <> 1
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
--select @name
SET @TableName = @NAME + '.sys.database_files'
--select @TableName
IF object_id(@TableName) IS NOT NULL
BEGIN
SET @insert = 'insert into tempdb.dbo.Database_growth select '''+@name+''',name,physical_name from ' + @TableName + ''
EXEC (@insert)
END
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SET NOCOUNT ON
DECLARE @size decimal(18,2)
DECLARE @fname NVARCHAR(MAX)
Declare @Command NVARCHAR(MAX)
DECLARE @dbname nvarchar(200)
DECLARE @command1 nvarchar(max)
DECLARE @vi nvarchar(max)
DECLARE db_cursor CURSOR FOR
SELECT abc.Size_MBs as SIZE, mf.physical_name as fname, abc.name as dbname
FROM sys.master_files mf
JOIN sys.databases d ON d.database_id = mf.database_id
JOIN SYSALTFILES SF ON SF.fileid=MF.file_id
JOIN (SELECT d.name,
cast((SUM(mf.size) * 8 /1024/ 1024.0) as decimal(10,2)) as Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name) abc on abc.name=d.name
WHERE d.database_id > 4 -- Skip system databases
and d.state_desc = 'ONLINE' and d.is_read_only <> 1
--and mf.is_percent_growth=1 or mf.growth < 6400
AND SF.groupid <> 0
group by abc.Size_MBs,MF.physical_name,abc.name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @size, @fname,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@size < 50.00)
BEGIN
SET @COMMAND=(SELECT 'ALTER DATABASE ['+@dbname+'] MODIFY FILE ( NAME = '''+logical+''' , FILEGROWTH = 100MB )' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
EXEC (@command)
END
IF (@size between 50.00 and 200.00)
BEGIN
SET @COMMAND=(SELECT 'ALTER DATABASE ['+@dbname+'] MODIFY FILE ( NAME = '''+logical+''' , FILEGROWTH = 500MB )' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
EXEC (@command)
END
IF (@size between 200.00 and 400.00)
BEGIN
SET @COMMAND=(SELECT 'ALTER DATABASE ['+@dbname+'] MODIFY FILE ( NAME = '''+logical+''' , FILEGROWTH = 2000MB )' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
EXEC (@command)
END
IF (@size > 400.00)
BEGIN
SET @COMMAND=(SELECT 'ALTER DATABASE ['+@dbname+'] MODIFY FILE ( NAME = '''+logical+''' , FILEGROWTH = 4000MB )' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
EXEC (@command)
END
FETCH NEXT FROM db_cursor INTO @size , @fname, @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
DECLARE db_cursor CURSOR FOR
SELECT abc.Size_MBs as SIZE, mf.physical_name as fname, abc.name as dbname
FROM sys.master_files mf
JOIN sys.databases d ON d.database_id = mf.database_id
JOIN SYSALTFILES SF ON SF.fileid=MF.file_id
JOIN (SELECT d.name,
cast((SUM(mf.size) * 8 /1024/ 1024.0) as decimal(10,2)) as Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name) abc on abc.name=d.name
WHERE d.database_id > 4 -- Skip system databases
and d.state_desc = 'ONLINE' and d.is_read_only <> 1
--and mf.is_percent_growth=1 or mf.growth < 6400
AND SF.groupid = 0
group by abc.Size_MBs,MF.physical_name,abc.name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @size, @fname,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@size < 50.00)
BEGIN
SET @COMMAND=(SELECT 'ALTER DATABASE ['+@dbname+'] MODIFY FILE ( NAME = '''+logical+''' , FILEGROWTH = 50MB )' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
EXEC (@command)
END
IF (@size between 50.00 and 200.00)
BEGIN
SET @COMMAND=(SELECT 'ALTER DATABASE ['+@dbname+'] MODIFY FILE ( NAME = '''+logical+''' , FILEGROWTH = 200MB )' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
EXEC (@command)
END
IF (@size between 200.00 and 400.00)
BEGIN
SET @COMMAND=(SELECT 'ALTER DATABASE ['+@dbname+'] MODIFY FILE ( NAME = '''+logical+''' , FILEGROWTH = 1000MB )' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
EXEC (@command)
END
IF (@size > 400.00)
BEGIN
SET @COMMAND=(SELECT 'ALTER DATABASE ['+@dbname+'] MODIFY FILE ( NAME = '''+logical+''' , FILEGROWTH = 2000MB )' from tempdb.dbo.Database_growth
where DBname=@dbname and physical=@fname)
EXEC (@command)
END
FETCH NEXT FROM db_cursor INTO @size , @fname, @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
IF OBJECT_ID('tempdb.dbo.Database_growth', 'U') IS NOT NULL DROP TABLE tempdb.dbo.Database_growth;
go
IF OBJECT_ID('tempdb.dbo.FILE_POST_DETAIL', 'U') IS NOT NULL DROP TABLE tempdb.dbo.FILE_POST_DETAIL;
go
-- CREATE TEMP TABLE FOR POST DATAFILE GROWTH
SELECT d.name,abc.Size_MBs as DB_SIZE_GB, mf.physical_name as fname,
cast((mf.size * 8 /1024/1024.0) as decimal(10,2)) as File_Size_GB,
cast((mf.growth * 8/1024.0) as decimal(10,2)) AS GROWTH_MBs,
case mf.is_percent_growth
when 0 then 'IN MBs'
when 1 then 'IN %'
end as GROWTH_TYPE
INTO tempdb.dbo.FILE_POST_DETAIL
FROM sys.master_files mf
JOIN sys.databases d ON d.database_id = mf.database_id
JOIN SYSALTFILES SF ON SF.fileid=MF.file_id
JOIN (SELECT d.name,
cast((SUM(mf.size) * 8 /1024/ 1024.0) as decimal(10,2)) as Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name) abc on abc.name=d.name
JOIN TEMPDB.DBO.FILE_PRE_DETAIL FPD ON FPD.fname=MF.physical_name
WHERE d.database_id > 4 -- Skip system databases
and d.state_desc = 'ONLINE' and d.is_read_only <> 1
group by d.name,abc.Size_MBs,MF.physical_name,mf.size,mf.is_percent_growth,MF.growth
order by d.name
select 'PRE_CHANGE_DETAIL' as REMARKS,* from tempdb.dbo.FILE_PRE_DETAIL
select 'POST_CHANGE_DETAIL' as REMARKS,* from tempdb.dbo.FILE_POST_DETAIL
|
|
|
|
|
|
|
|
|
|