Setting trace flag to eliminate successful backup messages in Sql Server error log
-- Written by Jason Qu
--
declare
@InstanceName varchar(2000),
@key varchar(2000),
@FlagName varchar(20),
@value_name VARCHAR(255),
@ArgCnt int,
@Version int --7 (SQL 7 ) 8 (SQL 2000) 9 (SQL 2005) 10 (SQL 2008)
SET NOCOUNT ON
SELECT @Version=@@MICROSOFTVERSION /
0x01000000
SET @FlagName='-T3226'
--show all global trace flag
--For SQL server 2005 and above
create table #t1(TraceFlag varchar(10),Status bit,Global bit,Session bit)
--For SQL server 2000 and below
--create table #t1(TraceFlag
varchar(10),Status bit)
insert into #t1
exec('DBCC Tracestatus(-1) WITH NO_INFOMSGS')
if exists(select 1 from #t1 where TraceFlag=substring(@FlagName,3,len(@FlagName))) begin
print 'Trace Flag(DBCC
TRACEON) '+@FlagName+' has been set on '+@@servername
end else begin
--SET The trace flag in cache, this takes effect
immediately
print 'Setting Trace
Flag(DBCC TRACEON) '+@FlagName+' on '+@@servername
DBCC TRACEON(3226,-1) WITH NO_INFOMSGS
end
select * from #t1
drop table #t1
--SET The trace flag in startup
parameters, this will take effect after the service restarts
if (@Version>8) begin
--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft
SQL Server\Instance Names\SQL
exec master..xp_instance_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\\Microsoft\\Microsoft
SQL Server\\Instance Names\\SQL',
@value_name='MSSQLSERVER', --for named instance,
should change this to the instance name
@value=@InstanceName output
print @InstanceName
set @key= N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\'+@InstanceName+'\\MSSQLServer\\Parameters'
end else begin
set @key= N'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer\\Parameters'
end
print @key
--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft
SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters
--store current params
create table #t(Value varchar(255),data varchar(255))
insert #t
EXEC master..xp_instance_regenumvalues
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = @key
select * from #t
SELECT @ArgCnt=count(*) from #t
SET @value_name='SQLArg'+CAST(@ArgCnt AS VARCHAR(3))
--print @value_name
if exists(select 1 from #t where data=@FlagName) begin
print 'Trace Flag(Startup
parameters) '+@FlagName+' has been set on '+@@servername
end else begin
print 'Setting Trace
Flag(Startup parameters) '+@FlagName+' on '+@@servername
EXEC master..xp_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key=@key,
@value_name=@value_name,
@type='REG_SZ', @value=@FlagName
end
drop table #t
