C# code to detect if sql server cannot save data due to environmental issues (server offline, out of space etc) - and NOT malformed data types

websss used Ask the Experts™
I need to detect a SQL error on inserting records to a table (for disaster recovery)
Last night the server ran out of space, and all incoming data was lost as we process it on the fly

What I need is to be able to detect certain conditions like disk full etc, and NOT stuff like datatype doesn't match (i.e. saving to int column as "bob")
We get a lot of data in wrong format, so i want to discard this

If the DB is inaccessible for whatever reason (resource/environment/timeout reasons etc), I need to re-insert it back into the microsoft message queue and try and process it again
this will cause an infinite loop until the server disk space issue is fixed/server is restarted/timeout no longer issue etc), ....basically we won't have lost any valid data

Last nights error was

The transaction log for database 'TelemetryData' is full due to 'ACTIVE_TRANSACTION'.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at ATPLDAL.SqlHelper.ExecuteScalar(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
   at ATPLDAL.SqlHelper.ExecuteScalar(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
   at ......

Open in new window

I'm sure there are a bunch errors which match being unable to save data due to environment issues?
As previously said, a simple catch would NOT suffice because there are valid reasons we want to discard data, i'm only concerned with stuff like database not available, offline, space full etc

I've looked into sqlexceptions, and there seems to be a lot of Id's (found in SELECT * FROM sysmessages)
Does anyone have any code that will detect the above conditions?

catch (SqlException e) when (e.Number == 2601)
   // Do something.

Open in new window

The issue is there are so many error codes, i don't want trial and error, and hoping someone has done this before?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
hmm, while you can solve this in C# using SMO, this is a lot of redundant work. Cause it's DBA work.

Just a comment:

E.g. "run out of space" means that your space requirement calculations is wrong.

The first step is to setup SQL Server alerts for a bunch of errors which require immediate DBA actions.
Then I would use fixed size databases and monitor free space (this reduces out of space errors triggered from the OS or third party processes). Otherwise you need to monitor that also. Then you need create a threshold calculation based on the measured values and your space requirements.
@ste5an makes a valid point; if you’re running out of space when you shouldn’t be then the root problem isn’t really the code’s responsibility to manage.

Having said that — and, as you have predicted — it is going to be a lot of trial and error to get the exact behavior that you want. I’m fairly sure solutions have been created to address this or very similar issues, bit I can almost guarantee that they are not going to fit your exact situation. The good news is that there are two paths which will probably reduce the period of trial and error to either nothing or as little as possible.

1. You said you already have a lot of errors you can base your solution from; personally, I would start there. Limit your catch to SqlExceptions and have switches for a. specific numeric codes or b. the “root” generic text of specific error messages. This route will give you the finest level of control over the error handling process. However, it will also be the hardest to get “right” because you’ll want to unwind the stack trace to figure out what the most important exception was.

2. Get the official list of sql exception codes here and use a single switch statement with cases for each one that you care about. This won’t give you as much control as the option above, but it will be much faster and “safer” to implement.

Good luck!
Question has been abandoned by the OP. ste5an was first in with sound advice.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial