Reading database timed out

  • Thread starter Thread starter fniles
  • Start date Start date
F

fniles

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
 
fniles said:
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 ?
Click to expand...

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, (e-mail address removed)

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
 
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.
Click to expand...
What do i do when I get an error ?
What do you mean by "tell you that there is no active transaction" ?
 
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 ?
 
I ran COMMIT TRANSACTION, no error.
After that it looks like all my problems were fixed.
Thanks !
 
fniles said:
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 ?
Click to expand...

The size of the table has nothing to do with it.

COMMIT TRANSACTION usually completes quickly. In any case, the question is
moot, because if you don't commit your changes, you will keep on blocking.
What do i do when I get an error ?
Click to expand...

Stop running COMMIT TRANSACTION.
What do you mean by "tell you that there is no active transaction" ?
Click to expand...

If you say BEGIN TRANSACTION you start a user-defined transaction. Meaning
that evrrything you do from that point until you commit, while either be
persisted in full or not at all. Transactions are a fundamental concept in
the database world.

If you issue a second BEGIN TRANSACTION, that does not start a new
transaction within the transaction, but it just increments a transaction
counter. This is useful when working with nested stored procedures. When
issue COMMIT TRANSACTION, this will only decrease the transaction counter,
and nothing else happens if the transaction counter still is > 0. Therefore
you need to run COMMIT TRANSCATION until you have matched all BEGIN
TRANSACTION. The simplest way to verify this is to run COMMIT TRANSACTION
until you get an error message telling you that there is no transaction to
commit.

ROLLBACK TRANSACTION, on the other hand, is different. ROLLBACK always takes
effect and immediately slashes the transaction counter to 0.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
Back
Top