/*
xp_readerrorlog

 
Parameter Name	Usage
@ArchiveID		Extension of the file which we would like to read.
				0 = ERRORLOG/SQLAgent.out
				1 = ERRORLOG.1/SQLAgent.1  and so on

@LogType		1 for SQL Server ERRORLOG (ERRORLOG.*)
				2 for SQL Agent Logs (SQLAgent.*)
@FilterText1	First Text filter on data
@FilterText2	Another Text filter on data. Output would be after applying both filters, if specified
@FirstEntry		Start Date Filter on Date time in the log
@LastEntry		End Date Filter on Date time in the log
@SortOrder		'asc' or 'desc' for sorting the data based on time in log.
*/

create table #errorlog 
(
LogDate datetime2(0),
ProcessInfo varchar(32),
[Text] nvarchar(max)
)


Declare @InstanceName nvarchar(4000),
        @ArchiveID int,
		@LogType int,
       @Filter1Text nvarchar(4000),
       @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,
        @ArchiveID=null,--当前日志
		@logtype=null, --1 for SQL Server ERRORLOG (ERRORLOG.*) 2 for SQL Agent Logs (SQLAgent.*)
      @Filter1Text=null,--筛选条件
       @Filter2Text=null,--进一步筛选
        @FirstEntry=(select convert(date,sqlserver_start_time) from  sys.dm_os_sys_info),--服务启动时间
        @LastEntry=getdate()--执行时间
--1.Network
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 2,@Filter1Text=  N'Network',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 

--1.Network
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'Network',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 

--2.Locked Pages in Memory (LPIM)
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'Locked',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 


--3.RAM
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'MB of RAM',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 

--4.CLR
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'CLR',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 

--5.licensing
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'licensing',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 

--6.DBCC TRACEON or DBCC TRACEOFF
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'DBCC TRACE',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 

--7.Trace
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'TRACE',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 

--8.CHECKDB
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'CHECKDB',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 

--9.Configure Change
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'Configuration',@Filter2Text=N'changed', @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 

--10.DB Setting Change
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'database',@Filter2Text=N'setting', @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 
--11.Deadlock information
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'Deadlock',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 
--12.FlushCache
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'FlushCache',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 
--13.AppDomain
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'AppDomain',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 
--14.How long it takes for a database to come online
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'online',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 
--15.endpoints
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'endpoints',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 
--16.Success or failure of Service Principal Name (SPN) registration
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'SPN',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 
--17.i/o issue
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'I/O',@Filter2Text=N'encountered', @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 
--18.recovery db
insert into #errorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID=@ArchiveID,@LogType= 1,@Filter1Text=  N'Recovery',@Filter2Text=null, @FirstEntry=@FirstEntry, @LastEntry=@LastEntry,@SortOrder= N'desc',@InstanceName= @InstanceName 


select   * from #errorlog


drop table #errorlog