?
Solved

Can't read SQL error log

Posted on 2005-05-09
13
Medium Priority
?
762 Views
Last Modified: 2008-02-01
I want to read the sql error log file. I don't want to use the sp_readerrorlog, so I tried using the openrowset, but since the errorlog is without the txt suffix I can't read it.

I tried the following command:
select * from
OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=D:\MSSQL\MSSQL$URI_2K_1\LOG;Extensions=*.*',  'select * from ERRORLOG')

Thanks for your help

0
Comment
Question by:urim
  • 4
  • 4
  • 2
10 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13957729
why dont you want to use the stored procedure ?
0
 
LVL 4

Author Comment

by:urim
ID: 13957741
For large files it takes too long to run

I want to parse the log file and search for keyword, so I insert the log file to #temp by
insert into #temp exec sp_readerrorlog

and it takes more than 5 minutes to complete. I try running only the exec sp_readerrorlog and I confirm that it is the problem. I thought if I read the log I might get better performance, since I don't want the continue indication
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 1000 total points
ID: 13957762
It will take equally as long using the access driver (been there bought the tshirt).


you are better to periodically cycle the logs to ensure they dont get to big.  

SQL normally only cycles the log on a restart but you can do it easily with my sp,  simply add a call to sql agent:



CREATE PROCEDURE usp_MGT_CycleLog(@MaxLogSize int) AS
BEGIN

CREATE TABLE #logs(id int,dt varchar(20) ,ls int)
INSERT #logs EXEC sp_enumerrorlogs
IF EXISTS( SELECT 1 FROM #logs WHERE id=0 AND ls@MaxLogSize)
      EXEC sp_cycle_errorlog

END
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Author Comment

by:urim
ID: 13957880
I can't cycle the log, since its not my installation ;)
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13958043
I would suggest sticking with the stored proc then,  as you will not gain any performance by using a different oledb provider.
0
 
LVL 4

Author Comment

by:urim
ID: 13958220
ok, still is there anyway I can read the error log myself?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13961302
ShogunWade,

Do you get the feeling you are not getting your message across?

Anthony
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13961317
yup.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14135345
>>if there are no objections within 4 days<<
I object:  Points should be awarded to ShogunWade.
0
 
LVL 4

Author Comment

by:urim
ID: 14147683
The question was how to read the error log without using the sp_readerrorlog.
With all the respect to ShogunWade, I didn't get any alternative to read the error log without using the sp_readerrorlog.

0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question