Reading database timed out
in [VB.NET]
|
From: fniles on 11 May 2010 16:59 Today, I did an UPDATE statement on SQL Server Management Studio on a few records. Now, from a VB.NET program when I tried to get those records that I updated, when I go thru the datareader, when it gets around those records that I updated, it timed out. Even though getting the same query thru SQL Server Management Studio is very fast. What can I do to fix it ? Thank you. This is the UPDATE statement that I did UPDATE tickdata1min set openprice = openprice *10,highprice = highprice *10, lowprice = lowprice *10,closeprice = closeprice *10 where symbol = 'YMM0' AND SEQUENCENUMBER >= '20100511-0950' AND SEQUENCENUMBER <= '20100511-1038' AND CLOSEPRICE < 100 This is the Stored Procedure that I use exec GetData1Min @Symbol='YMM0',@SeqNumLow='20100511-1000' Using command = New SqlCommand(sql, connection) Dim reader As SqlDataReader command.CommandType = CommandType.Text command.CommandTimeout = 300 reader = command.ExecuteReader() While reader.Read outputData = "" currentSequenceNumber = reader(0) -> timed out when it gets to SEQUENCENUMBER >= '20100511-0844' If I do exec GetData1Min @Symbol='YMM0',@SeqNumLow='20100511-1040', it won't timed out. If I do GetData1Min for other symbols that I didn't update today, it won't timed out. It only timed out on the symbols that I did an "UPDATE" statement thru SQL Server Management Studio. ************************************************************************************************ This is the table CREATE TABLE [dbo].[TickData1Min]( [SequenceNumber] [char](13) NOT NULL, [CommodityCode] [char](10) NOT NULL, [MonthYear] [char](2) NULL, [Symbol] [char](12) NOT NULL, [OpenPrice] [decimal](16, 4) NULL, [HighPrice] [decimal](16, 4) NULL, [LowPrice] [decimal](16, 4) NULL, [ClosePrice] [decimal](16, 4) NULL, [Volume] [numeric](18, 0) NULL CONSTRAINT [DF_TickData1Min_Volume] DEFAULT ((0)), [Date] [datetime] NULL, CONSTRAINT [PK_TickData1Min] PRIMARY KEY CLUSTERED ( [Symbol] ASC, [SequenceNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF This is the Stored Procedure ALTER Procedure [dbo].[GetData1Min] ( @Symbol VarChar(10), @SeqNumLow VarChar(13) ) as Begin Select sequencenumber, openprice,highprice,lowprice,closeprice,volume From TickData1Min Where [Symbol] = @Symbol AND SequenceNumber >= @SeqNumLow Order By SequenceNumber OPTION (RECOMPILE) End
From: Erland Sommarskog on 11 May 2010 17:43 fniles (fniles(a)pfmail.com) writes: > Today, I did an UPDATE statement on SQL Server Management Studio on a few > records. > Now, from a VB.NET program when I tried to get those records that I > updated, when I go thru the datareader, when it gets around those > records that I updated, it timed out. Even though getting the same query > thru SQL Server Management Studio is very fast. > What can I do to fix it ? It seems that you at some point started a transaction in SSMS, and then forgot to roll it back or commit it. Run COMMIT TRANSACTION in your query window until you get an error message, tell you that there is no active transaction. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: fniles on 11 May 2010 19:50 Thank you The database is pretty big at 9 gig, and the 1 of the table that I updated has about 13 million records in it. Will the COMMIT TRANSATION run a long time for a table this big ? Do I just type in COMMIT TRANSATION in the SQL Server Management Studio query window ? > Run COMMIT TRANSACTION in your query window until you get an error > message, > tell you that there is no active transaction. What do i do when I get an error ? What do you mean by "tell you that there is no active transaction" ? "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D75F15F359CCYazorman(a)127.0.0.1... > fniles (fniles(a)pfmail.com) writes: >> Today, I did an UPDATE statement on SQL Server Management Studio on a few >> records. >> Now, from a VB.NET program when I tried to get those records that I >> updated, when I go thru the datareader, when it gets around those >> records that I updated, it timed out. Even though getting the same query >> thru SQL Server Management Studio is very fast. >> What can I do to fix it ? > > It seems that you at some point started a transaction in SSMS, and then > forgot to roll it back or commit it. > > Run COMMIT TRANSACTION in your query window until you get an error > message, > tell you that there is no active transaction. > > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: fniles on 11 May 2010 21:01 I think whatever problem I have, now is causing connection status to be suspended. Now when I do sp_who, every few minutes I see a new connection to the database and the status is "suspended". This new connection comes from the same VB.NET program that inserts data to the database. After a while, the program will get the error "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." What does the status "suspended" mean, and will it use up all the connection from the pool ? How can I fix this ? "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D75F15F359CCYazorman(a)127.0.0.1... > fniles (fniles(a)pfmail.com) writes: >> Today, I did an UPDATE statement on SQL Server Management Studio on a few >> records. >> Now, from a VB.NET program when I tried to get those records that I >> updated, when I go thru the datareader, when it gets around those >> records that I updated, it timed out. Even though getting the same query >> thru SQL Server Management Studio is very fast. >> What can I do to fix it ? > > It seems that you at some point started a transaction in SSMS, and then > forgot to roll it back or commit it. > > Run COMMIT TRANSACTION in your query window until you get an error > message, > tell you that there is no active transaction. > > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: fniles on 11 May 2010 21:40
I ran COMMIT TRANSACTION, no error. After that it looks like all my problems were fixed. Thanks ! "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D75F15F359CCYazorman(a)127.0.0.1... > fniles (fniles(a)pfmail.com) writes: >> Today, I did an UPDATE statement on SQL Server Management Studio on a few >> records. >> Now, from a VB.NET program when I tried to get those records that I >> updated, when I go thru the datareader, when it gets around those >> records that I updated, it timed out. Even though getting the same query >> thru SQL Server Management Studio is very fast. >> What can I do to fix it ? > > It seems that you at some point started a transaction in SSMS, and then > forgot to roll it back or commit it. > > Run COMMIT TRANSACTION in your query window until you get an error > message, > tell you that there is no active transaction. > > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |