question

Sonia avatar image
Sonia asked

error 9001: tempdb log is not available

I have SQl Server 2000. I wrote following stored procedure for one of my queries. I am .net programmer do not know much about sql. All of a sudden from last week , our tempdb database is blown up and i have to restart sql server each time to get it working. i searched online for solutions , one of the links said that sometimes using temporary tables can do that. Here is my stored procedure , i just want to know , if my stored procedure is the potential cause of this error and if yes, i need help of all the experts out there to solve this issue.

CREATE PROCEDURE [dbo].[sp_GetLocations]                     
 -- Add the parameters for the stored procedure here                    
 (                    
@loc_required int                    
)                    
AS                    
BEGIN                    
 -- SET NOCOUNT ON added to prevent extra result sets from                    
 -- interfering with SELECT statements.                    
 SET NOCOUNT ON;                    
                    
    -- Insert statements for procedure here                    
declare  @gaps table (id int identity(1, 1),Lct varchar(10), locid int)                    
insert into @gaps(Lct, locid)                     
select location, id from location where filled =0 order by id                    
                    
declare @templocation table(from_cell varchar(10),to_cell varchar(10),no_of_free_cells int)                    
                    
insert into @templocation                    
select g1.Lct as 'from cell', g2.Lct as 'to cell' , [number of free cells] from                     
(select     min(locid) as 'from_cell' , max (locid) 'to_cell'  , count(Lct) as 'number of free cells' from @gaps                     
group by left(lct, 1), locid-id ) x                    
 inner join @gaps g1 on  x.from_cell = g1.locid                    
 inner join @gaps g2 on x.to_cell = g2.locid                    
                    
declare @finallocation table(from_cell varchar(10),to_cell varchar(10),no_of_free_cells int)                    
                    
insert into @finallocation                    
select * from @templocation where no_of_free_cells >= @loc_required order by no_of_free_cells,from_cell                    
                    
select top 1 * from @finallocation                    
                    
                    
END                    
                    
GO                    
                    
                    
And my application gives me following error:-                    
Exception type: System.Data.SqlClient.SqlException                    
 ExceptionMessage: Warning: Fatal error 9001 occurred at Feb 24 2010  9:27PMက催՛ꁄ ꀸ ꁸ 灴Ʋꁄ ꀸ . Note the error and time, and contact your system administrator.                    
 Source: .Net SqlClient Data Provider                     
 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.ConsumeMetaData()                    
   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)                    
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)                    
   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.ExecuteReader()                    
   at Location.findlocations(Int32 loc_required) in D:\Inetpub\wwwroot\AssemblyParts\App_Code\Location.vb:line 23                    
   at RotorAssemby.gvRotor_RowCommand(Object sender, GridViewCommandEventArgs e) in D:\Inetpub\wwwroot\AssemblyParts\RotorAssembly.aspx.vb:line 263                    

i ran dbcc checkdb (tempdb) with all_error_msgs. But it didnot return me any msgs. Thanks in advance

sql-server-2000dbcctemporary-tables
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
RickD avatar image
RickD answered

You're not using Temp tables, @ denotes a variable and table variables were not available in SQL 2000. Change them to # and it might be an issue with TempDB, but I am guessing it is just a problem with the SQL as it will not be able to tell what @gaps, @templocation and @finallocation are supposed to be.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.