SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIEROFF
GO
CREATEProcedure [dbo].[spResizeTlog]
(@modus varchar(50)=‘view’
, @database nvarchar(128)=NULL
, @newsizeMB int=NULL
, @maxautomaticgrowthMB int=NULL
)
AS
setnocounton
–Create a lookup table, to lookup the required growth size
declare @maxgrowth int
declare @maxVLFs int
declare @lookup table (size int, VLFs int, fullsize int, fullVLFs int, numofgrowths int)
insert @lookup(size, VLFs)values (1, 4)
insert @lookup(size, VLFs)values (2, 4)
insert @lookup(size, VLFs)values (4, 4)
insert @lookup(size, VLFs)values (8, 4)
insert @lookup(size, VLFs)values (16, 4)
insert @lookup(size, VLFs)values (32, 4)
insert @lookup(size, VLFs)values (60, 4)
insert @lookup(size, VLFs)values (128, 8)
insert @lookup(size, VLFs)values (256, 8)
insert @lookup(size, VLFs)values (512, 8)
insert @lookup(size, VLFs)values (700, 8)
insert @lookup(size, VLFs)values (1000, 8)
insert @lookup(size, VLFs)values (2000, 16)
insert @lookup(size, VLFs)values (3000, 16)
insert @lookup(size, VLFs)values (4000, 16)
insert @lookup(size, VLFs)values (5000, 16)
insert @lookup(size, VLFs)values (6000, 16)
insert @lookup(size, VLFs)values (7000, 16)
insert @lookup(size, VLFs)values (8000, 16)
update @lookup set fullsize=((50/VLFs)+1)*size, fullVLFs=((50/VLFs)+1)*VLFs, numofgrowths=((50/VLFs)+1)
selecttop 1 @maxgrowth=size, @maxVLFs=VLFs from @lookup orderby size desc
–Create tmp table to hold the info of logfiles of all databases
createtable #dblogfiles(
dbname nvarchar(128)
, RecoveryModel nvarchar(128)
, logfilename nvarchar(128)
, physical_name nvarchar(2000)
, logfileid int
, logsizeMB int
, CurrentVLFs int
, CurrentGrowth int
, CurrentGrowthType smallint)
–Declare and fill table for database loop
declare @databases table (dbname nvarchar(128),recoverymodel nvarchar(128))
insertinto @databases
select name, recovery_model_desc fromsys.databases
–Declare table for DBCC loginfo output
CREATETABLE #dbcc_loginfo
(
fileid TINYINT,
file_size BIGINT,
start_offset BIGINT,
FSeqNo INT,
[status] TINYINT,
parity TINYINT,
create_lsn NUMERIC(25, 0)
)
–Loop to gather all current data
declare @dbname nvarchar(128)
declare @recoverymodel nvarchar(128)
declare @cmd nvarchar(max)
declare @cmd2 nvarchar(max)
WHILEEXISTS(select*from @databases)
BEGIN
selecttop 1 @dbname=dbname, @recoverymodel=recoverymodel from @databases
set @cmd=‘
insert into #dblogfiles
SELECT DB_NAME()
,null
,name
, physical_name
,file_id
,( size / 128 )
,NULL
,growth
,is_percent_growth
FROM sys.database_files
WHERE type_desc = ”log”
‘
SET @CMD2=‘declare @cmd nvarchar(max)’
SET @CMD2=@CMD2 +‘SET @cmd =”’+REPLACE(@cmd,””,”””)+””+char(13)+char(10)
SET @CMD2=@CMD2 +‘Execute [‘+@dbname+‘].dbo.sp_executesql @cmd’
exec (@cmd2)
truncatetable #dbcc_loginfo
SET @cmd =‘DBCC loginfo (‘+””+ @dbname +”’) WITH NO_INFOMSGS’
INSERTINTO #dbcc_loginfo
EXEC (@cmd)
UPDATE l set CurrentVLFs=x.cnt, RecoveryModel=@recoverymodel
FROM #dblogfiles l innerjoin
(SELECT fileid, cnt=COUNT(1)FROM #dbcc_loginfo GROUPBY fileid) x
ON x.fileid=l.logfileid
WHERE l.dbname=@dbname
delete @databases where dbname=@dbname
END
select
dbname, logfilename, RecoveryModel, CurrentSizeMB=logsizeMB, CurrentNumOfVLFs=CurrentVLFs,
CurrentGrowth=CASEWHEN CurrentGrowthType=1 THEN CurrentGrowth ELSE CurrentGrowth/128 END
, CurrentGrowthType=CASEWHEN CurrentGrowthType=1 THEN‘%’ELSE‘MB’END
, RecommendedGrowthMB=ISNULL((SELECTTOP 1 size FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize), @maxgrowth)
, RecommendedNewSize=ISNULL((SELECTTOP 1 fullsize FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize),((CEILING(CAST((ISNULL(@newsizeMB,logsizeMB))ASFLOAT)/CAST(@maxgrowth ASFLOAT))))*@maxgrowth)
, RecommendedNumOfVLFs=ISNULL((SELECTTOP 1 fullVLFs FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize),((CEILING(CAST((ISNULL(@newsizeMB,logsizeMB))ASFLOAT)/CAST(@maxgrowth ASFLOAT))))*@maxVLFs)
, RecommendedNumGrowths=ISNULL((SELECTTOP 1 numofgrowths FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize),((CEILING(CAST((ISNULL(@newsizeMB,logsizeMB))ASFLOAT)/CAST(@maxgrowth ASFLOAT)))))
, AutomaticGrowthSet=CASEWHENISNULL((SELECTTOP 1 size FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize), @maxgrowth)> @maxautomaticgrowthMB THEN @maxautomaticgrowthMB ELSEISNULL((SELECTTOP 1 size FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize), @maxgrowth)END
into #result
from #dblogfiles orderby dbname
select*
,IsDiff=CASE
WHEN CurrentSizeMB<>RecommendedNewSize THEN 1
WHEN CurrentGrowthType=‘%’THEN 1
WHEN CurrentGrowth<>AutomaticGrowthSet THEN 1
WHEN CurrentNumOfVLFs >(RecommendedNumOfVLFs + 30)THEN 1
ELSE 0
END
into #result_generate
from #result
droptable #dblogfiles
droptable #dbcc_loginfo
IF @modus=‘view’
BEGIN
select*from #result_generate where dbname=isnull(@database, dbname)orderby dbname
END
ELSE
BEGIN
–modus must be generate or execute, so we need to generate the code
DECLARE @logfilename nvarchar(128)
DECLARE @GrowthMB int
DECLARE @Growths int
DECLARE @AutomaticGrowthSet int
declare @shrink_command nvarchar(max)
declare @alter_command nvarchar(max)
WHILEEXISTS(select*from #result_generate where dbname=isnull(@database, dbname)and IsDiff=1)
BEGIN
SELECTTOP 1 @dbname=dbname, @logfilename=logfilename,@recoverymodel=recoverymodel, @GrowthMB=RecommendedGrowthMB,@Growths=RecommendedNumGrowths, @AutomaticGrowthSet=AutomaticGrowthSet
FROM #result_generate where dbname=isnull(@database, dbname) and IsDiff=1 ORDERBY dbname
SET @alter_command=”
IF @recoverymodel<>‘SIMPLE’
BEGIN
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘ALTER DATABASE [‘+ @dbname +‘] SET RECOVERY SIMPLE;’
END
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘DBCC SHRINKFILE (N”’+ @logfilename +
”’ , 0, TRUNCATEONLY);’;
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘DBCC SHRINKFILE (N”’+ @logfilename +”’ , 0);’;
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘ALTER DATABASE [‘+ @dbname +‘] MODIFY FILE ( NAME = N”’+ @logfilename +”’, MAXSIZE = UNLIMITED, FILEGROWTH = ‘+
CAST(@AutomaticGrowthSet ASNVARCHAR)+‘ MB );’
IF @recoverymodel<>‘SIMPLE’
BEGIN
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘ALTER DATABASE [‘+ @dbname +‘] SET RECOVERY ‘ + @recoverymodel +‘;’
END
–Do this twice, to get the file as small as possible
IF @recoverymodel<>‘SIMPLE’
BEGIN
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘ALTER DATABASE [‘+ @dbname +‘] SET RECOVERY SIMPLE;’
END
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘DBCC SHRINKFILE (N”’+ @logfilename +
”’ , 0, TRUNCATEONLY);’;
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘DBCC SHRINKFILE (N”’+ @logfilename +”’ , 0);’;
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘ALTER DATABASE [‘+ @dbname +‘] MODIFY FILE ( NAME = N”’+ @logfilename +”’, MAXSIZE = UNLIMITED, FILEGROWTH = ‘+
CAST(@AutomaticGrowthSet ASNVARCHAR)+‘ MB );’
IF @recoverymodel<>‘SIMPLE’
BEGIN
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘ALTER DATABASE [‘+ @dbname +‘] SET RECOVERY ‘ + @recoverymodel +‘;’
END
DECLARE @Cntr int
SET @Cntr = 0
WHILE @Cntr < @Growths
BEGIN
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+
‘IF EXISTS(select * from sys.database_files where type_desc=”LOG” and name=”’+@logfilename+”’ and size/128 < ‘+CAST(((@GrowthMB )*( @Cntr + 1 ))ASNVARCHAR)+‘)
BEGIN
ALTER DATABASE [‘+ @dbname +‘] MODIFY FILE (NAME = N”’+@logfilename+”’, SIZE = ‘+CAST(((@GrowthMB )*( @Cntr + 1 ))ASNVARCHAR)+‘MB)
END;’
SET @Cntr = @Cntr + 1;
END
IF @modus=‘generate’
BEGIN
SET @alter_command=‘USE [‘+ @dbname +‘];’ +CHAR(13)+CHAR(10)+@alter_command
SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+ ‘GO’
PRINT @alter_command
END
IF @modus=‘execute’
BEGIN
SET @CMD2=‘declare @cmd nvarchar(max)’
SET @CMD2=@CMD2 +‘SET @cmd =”’+REPLACE(@alter_command,””,”””)+””+char(13)+char(10)
SET @CMD2=@CMD2 +‘Execute [‘+@dbname+‘].dbo.sp_executesql @cmd’
exec (@cmd2)
END
DELETE #result_generate where dbname=@dbname and logfilename=@logfilename
END
END
droptable #result
droptable #result_generate
GO
