tempdb log is full but cannot do anything about it. 2

[Trusts]

Hi all,

I am developing a vb.net 2005 app with SQL Server 2005. In a run of the app the code stopped where I was filling a dataset (the data provided through a stored procedure). The error says "The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."

I tried this using the database backup dialog but received a failure stating that the tempdb cannot be backed upped or restored.

So I shrunk the files. That did recover some room, but when I ran the app again, the same problem occured. Now I don't know what to do. Any ides?

Thanks, KB

The full output of the error follows:



System.Data.SqlClient.SqlException was unhandled by user code
Class=17
ErrorCode=-2146232060
LineNumber=18
Message="The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."
Number=9002
Procedure="get_existing_deals"
Server="WPC06C09"
Source=".Net SqlClient Data Provider"
State=2
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.ProviderBase.DataReaderContainer.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
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 pages_ExistingDeals.fill_view() in C:\Deal Capture Local\Deal Capture Web Dev\pages\ExistingDeals.aspx.vb:line 92
at pages_ExistingDeals.Page_Load(Object sender, EventArgs e) in C:\Deal Capture Local\Deal Capture Web Dev\pages\ExistingDeals.aspx.vb:line 34
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

 

[cyno]

This happens when you are running a big transaction where the tempdb is being used. It will grow up automatically and will allow the transaction to complete. But If you dont have enough disk space it will fail. If you could make transactions simple that would help.

 

[Trusts]

thanks cyno, I'll see what I ca change

 

[SQLBill]

Or it could be the log file can't grow fast enough. I would check the auto-grow settings for the log file in that database (use Enterprise Manager, right click on the database, select Properties, go to the tab for the Log files). Usually the auto-grow is set to %. I suggest setting it to MB. Try it with 500 MB. If that fails, increase it.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

BTW-further explaination....

Let's say your transaction is 100 MB, but the transaction log file only has 10 MB. It needs to autogrow. If the autogrow is set to 10 MB, it needs to grow 10 times for that one transaction and it will fail because it can't grow fast enough. Same if it is set for a percentage that is too small.

-SQLBill

Posting advice: FAQ481-4875

 

[Trusts]

Yeah, I have it on autogrow, but am not at work now, so can't see what settings autogrow are set at. But I will change it accordingly, making sure it is by MB not percentage. Thanks!

KB