Does SQL Server 2000 log queries that produce errors anywhere?

Question

I've got a distributed deployment of ±20 clients using the same stored procedure to create a record and then attach detail records. We've noticed gaps in the identity fields of the master record. We are not using a transaction in this instance so the only reason we can come up with for the gaps is that one of the developers is swallowing an exception somewhere. Is there a list of failed queries or queries that threw an exception logged somewhere?

Are there any other suggestions to get to the problem queries besides:

• Using SQL Profiler
• Creating a mirror table that has the same signature but with all fields nullable and without references, then expanding the stored procedure to insert into that table first and then seeing if some of the input is blank/bad

Answer 1

Have you checked the SQL Server error logs that you can find in Enterprise Manager?

I don't have Enterprise Manager in front of me, but I imagine its either under the Security or Management section.

Answer 2

No way to get that information out of Sql 2000 other than using a trace (though I'd recommend a server-side-trace over using profiler as usual). You could easily limit the trace to include batchstart/end events that include just that table along with transaction start/end and the exception event classes.

Note that there are also other ways you could be getting identity gaps, including:

• ROLLBACKs (used identities in a non-committed transaction aren't rolled back, they just continue moving on up, or down if you've configured the identity as such)
• Replication
• Explicit updates to the identity seed / current value
• Deletes
• Etc.

Answer 3

Gaps are generally from when an insert was rolled back. Doesn't have to be in an explicit transaction to be rolled back. For instance, suppose you havea date field and someone sends an input value for that field which is not a valid date. The insert would fail and be rolled back causing a gap in the identity values.

You cannot ever expect identity fields to be without gaps. It simply won't happen.

Answer 4

SQL Server 2000 (and later) does not automatically log queries that generate mere "data" errors. It will log queries that generate serious errors (e.g. RAISERROR level 20 and up), and any such errors will be logged in the Windows Application Event log. However, it seems very likely that whatever is causing your games will not be generating these kinds of errors, as you'd have probably noticed other problems by now.

One way to try and locate this activity is, as chadhoc said, to set up a trace to watch for it. Doing so acrosss 20+ servers across [how many?] days, and then analyzing the results, qualifies as a non-trivial task. (I'd configure a first pass with Exception events and, depending on your application, one of Batch Start, stored procedure start, or RPC start.)

Another tactic: rather than a mirror table populated by "expanding" your stored procedure(s), try an INSERT trigger on the table that populates a log table. (This would catch all inserts, not just those done by your stored procedure.) The log table would track the ID, time of insertion, and any additional information you might find useful (who performs the login, the calling application, whatever other data be useful for debugging). It may end up looking like a mirrored table, but it doesn't have to be. If gaps still appear, then you'd know for sure you had transactions being rolled back. (And remember, if just that first INSERT statement fails and the row is never inserted, it still "uses up" an identity value.)