error: 9001 , the log for tempdb is not available

Question

Post reply

error: 9001 , the log for tempdb is not available

schauhan13

SSC Eights!

Points: 901
More actions
February 25, 2010 at 7:45 am

#211736

I am a vb.net programmer. I created following stored procedure on SQL Server 2000:-
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
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 now me 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
It seems like my stroed procedure is corrupting tempdb database. It was just my guess. I wanted to make sure from SQL experts. Please help me here, it is blow up my whole sql server. I had to restart it.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

Have you run this from just SSMS or SQLCMD?

I doubt this is corrupting tempdb, but it would be good to run this without a VB client to see more details.

schauhan13 SSC Eights! Points: 901 More actions · Answer 2

I have run this on Sql Server Management Studio for SQL Server 2005(express edition) and also through enterprise manager for sql server 2000.When i just run the query it never gave me any error. But when i tried through vb.net , i got the above error! I have no experience on SQL SERver. Can you explain me more so that i can check what the problem is and how to solve it?

schauhan13 SSC Eights! Points: 901 More actions · Answer 3

i ran following query and it didnot provide me any error.

dbcc checkdb (tempdb) with no_infomsgs,all_errormsgs

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 4

What's the error? You have put something in the subject, but that doesn't show up. The error needs to be in the post. Also, there are some non-English characters in your original post, so don't know what is wrong.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

CHECKDB of tempdb on 2000 doesn't really do much - so unsurprising it doesn't show any errors.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005