how to check system errorlog for a previous date, It''s very urgent...

Question

how to check system errorlog for a previous date, It''s very urgent...

shashi kant

SSCommitted

Points: 1659
More actions
January 31, 2007 at 9:53 pm

#153410

Hi every one how to check system error log file using query
i know there is one option in enterprise manager
by clicking on the management tree view there is an sql server log

my problem is that sql generate errors when there is an error in executing any sql code, but sql store it's own error in some error log ,
i just want to know how i check the exact error occur on 19 jan 2007 by my application programme for executing the sp...?????
Thanx in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

Hi,

Create a sqldiag.txt file which will have all the list of the error by date and even the system configuration etc details.

1. open the cmd prompt

2. Navigate to C:\Program Files\Microsoft SQL Server\MSSQL\Binn folder

3. Run the SQLdiag.exe

4. This file will create sqldiag.txt in the C:\Program Files\Microsoft SQL Server\MSSQL\LOG folder

5. Open the file

This will give you all the error information by date

Hope this help.

Minaz AMin

"More Green More Oxygen !! Plant a tree today"

shashi kant SSCommitted Points: 1659 More actions · Answer 2

thanx Minaz AMin for your suggestion

but u know there is an @@error global function in sql

which give us the deatail of error and Error type. when i got the error . i need the information that this error where store in sql server 2000. suppose i want an error which occurs 20 days back how should i found this which query should i use for finding the error.

thankx for your valuable new suggestion , through i this i learn a new thing

Minaz Amin SSChampion Points: 11052 More actions · Answer 3

Well try this :

Create a new table to store the error number/type, message , description, Query text and date

If @@error is not zero then insert the error information into this table. You will be able to get the information on date wise / Querywise by searching this table.

Hope this help

From BOL:

Adding User-Defined Error Messages

sp_addmessage

Minaz Amin

"More Green More Oxygen !! Plant a tree today"

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

u can use sp_readerrorlog to read any of ur error log files. just google this and u will find a plenty of details abut this procedure.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

but u know there is an @@error global function in sql

@@Error just returns the error statement of the previous command in a TSQL batch. If the previous command executed without error, then it's 0, otherwise it has the error code in it.

You won't catch errors with severity or 19 or higher using @@Error as errors with a severity of 19 or higher always immediatly terminate the user connection.

They'll also be logged in the windows event log.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shashi kant SSCommitted Points: 1659 More actions · Answer 6

thanks

GilaMonster

your suggestion is great and i check this. i clear my doubt so i can say 100% to some one about this ......