Extraer SQl Server Logs

Funciona en SQL 7, 200 y 2005. Extae los logs de los últimos dos días (para más o menos días podéis modificar la tabla temporal que recoge la información de xp_enumerrorlog)



use MASTER
go

SET NOCOUNT ON
DECLARE @archive int
if (SELECT @@VERSION ) like '%Microsoft SQL Server 2005%'

goto yukon

else

goto twokey

twokey:




CREATE TABLE #Errors (Text varchar(255), ID int)
CREATE INDEX idx_msg ON #Errors(ID, Text)


CREATE TABLE #tmp
(
archive int
,[date]datetime
,[size] int -- no retrieved by xp_enumerrorlogs 7.0
)
--SO

IF (SELECT @@version ) like '%7.00%'

INSERT INTO #tmp(archive,[date])
EXEC xp_enumerrorlogs

ELSE
INSERT INTO #tmp EXEC xp_enumerrorlogs



DECLARE dbcursor cursor FOR SELECT archive
FROM #tmp WHERE archive <>0 AND
datediff(day,[date],getdate())< 3 -- errorlogs from two days old
ORDER BY archive DESC
OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @archive
WHILE @@fetch_status = 0

BEGIN
INSERT #Errors EXEC xp_readerrorlog @archive

FETCH NEXT FROM dbcursor INTO @archive
END

CLOSE dbcursor

DEALLOCATE dbcursor

DROP TABLE #tmp
INSERT #Errors EXEC xp_readerrorlog

-- to retrieve from the last errorlog
-- since with 0 as parameter won't return data but an error

declare @date1 nvarchar(11)
declare @date2 nvarchar(11)

select @date1 = convert(char(10),getdate(),121)
select @date2 = convert(char(10),getdate()-1,121)

set @date1 = @date1 +'%'
set @date2 = @date2 +'%'


PRINT ' '
SELECT 'SQL SERVER ERROR LOG. CREATION DATE: '+ CONVERT(char(20),getdate(),120)
print '=========================================================================================================='
SELECT Text FROM #Errors WHERE Text NOT LIKE '%Log backed up%' AND
Text NOT LIKE '%.TRN%' AND Text NOT LIKE '%Database backed up%' AND
Text NOT LIKE '%.BAK%' AND Text NOT LIKE '%Run the RECONFIGURE%' AND
Text NOT LIKE '%Copyright (c)%'AND
Text NOT LIKE '%Login succeeded%'AND
(Text like @date1 or Text like @date2)

print '==========================================================================================================='


DROP TABLE #Errors
goto fin

yukon:





create table #errors2 (logdate datetime, process varchar(16), texto varchar (800))
CREATE TABLE #tmp2
(
archive int
,[date]datetime
,[size] int
)

insert into #tmp2 exec xp_enumerrorlogs

DECLARE dbcursor cursor FOR SELECT archive
FROM #tmp2 WHERE archive <>0 AND
datediff(day,[date],getdate())< 3 -- errorlogs from two days old
ORDER BY archive DESC
OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @archive
WHILE @@fetch_status = 0

BEGIN
INSERT #Errors2 EXEC xp_readerrorlog @archive

FETCH NEXT FROM dbcursor INTO @archive
END

CLOSE dbcursor

DEALLOCATE dbcursor

DROP TABLE #tmp2
INSERT #Errors2 EXEC xp_readerrorlog

-- to retrieve from the last errorlog
-- since with 0 as parameter won't return data but an error

declare @date3 datetime
declare @date4 varchar(12)

select @date3 = getdate()-1


select @date4= convert(varchar(11),@date3,121)





PRINT ' '
PRINT ' '
SELECT 'SQL SERVER ERROR LOG. CREATION DATE: '+ CONVERT(char(20),getdate(),120)
print '=========================================================================================================='
SELECT LogDate,Process, rtrim(Texto) as 'Mssge' FROM #Errors2 WHERE Texto NOT LIKE '%Log backed up%' AND
Texto NOT LIKE '%.TRN%' AND Texto NOT LIKE '%Database backed up%' AND
Texto NOT LIKE '%.BAK%' AND Texto NOT LIKE '%Run the RECONFIGURE%' AND
Texto NOT LIKE '%Copyright (c)%'AND
Texto NOT LIKE '%Login succeeded%'AND
logdate > @date4
order by logdate

print '==========================================================================================================='


DROP TABLE #Errors2

fin: