Watch 0 Star 0 Fork 0

發糞塗牆 / MVPOpsSQLApache-2.0

Join us
Explore and code with more than 2 million developers,Free private repositories !:)
Sign up
聚焦在使用各种辅助技术为数据库运维带来更多的价值并减轻DBA和其他运维人员的工作负担。 spread retract

Clone or download
ReadErrorlogs 6.13 KB
Copy Edit Web IDE Raw Blame History
/*
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

Comment ( 0 )

Sign in for post a comment

SQL
1
https://gitee.com/dba_huangzj/MVPOps.git
git@gitee.com:dba_huangzj/MVPOps.git
dba_huangzj
MVPOps
MVPOps
master

Help Search