Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Microsoft SQL Server Error Log Scanning Expand / Collapse
Author
Message
Posted Tuesday, February 15, 2011 4:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 1:00 PM
Points: 368, Visits: 544
Great Article Rudy.

anyways people can also use XP_ReadErrorLog 0,1,'string_you_want_To_search'

like xp_readerrorlog 0,1,'error'
provided you are using SQL Server 2005+ versions


for earlier version this would be a good option.


Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #1064150
Posted Tuesday, February 15, 2011 9:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 318, Visits: 1,129
Sarab. (2/15/2011)
Great Article Rudy.

anyways people can also use XP_ReadErrorLog 0,1,'string_you_want_To_search'

like xp_readerrorlog 0,1,'error'
provided you are using SQL Server 2005+ versions


for earlier version this would be a good option.


Thanks Sarab.

Nicely done. Your idea should make it easier to collect the data needed. Once we get rid of our SQL 2000 servers I will update my code.

Rudy



Post #1064351
Posted Tuesday, February 15, 2011 4:54 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:32 PM
Points: 18,068, Visits: 16,111
Thanks for the helpful script.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1064645
Posted Friday, September 6, 2013 3:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:12 AM
Points: 47, Visits: 1,427
I just don’t understand....

From
CREATE TABLE [dbo].[ErrLogData](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[LogDate] [datetime] NULL,
[ProcessInfo] [nvarchar](50) NULL,
[LogText] [nvarchar](4000) NULL,
[SQLServerName] [nvarchar](150) NULL,
PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET STATISTICS IO ON
GO

-- Remove older data
IF EXISTS (SELECT * FROM [ErrorLogStorage].[dbo].[ErrLogData])
BEGIN
TRUNCATE TABLE [ErrorLogStorage].[dbo].[ErrLogData]
END
DECLARE @SQLCmd VARCHAR(1024)
SELECT @SQLCmd = 'Insert Into [ErrorLogStorage].dbo.ErrLogData (LogDate, ProcessInfo, LogText) Exec master..xp_readerrorlog'
EXEC (@SQLCmd)

-- Cycle through the ErrLogData table and insert the server''s name
DECLARE SrvName_Cursor CURSOR FOR
SELECT [SQLServerName] FROM [ErrorLogStorage].[dbo].[ErrLogData] WHERE [SQLServerName] IS NULL
OPEN SrvName_Cursor
FETCH NEXT FROM SrvName_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE [ErrorLogStorage].[dbo].[ErrLogData] SET [SQLServerName] = @@servername
FETCH NEXT FROM SrvName_Cursor
END
CLOSE SrvName_Cursor
DEALLOCATE SrvName_Cursor
GO

SET STATISTICS IO OFF
GO

Table 'ErrLogData'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ErrLogData'. Scan count 0, logical reads 225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 232, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ErrLogData'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ErrLogData'. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ErrLogData'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To

SET   STATISTICS IO ON
GO

-- Remove older data
IF EXISTS (SELECT * FROM [ErrorLogStorage].[dbo].[ErrLogData])
BEGIN

TRUNCATE TABLE [ErrorLogStorage].[dbo].[ErrLogData]

END

INSERT
INTO [ErrorLogStorage].dbo.ErrLogData
(
LogDate
,ProcessInfo
,LogText
)

EXEC master.dbo.xp_readerrorlog

UPDATE [ErrorLogStorage].[dbo].[ErrLogData]
SET [SQLServerName] = @@SERVERNAME


SET STATISTICS IO OFF
GO

Table 'ErrLogData'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ErrLogData'. Scan count 0, logical reads 225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 232, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ErrLogData'. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Or


CREATE TABLE [dbo].[ErrLogData](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[LogDate] [datetime] NULL,
[ProcessInfo] [nvarchar](50) NULL,
[LogText] [nvarchar](4000) NULL,
[SQLServerName] [nvarchar](150) DEFAULT(@@SERVERNAME) NOT NULL,
PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


SET STATISTICS IO ON
GO

-- Remove older data
IF EXISTS (SELECT * FROM [ErrorLogStorage].[dbo].[ErrLogData])
BEGIN

TRUNCATE TABLE [ErrorLogStorage].[dbo].[ErrLogData]

END

INSERT
INTO [ErrorLogStorage].dbo.ErrLogData
(
LogDate
,ProcessInfo
,LogText
)

EXEC master.dbo.xp_readerrorlog

SET STATISTICS IO OFF
GO

Table 'ErrLogData'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ErrLogData'. Scan count 0, logical reads 225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 232, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


?
Post #1492114
Posted Friday, September 6, 2013 4:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:24 AM
Points: 4, Visits: 107
Very helpful article.
One thing that I notice is that update of log table to include @@servername is not not necessary as the command xp_readerrorlog is always executed on the same server.
we can effectively use @@servername in the select clause

SELECT

,[LogID]
,[LogDate]
,[ProcessInfo]
,[LogText]
,SQLServerName=@@ServerName
FROM [ErrorLogStorage].[dbo].[ErrLogData]......
Post #1492136
Posted Friday, September 6, 2013 8:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:18 AM
Points: 29, Visits: 162
Looks nice and easy. Great for an Implementation Engineer who is not a DBA, but would benefit from quickly checking the SQL Error Log if errors crop up during an implementation.
Post #1492272
Posted Friday, September 6, 2013 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 9:28 AM
Points: 14, Visits: 43
Good article. Updating the SQLServerName column could just as easily be updated in real-time through a default constraint:

CREATE TABLE [dbo].[ErrLogData](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[LogDate] [datetime] NULL,
[ProcessInfo] [nvarchar](50) NULL,
[LogText] [nvarchar](4000) NULL,
[SQLServerName] [nvarchar](150) NOT NULL,
PRIMARY KEY CLUSTERED
(
[LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].ErrLogData] ADD CONSTRAINT [DF_dbo_ErrLogData_SQLServerName] DEFAULT (@@servername) FOR [SQLServerName]
GO

This way you could set up an SSIS job to loop through your more critical instances.



James E Bothamley
Sr DBA
Supreme Court of Wyoming
JBothamley@Courts.State.WY.US
JamesBothamley@Wyoming.Com

"Once in a while you can get shown the light
in the strangest of places if you look at it right"

JG 1942-1995 RIP
Post #1492276
Posted Friday, September 6, 2013 8:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 18, 2014 3:29 AM
Points: 99, Visits: 678
Please pay attention: this stored procedure, xp_readerrorlog, can hang and you cannot kill it's process from SQL.
I am experimenting now with sp_readerrorlog.
Post #1492282
Posted Friday, September 6, 2013 11:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 1:44 PM
Points: 11, Visits: 532
That's true Youri, but I'm pretty sure that was fixed at some point in the 2005 and 2008 builds. Somewhere between SP3 and SP4 for 2005 if I remember correctly.
Post #1492346
Posted Friday, September 6, 2013 11:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 18, 2014 3:29 AM
Points: 99, Visits: 678
Unfortunately, it has not been fixed... I don't have the global view, but I guess that this issue has not been fixed AT ALL.
I manage about 100 SQL Server instances from 2000 to 2012 and at the moment I get this problem with 2005 SP4 and 2008 SP1; earlier I had the same with other versions.

Even more bad information: such processes not only cannot be killed but also caused high CPU percentages. The only way to get rid of them is to restart SQL. But this is not always a good solution for production environments.
Post #1492349
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse