Hi.
This is my first post to this forum, so please excuse me if I'm doing wrong.
And excuse my poor english, I am german.
First of all great thanks for your article. Been great help...
and sounding a good idea, so I added some more code and I'd ike to share the results with you.
1st: I used exactly the code provided in the article for creating the base table, only renaming the table from #errorlog to sk_errorlog according to our programming rules.
2nd: I built a second table as follows:
------------tbl-----------------
CREATE TABLE [sk_errorlog2] (
[rowID] [int] IDENTITY (1, 1) NOT NULL ,
[datum] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,-- datum is german for date
[textRow] [varchar] (4000) COLLATE Latin1_General_CI_AS NULL ,
[uhrzeit] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,-- uhrzeit is german for time
[kategorie] [varchar] (100) COLLATE Latin1_General_CI_AS NULL -- same for category
) ON [PRIMARY]
GO
--------------------------------
I will add colums like "servername" or similar later on when using it for multiple servers.
This table is meant to be a permanent depository for all sql-logs in my environment (only 5 sql-servers here)
3rd: I created some sp's for updating the second table
here is the first for inserting new rows:
----------------------SP1-----------------------
CREATE PROCEDURE [dbo].[sp_sk_errorlog]
AS
insert into sk_errorlog2 (textrow)
(
select
RTRIM(a.textrow) + COALESCE (b.textrow,'') as textrow
FROM
(
SELECT
*
FROM
sk_errorlog
WHERE
continuationrow=0
) as A
LEFT OUTER JOIN
(
SELECT
*
FROM
sk_errorlog
WHERE
continuationrow=1
) as B
ON
A.rowID = B.rowID - 1
where ( RTRIM(a.textrow) + COALESCE (b.textrow,'')
not in (select textrow from sk_errorlog2)
and a.continuationrow=0
)
)
GO
-------------------------------------------------------
the second sp will separate some values from the textrow:
-----------------sp2-------------------------
CREATE PROCEDURE [dbo].[sp_sk_errorlog_update]
AS
-----Check your collation/datetime settings here!-----
update sk_errorlog2 set datum =
(
select (case when (left (textrow,2) = '20') then left (textrow,10)
else null end)
)
,
uhrzeit =(
case ( right (left (textrow,12),1) )
when '0' then right ((left (textrow,19)),8)
when '1' then right ((left (textrow,19)),8)
when '2' then right ((left (textrow,19)),8)
else null
end
)
,
kategorie =(
case
----check for language settings here, e.g.: fehler is german for error!
----Here you can put the messages into categories, I started with the following
when textrow like '%[F,f]ehler%' or textrow like '[F,f]ehler%' then 'error'
when textrow like '%backup%' and textrow like '%datenbankprotokoll%' then 'protokoll'
when textrow like '%backup%' then 'backup'
when textrow like '%server%' then 'server'
when textrow like '%logon%' then 'logon'
else 'sonstiges'
end
)
where datum is null
----------------------------------------
/*
NOTE: the following 3 MS standard entries are the only ones without datetime information ( at least here 🙂
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
*/
So now you should have some information in a better queryable form than directly from xp_readerrorlog.
Now use the gui you like best for creating some "management dashboard" where you can list the events separated by time and category.
Looking foward to part 2 and
Keep on coding!
SK