SQL Server complains that it is full of nothing, which is, I guess, better than being full of some other things. This question has some very interesting answers, especially the one by Steven.
However, after running the script he provided, rather than seeing TRANSACTION or BACKUP as the entries in the log_reuse_wait_description column, I see only "NOTHING" and "CHECKPOINT" Every "state_desc" value is "ONLINE" and every "recovery_model_desc" is "SIMPLE". All of the "Last FULL Backup" vals are the same, too - 10/31/2014 - almost a year-and-a-half ago.
I am no DBAdmin by a long stretch, so have no idea what these things mean, or most importantly, how to prevent the err msg from occurring.
Is there anything I can do from the C# side to prevent this "full of nothing" error from occurring?
The entire err msg is:
Date: 3/7/2016 2:24:45 PM Message: Monday, March 07, 2016: Exception message: The transaction log for database 'tempdb' is full due to 'NOTHING'. Exception Source: .Net SqlClient Data Provider Exception StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, 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 asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at ReportRunner.SharedCode.SQLDBHelper.ExecuteSQLReturnDataTable(String sql, CommandType cmdType, SqlParameter[] parameters)
So the code that is mine that is referenced in that msg is ExecuteSQLReturnDataTable(), which is:
public static DataTable ExecuteSQLReturnDataTable(string connectionStr, string sql, CommandType cmdType, params SqlParameter[] parameters)
{
using (DataSet ds = new DataSet())
using (SqlConnection connStr = new SqlConnection(connectionStr))
using (SqlCommand cmd = new SqlCommand(sql, connStr))
{
cmd.CommandType = cmdType;
cmd.CommandTimeout = EXTENDED_TIMEOUT;
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
try
{
cmd.Connection.Open();
new SqlDataAdapter(cmd).Fill(ds);
}
catch (SqlException sqlex)
{
for (int i = 0; i < sqlex.Errors.Count; i++)
{
var sqlexDetail = String.Format("From ExecuteDataSet(), SQL Exception #{0}{1}Source: {2}{1}Number: {3}{1}State: {4}{1}Class: {5}{1}Server: {6}{1}Message: {7}{1}Procedure: {8}{1}LineNumber: {9}",
i + 1, // Users might get the fantods if they saw #0
Environment.NewLine,
sqlex.Errors[i].Source,
sqlex.Errors[i].Number,
sqlex.Errors[i].State,
sqlex.Errors[i].Class,
sqlex.Errors[i].Server,
sqlex.Errors[i].Message,
sqlex.Errors[i].Procedure,
sqlex.Errors[i].LineNumber);
ReportRunnerConstsAndUtils.HandleException(sqlex);
}
return null; // see https://stackoverflow.com/questions/33883125/how-can-i-defensively-code-against-randomly-referencing-table-0-and-null-value?noredirect=1#comment55530765_33883125
}
catch (Exception ex)
{
ReportRunnerConstsAndUtils.HandleException(ex);
return null;
}
return ds.Tables[0];
}
}
So is there anything I can add to this code to alleviate or even eliminate the problem?