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

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?

i.e.
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?
websssCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin McDanielSr. DeveloperCommented:
@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!
0
Kelvin McDanielSr. DeveloperCommented:
Question has been abandoned by the OP. ste5an was first in with sound advice.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.