Setting trace flag to eliminate successful backup messages in errorlog

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

Measuring Transactional Replication Latency Without Tracer Tokens

http://www.sqlsoldier.com/wp/sqlserver/measuringtransactionalreplicationlatencywithouttracertokens

Declare @Publisher sysname,
      @PublisherDB sysname;
 
-- Set Publisher server name
Set @Publisher = 'Publisher server name';
-- Set Publisher database name
Set @PublisherDB = 'Publisher database name'; 
 
-- Refresh replication monitor data
Exec sys.sp_replmonitorrefreshjob @iterations = 1;
 
With MaxXact (ServerName, PublisherDBID, XactSeqNo)
As (Select S.name, DA.publisher_database_id, max(H.xact_seqno)
      From dbo.MSdistribution_history H with(nolock)
      Inner Join dbo.MSdistribution_agents DA with(nolock)
            On DA.id = H.agent_id
      Inner Join master.sys.servers S with(nolock)
            On S.server_id = DA.subscriber_id
      Where DA.publisher_db = @PublisherDB
      Group By S.name, DA.publisher_database_id)
, OldestXact (ServerName, OldestEntryTime)
As (Select MX.ServerName, Min(entry_time)
      From dbo.msrepl_transactions T with(nolock)
      Inner Join MaxXact MX
            On MX.XactSeqNo < T.xact_seqno
            And MX.PublisherDBID = T.publisher_database_id
      Group By MX.ServerName)
Select [Replication Status] = Case MD.status
                  When 1 Then 'Started'
                  When 2 Then 'Succeeded'
                  When 3 Then 'In progress'
                  When 4 Then 'Idle'
                  When 5 Then 'Retrying'
                  When 6 Then 'Failed'
            End,
      Subscriber = SubString(MD.agent_name, 
                              Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 4, 
                              Charindex('-', MD.agent_name, 
                                    Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 5) - 
                                          (Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 4)), 
      [Subscriber DB] = A.subscriber_db, 
      [Publisher DB] = MD.publisher_db, 
      Publisher = MD.publisher,
      [Current Latency (sec)] = MD.cur_latency,
      [Current Latency (hh:mm:ss)] = Right('00' + Cast(MD.cur_latency/3600 As varchar), 2) +
                                                ':' + Right('00' + Cast((MD.cur_latency%3600)/60 As varchar), 2) +
                                                ':' + Right('00' + Cast(MD.cur_latency%60 As varchar), 2),
      [Latency Threshold (min)] = Cast(T.value As Int),
      [Agent Last Stopped (sec)] = DateDiff(hour, agentstoptime, getdate()) - 1, 
      [Agent Last Sync] = MD.last_distsync,
      [Last Entry TimeStamp] = OX.OldestEntryTime
From dbo.MSreplication_monitordata MD with(nolock)
Inner Join dbo.MSdistribution_agents A with(nolock)
      On A.id = MD.agent_id
Inner Join dbo.MSpublicationthresholds T with(nolock)
      On T.publication_id = MD.publication_id
      And T.metric_id = 2 -- Latency
Inner Join OldestXact OX
      On OX.ServerName = SubString(MD.agent_name, 
                              Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 4, 
                              Charindex('-', MD.agent_name, 
                                    Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 5) - 
                                          (Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 4))
Where MD.publisher = @Publisher
And MD.publisher_db = @PublisherDB
And MD.publication_type = 0 -- 0 = Transactional publication
And MD.agent_type = 3; -- 3 = distribution agent