[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

catch the code behind a sql error

there is a specific sql error that happens few times a week.. (maybe one error every other day.. there is no pattern of when the error happens)..

I need to catch the exact process causing the error without overwhelming the profile process...

I don't know when I will see the same error again on the error log.. so if can capture all entries, I am going to capture 100s of 1000s of records in profiler.

if you knew the error number that you are after, how would you go about pinpointing the code that caused it? (if you did not know when it will occur nextx or which database etc)
0
25112
Asked:
25112
  • 5
  • 5
  • 2
4 Solutions
 
Barry CunneyCommented:
Hi 25112,
You could use SQL Alerts to help narrow down potential suspects.

Create a SQL Server event alert and specify the error number you mention.
Also, in the Response options, specify that the alert executes a job which records details, to a table, of current processes executing. Then you will know everything that was executing on your SQL Server when the error number occurred.

The following SQL can be used to get details of current processes:
SELECT 
Hostname
,nt_username
,program_name
,sp.login_time 
,qt.text AS [Parent Query]
,sp.cpu
,sp.memusage 
,sp.physical_io 
,er.total_elapsed_time
,session_Id AS [Spid]
,er.blocking_session_id 
,er.granted_query_memory 
,er.start_time 
,ecid
,DB_NAME(sp.dbid) AS [Database]
,er.status
,er.wait_resource 
,wait_type
,SUBSTRING (qt.text, (er.statement_start_offset/2) + 1, --#1
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Individual Query]
,nt_domain
FROM sys.dm_exec_requests er --#2
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
ORDER BY (sp.cpu + sp.memusage + sp.physical_io) DESC

Open in new window

 

Please let me know if you need more advice on how to create SQL Server alerts
0
 
25112Author Commented:
hi, I tried the same.. but 'job' section is greyed out.. how can you have the code kicked off at the same time as the error event?

pl see attached.

the exception is "Exception      Error: 823, Severity: 24, State: 7      .Net SqlClient Data Provider            RAZAUser      2040      95      2014-07-15 11:18:20.727      40      RAZA      823      616980      1      EDWEBP801            0XB8DD0A8021C70743BBFDEF23F08E70E1            0      POSRAR1                        RAZAUser      24      7      164986481      0                                                                                    
"
err.png
0
 
Barry CunneyCommented:
Hi 25112,
Please confirm that SQL Server Agent is running on your server.
If so, please confirm that you can crate a job by right-clicking on SQL Server Agent in Management Studio and selecting New | Job
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
25112Author Commented:
yes agent is running- confirmed.. I can create a dummy job no problem..
other jobs are running today - no problems there either..

just that in the response, i see that section greyed out..

(in the new job...'Steps' and 'Schedules' are left empty, right? notification and targets also empty.. i just say 'new job'... give it a name and go to alerts.. am i missing anything else?)
0
 
Barry CunneyCommented:
Hi 25112,
Start off by creating a new alert outside of the job
Right-click on SQL Server Agent and choose New | Alert
0
 
25112Author Commented:
ok.. that works better.. :) thanks Barry..

now will sql server be very quick to capture all those records in that millisecond when the error happens!?

i am trying to put them in a temp table to analyze it later.. would that be your approach too?
0
 
Barry CunneyCommented:
Hi 25112,
For the SELECT in my original post turn this into an INSERT....SELECT and insert to a physical table so as you can then query this table.
Yes there is the possibility that the error and underlying process only exists for milliseconds, but you have to try and attempt to capture some degree of evidence.
On the Alert you can also get it to e-mail you.

So if you get an e-mail and nothing logged by the job in the table then we plan further at this stage.
... maybe we modify the job to only do more basic logging first, like only the PID to prove that we can potentially grab some evidence when the error occurs
0
 
25112Author Commented:
Hi, you are right.. it happened quickly and nothing captured...

The error already happened  in the last hour.. but the query did not store the data... is it because the error comes and goes so fast..? and by the time the event is triggered, it is already gone?

kindly suggest your thought/idea on capturing pid?
0
 
Barry CunneyCommented:
Hi 25112,
Did you set up an e-mail operator on the Alert so that you get an e-mail when the error number 823 occurs?
0
 
25112Author Commented:
yes. that works Barry. (email)

so we have the procedure in place.. only thing is to capture in live what statement caused the error...

in see that the error happens so quickly and our alert send out the email and captures in the table.. but in those millisecond, the spid is probably relieved for another new process.. so the procedure as good as it is, does not help in this case?

profiler is the alternative, but heavy resource and makes users slow on live server app access.

thanks for any other insights you can give.
0
 
Marten RuneCommented:
Have you pursuied the root cause analysis for: Error: 823, Severity: 24,State: 7
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a4983f36-6b42-43c8-a3be-5e8ed50d2485/error-823-severity-24-state-7?forum=sqldatabaseengine

This indicates failing HW, storage to be exact, DBCC CHECKDB is your friend in this case.
This may happend when a proc writes to a faulty page, i e it depends on what the where clause directs the write/update to take Place. So it's not a faulty application, but faulty hardware.

You need to run CheckDB as soon as possible om theese databases. If they are corrupt, you want to look for new hardware for your system.

Reggards Marten
0
 
Marten RuneCommented:
Quote from Paul Randalls page: "...823 is a nasty error to get – it says that an I/O operation failed at the OS level and the I/O subsystem is causing corruption – SQL Server didn't even get a chance to check the page checksum (and potentially raise an 824 error)..."

http://www.sqlskills.com/blogs/paul/dont-confuse-error-823-and-error-832/

Read up and take action immediatily

Regards, and good luck, Marten

PS Check backups on Another system, and run DBCC CHECKDB on this restored db to see how bad this is hitting you.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now