SQL Adding a User account: timeout

Showing posts with label timeout. Show all posts

Saturday, February 25, 2012

ADODB Connection Open: Intermittent Timeout Expired Errors

Hello all-
Connecting to a SQL Server 2005 Express w/Advanced Services database using
ADODB in vbscript. The database is on the local server and am using shared
memory. The server has dual processors and 3GB Ram, but SQL Server Express i
s
limited to 1 processor and 1 GB of ram.
Every once in a great while I receive timeout expired errors while trying to
connect to the database. When it works, it connects in less than a
millisecond. Therefore I believe that increasing the timeout period in the
connection string rather than using its default of 30 seconds is not going t
o
fix this problem.
Const CONNECTION_STRING = "Provider=sqloledb;Data Source=INGEN;Initial
Catalog=Ingen;Integrated Security=SSPI;"
Dim dbConnection : Set dbConnection = CreateObject("ADODB.Connection")
Call dbConnection.Open(CONNECTION_STRING)
Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired
Any suggestions?
Thank you in advance.Go ahead and try the longer timeout. There may be other things happening on
your system that occasionally cause a hesitation. One of the more common
issues is that SQL Express databases have the autoclose option set by
default. This means that when no users are logged into a database, SQL
Server shuts it down to safe resources. The first user to login after the
database shuts down waits for the database to start up before the login is
complete so that may be what is causing the timeout. Generally starting up
a database takes only a few seconds but if the system is busy doing other
things it may take a while for SQL Server to obtain the resources it needs.
You can check this by using ALTER DATABASE to turn off the autoclose option
on your main databases.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:89E366F9-6946-4B45-86AD-CC7B9B0EB5BE@.microsoft.com...
> Hello all-
> Connecting to a SQL Server 2005 Express w/Advanced Services database using
> ADODB in vbscript. The database is on the local server and am using
> shared
> memory. The server has dual processors and 3GB Ram, but SQL Server Express
> is
> limited to 1 processor and 1 GB of ram.
> Every once in a great while I receive timeout expired errors while trying
> to
> connect to the database. When it works, it connects in less than a
> millisecond. Therefore I believe that increasing the timeout period in the
> connection string rather than using its default of 30 seconds is not going
> to
> fix this problem.
> Const CONNECTION_STRING = "Provider=sqloledb;Data Source=INGEN;Initial
> Catalog=Ingen;Integrated Security=SSPI;"
> Dim dbConnection : Set dbConnection = CreateObject("ADODB.Connection")
> Call dbConnection.Open(CONNECTION_STRING)
> Microsoft OLE DB Provider for SQL Server error '80040e31'
> Timeout expired
> Any suggestions?
> Thank you in advance.|||Thanks Roger for your response. We started getting this error after
upgrading from MSDE to the 2005 Express version. Is this auto-close
something new to 2005? I verified that I do have auto-close turned off for m
y
database properties.
What would you suggest increasing the timeout period to?|||Yes, also look in the SQL Server errorlog file to see if any errors are
logged when the timeout happens.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:D29FD261-9A6C-4083-8D1E-22C1914DB2DC@.microsoft.com...
> Thanks Roger for your response. We started getting this error after
> upgrading from MSDE to the 2005 Express version. Is this auto-close
> something new to 2005? I verified that I do have auto-close turned off for
> my
> database properties.
> What would you suggest increasing the timeout period to?|||Where do I find the SQL Server errorlog file?
I am on SQL Server Express so it will not let me view the logs in the SSMSE
interface. If you are talking about the logs in the Event Viewer, there was
nothing logged.
Thanks
"Roger Wolter[MSFT]" wrote:

> Yes, also look in the SQL Server errorlog file to see if any errors are
> logged when the timeout happens.|||Where do I find the SQL Server errorlog?
I am on SQL Server Express and cannot view the transaction logs from within
SSMSE. If you are talking about the system logs in the Event Viewer, there
was nothing logged in here during the time frame of the connection error.
"Roger Wolter[MSFT]" wrote:

> Yes, also look in the SQL Server errorlog file to see if any errors are
> logged when the timeout happens.|||C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
assuming you installed express first and to the default location.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:17F23E00-0514-43F1-9822-88F61816AD2D@.microsoft.com...[vbcol=seagreen]
> Where do I find the SQL Server errorlog?
> I am on SQL Server Express and cannot view the transaction logs from
> within
> SSMSE. If you are talking about the system logs in the Event Viewer,
> there
> was nothing logged in here during the time frame of the connection error.
> "Roger Wolter[MSFT]" wrote:
>|||Roger-
Increasing the timeout in the connection string appears to have fixed the
problem! No errors since making the change, so I think that's a good sign.
Thanks for your help.
"Roger Wolter[MSFT]" wrote:

> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> assuming you installed express first and to the default location.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
> news:17F23E00-0514-43F1-9822-88F61816AD2D@.microsoft.com...
>
>

ADODB Connection Open: Intermittent Timeout Expired Errors

Hello all-
Connecting to a SQL Server 2005 Express w/Advanced Services database using
ADODB in vbscript. The database is on the local server and am using shared
memory. The server has dual processors and 3GB Ram, but SQL Server Express is
limited to 1 processor and 1 GB of ram.
Every once in a great while I receive timeout expired errors while trying to
connect to the database. When it works, it connects in less than a
millisecond. Therefore I believe that increasing the timeout period in the
connection string rather than using its default of 30 seconds is not going to
fix this problem.
Const CONNECTION_STRING = "Provider=sqloledb;Data Source=INGEN;Initial
Catalog=Ingen;Integrated Security=SSPI;"
Dim dbConnection : Set dbConnection = CreateObject("ADODB.Connection")
Call dbConnection.Open(CONNECTION_STRING)
Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired
Any suggestions?
Thank you in advance.
Go ahead and try the longer timeout. There may be other things happening on
your system that occasionally cause a hesitation. One of the more common
issues is that SQL Express databases have the autoclose option set by
default. This means that when no users are logged into a database, SQL
Server shuts it down to safe resources. The first user to login after the
database shuts down waits for the database to start up before the login is
complete so that may be what is causing the timeout. Generally starting up
a database takes only a few seconds but if the system is busy doing other
things it may take a while for SQL Server to obtain the resources it needs.
You can check this by using ALTER DATABASE to turn off the autoclose option
on your main databases.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:89E366F9-6946-4B45-86AD-CC7B9B0EB5BE@.microsoft.com...
> Hello all-
> Connecting to a SQL Server 2005 Express w/Advanced Services database using
> ADODB in vbscript. The database is on the local server and am using
> shared
> memory. The server has dual processors and 3GB Ram, but SQL Server Express
> is
> limited to 1 processor and 1 GB of ram.
> Every once in a great while I receive timeout expired errors while trying
> to
> connect to the database. When it works, it connects in less than a
> millisecond. Therefore I believe that increasing the timeout period in the
> connection string rather than using its default of 30 seconds is not going
> to
> fix this problem.
> Const CONNECTION_STRING = "Provider=sqloledb;Data Source=INGEN;Initial
> Catalog=Ingen;Integrated Security=SSPI;"
> Dim dbConnection : Set dbConnection = CreateObject("ADODB.Connection")
> Call dbConnection.Open(CONNECTION_STRING)
> Microsoft OLE DB Provider for SQL Server error '80040e31'
> Timeout expired
> Any suggestions?
> Thank you in advance.
|||Thanks Roger for your response. We started getting this error after
upgrading from MSDE to the 2005 Express version. Is this auto-close
something new to 2005? I verified that I do have auto-close turned off for my
database properties.
What would you suggest increasing the timeout period to?
|||Yes, also look in the SQL Server errorlog file to see if any errors are
logged when the timeout happens.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:D29FD261-9A6C-4083-8D1E-22C1914DB2DC@.microsoft.com...
> Thanks Roger for your response. We started getting this error after
> upgrading from MSDE to the 2005 Express version. Is this auto-close
> something new to 2005? I verified that I do have auto-close turned off for
> my
> database properties.
> What would you suggest increasing the timeout period to?
|||Where do I find the SQL Server errorlog file?
I am on SQL Server Express so it will not let me view the logs in the SSMSE
interface. If you are talking about the logs in the Event Viewer, there was
nothing logged.
Thanks
"Roger Wolter[MSFT]" wrote:

> Yes, also look in the SQL Server errorlog file to see if any errors are
> logged when the timeout happens.
|||Where do I find the SQL Server errorlog?
I am on SQL Server Express and cannot view the transaction logs from within
SSMSE. If you are talking about the system logs in the Event Viewer, there
was nothing logged in here during the time frame of the connection error.
"Roger Wolter[MSFT]" wrote:

> Yes, also look in the SQL Server errorlog file to see if any errors are
> logged when the timeout happens.
|||C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
assuming you installed express first and to the default location.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:17F23E00-0514-43F1-9822-88F61816AD2D@.microsoft.com...[vbcol=seagreen]
> Where do I find the SQL Server errorlog?
> I am on SQL Server Express and cannot view the transaction logs from
> within
> SSMSE. If you are talking about the system logs in the Event Viewer,
> there
> was nothing logged in here during the time frame of the connection error.
> "Roger Wolter[MSFT]" wrote:
|||Roger-
Increasing the timeout in the connection string appears to have fixed the
problem! No errors since making the change, so I think that's a good sign.
Thanks for your help.
"Roger Wolter[MSFT]" wrote:

> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> assuming you installed express first and to the default location.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
> news:17F23E00-0514-43F1-9822-88F61816AD2D@.microsoft.com...
>
>

ADO.NET Timeout Exception - I have tried everything

Hello!
I am looking for someone who has solved this multi-million people's
problem. EVERYONE seems to ahve this problem.

Im a creating a data set and populating it with a call to a store proc.

Its a complex stored proc with the end result as an insert to a temp
table. Then I do a select from the temp table - in the store proc.

I get the following sqlException error on the following line:
DataAdapterName.Fill(DataSetName, "TableName")

The error is:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.

My connectiong string looks like this:
<add key="cnITDevWinUser" value="Data Source=server; Integrated
Security=SSPI; Initial Catalog=dbname; pooling=false;connection
reset=false;connection lifetime=5;min pool size=1;max pool
size=10;connection timeout=120" /
I have admin rights on that db.

I have set my command.timeout to 500.

If i run this same code in a windows application, it works fine.
If I use a DataReader with the same storeProc, it works fine.
If I run this same code on a simple selec (hello world), it also works
fine.

If I run this store proc in QueryAnalyzer it works fine and is done
within 6 seconds.
If I run this on a different machine it produces the same result.

I am using SQL2000 with vb.net in VS2003.

I have looked everywhere for the answer. I can't find it anywhere.
PLEASE SOMEONE HELP.

regards,
Stas K.(a.k.a Sorcerdon)(sorcerdon@.gmail.com) writes:
> Im a creating a data set and populating it with a call to a store proc.
> Its a complex stored proc with the end result as an insert to a temp
> table. Then I do a select from the temp table - in the store proc.
>
> I get the following sqlException error on the following line:
> DataAdapterName.Fill(DataSetName, "TableName")
>
> The error is:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.

How long time does take before you get the error? Since you say
that you've set the command timeout to 500, I expect it to take
three minutes, but I want to verify.

> If I use a DataReader with the same storeProc, it works fine.

Since DataAdapter.Fill more or less is just a wrapper on ExecuteReader,
this is funky. (Then again, that sounds like you have a workaround.)

I get the feeling that there is a blocking issue lurking here. When
you run DataAdapter.Fill and are waiting for it to timeout, run sp_who
from Query Analyzer and look for non-zero values in the Blk column.
In such case the spid in the Blk column blocks the spid on this row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Already done that. There are no locks.
I forgot to mention that in the original post.

regards,
Stas K (a.k.a Sorcerdon)|||Sorcerdon (sorcerdon@.gmail.com) writes:
> Already done that. There are no locks.
> I forgot to mention that in the original post.

I afraid then I don't have more suggestions with the information you have
given.

You could use Profiler to see if you get different query plans for
the different situations, althogh I don't see why that would happen.

Then again, there may be some pertinent information you have shared with
us.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The problem is solved but a new mystery begins.
The problem was one of the parameters being passed is wrong.
but the mystery is that sql didnt return an error - it just froze...
intresting.

ADO.NET Timeout Exception - I have tried everything

Hello!
I am looking for someone who has solved this multi-million people's
problem. EVERYONE seems to ahve this problem.
Im a creating a data set and populating it with a call to a store proc.
Its a complex stored proc with the end result as an insert to a temp
table. Then I do a select from the temp table - in the store proc.
I get the following sqlException error on the following line:
DataAdapterName.Fill(DataSetName, "TableName")
The error is:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
My connectiong string looks like this:
<add key="cnITDevWinUser" value="Data Source=server; Integrated
Security=SSPI; Initial Catalog=dbname; pooling=false;connection
reset=false;connection lifetime=5;min pool size=1;max pool
size=10;connection timeout=120" />
I have admin rights on that db.
I have set my command.timeout to 500.
If i run this same code in a windows application, it works fine.
If I use a DataReader with the same storeProc, it works fine.
If I run this same code on a simple selec (hello world), it also works
fine.
If I run this store proc in QueryAnalyzer it works fine and is done
within 6 seconds.
If I run this on a different machine it produces the same result.
I am using SQL2000 with vb.net in VS2003.
I have looked everywhere for the answer. I can't find it anywhere.
PLEASE SOMEONE HELP.
regards,
Stas K.(a.k.a Sorcerdon)Another thought: Go to see the "SQL Server Logs" or the "Event Viewer" on th
e
Server machine to see if there is error occurred on hardware. Sometimes a
certain bad sector on disk can cause SQL Server timeout.
Just my 2 cents.
James
"sorcerdon@.gmail.com" wrote:

> Hello!
> I am looking for someone who has solved this multi-million people's
> problem. EVERYONE seems to ahve this problem.
> Im a creating a data set and populating it with a call to a store proc.
> Its a complex stored proc with the end result as an insert to a temp
> table. Then I do a select from the temp table - in the store proc.
>
> I get the following sqlException error on the following line:
> DataAdapterName.Fill(DataSetName, "TableName")
>
> The error is:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
>
> My connectiong string looks like this:
> <add key="cnITDevWinUser" value="Data Source=server; Integrated
> Security=SSPI; Initial Catalog=dbname; pooling=false;connection
> reset=false;connection lifetime=5;min pool size=1;max pool
> size=10;connection timeout=120" />
>
> I have admin rights on that db.
>
> I have set my command.timeout to 500.
>
> If i run this same code in a windows application, it works fine.
> If I use a DataReader with the same storeProc, it works fine.
> If I run this same code on a simple selec (hello world), it also works
> fine.
>
> If I run this store proc in QueryAnalyzer it works fine and is done
> within 6 seconds.
> If I run this on a different machine it produces the same result.
>
> I am using SQL2000 with vb.net in VS2003.
>
> I have looked everywhere for the answer. I can't find it anywhere.
> PLEASE SOMEONE HELP.
>
> regards,
> Stas K.(a.k.a Sorcerdon)
>|||Hi:
Check the memory usage/settings on the server. I encountered timeout
errors when the server ran out of memory on a complicated query that
was run repeatedly. Check the Event Log for 'Out of Memory' errors
(code 123, I believe).
- David
sorcerdon@.gmail.com wrote:
> Hello!
> I am looking for someone who has solved this multi-million people's
> problem. EVERYONE seems to ahve this problem.
> Im a creating a data set and populating it with a call to a store proc.
> Its a complex stored proc with the end result as an insert to a temp
> table. Then I do a select from the temp table - in the store proc.
>
> I get the following sqlException error on the following line:
> DataAdapterName.Fill(DataSetName, "TableName")
>
> The error is:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
>
> My connectiong string looks like this:
> <add key="cnITDevWinUser" value="Data Source=server; Integrated
> Security=SSPI; Initial Catalog=dbname; pooling=false;connection
> reset=false;connection lifetime=5;min pool size=1;max pool
> size=10;connection timeout=120" />
>
> I have admin rights on that db.
>
> I have set my command.timeout to 500.
>
> If i run this same code in a windows application, it works fine.
> If I use a DataReader with the same storeProc, it works fine.
> If I run this same code on a simple selec (hello world), it also works
> fine.
>
> If I run this store proc in QueryAnalyzer it works fine and is done
> within 6 seconds.
> If I run this on a different machine it produces the same result.
>
> I am using SQL2000 with vb.net in VS2003.
>
> I have looked everywhere for the answer. I can't find it anywhere.
> PLEASE SOMEONE HELP.
>
> regards,
> Stas K.(a.k.a Sorcerdon)|||Hi:
Check the memory usage/settings on the server. I encountered timeout
errors when the server ran out of memory on a complicated query that
was run repeatedly. Check the Event Log for 'Out of Memory' errors
(code 123, I believe).
- David
sorcerdon@.gmail.com wrote:
> Hello!
> I am looking for someone who has solved this multi-million people's
> problem. EVERYONE seems to ahve this problem.
> Im a creating a data set and populating it with a call to a store proc.
> Its a complex stored proc with the end result as an insert to a temp
> table. Then I do a select from the temp table - in the store proc.
>
> I get the following sqlException error on the following line:
> DataAdapterName.Fill(DataSetName, "TableName")
>
> The error is:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
>
> My connectiong string looks like this:
> <add key="cnITDevWinUser" value="Data Source=server; Integrated
> Security=SSPI; Initial Catalog=dbname; pooling=false;connection
> reset=false;connection lifetime=5;min pool size=1;max pool
> size=10;connection timeout=120" />
>
> I have admin rights on that db.
>
> I have set my command.timeout to 500.
>
> If i run this same code in a windows application, it works fine.
> If I use a DataReader with the same storeProc, it works fine.
> If I run this same code on a simple selec (hello world), it also works
> fine.
>
> If I run this store proc in QueryAnalyzer it works fine and is done
> within 6 seconds.
> If I run this on a different machine it produces the same result.
>
> I am using SQL2000 with vb.net in VS2003.
>
> I have looked everywhere for the answer. I can't find it anywhere.
> PLEASE SOMEONE HELP.
>
> regards,
> Stas K.(a.k.a Sorcerdon)

Friday, February 24, 2012

ADO.Net connection timeout and transaction log

Hi newsgroup.
Can someone help me figure out what happens to the transaction log
file when you run an ADO.Net transaction that times out? I have
configure the database recovery model to SIMPLE so normally the
transaction log file should never grow large, yet in some occasions, I
found it growing to several Gigs.
If the connection times out in the C# code and is eventually closed,
is Sql Server notified of it and does it roll back the transaction
right away or does it keep the transaction waiting around, thus
filling up the transaction log? What is the default time out before
Sql Server notices that the client got disconnected and that the
transaction must be rolled back?
I have the following code running. Is it possible that it would make
the transaction log grow when exceptions occur
// m_Connection is a SqlConnection object.
DbTransaction trans = m_Connection.BeginTransaction();
try
{
// Some transaction uploading large files and
// updating several records in several tables
trans.Commit();
}
catch( SqlException sqlEx )
{
trans.Rollback();
if( sqlEx.Number == SqlTimeoutError )
{
throw new NeedRetryException( "SqlTimeoutException
is occured.", sqlEx );
}
else
{
throw;
}
}
catch
{
trans.Rollback();
throw;
}
finally
{
trans.Dispose();
m_Connection.Close();
}
Any help or pointer greatly appreciated.
Tony.> I have configure the database recovery model to SIMPLE so normally the
> transaction log file should never grow large, yet in some occasions, I
> found it growing to several Gigs.
The SIMPLE mode doesn't mean you won't get a large tran log. It's the size
of a transaction that matters. In other words, you can have your database in
the simple mode, but if you run a large single transaction, you can still
blow up your log file.
Linchi
"tony.newsgrps@.gmail.com" wrote:
> Hi newsgroup.
> Can someone help me figure out what happens to the transaction log
> file when you run an ADO.Net transaction that times out? I have
> configure the database recovery model to SIMPLE so normally the
> transaction log file should never grow large, yet in some occasions, I
> found it growing to several Gigs.
> If the connection times out in the C# code and is eventually closed,
> is Sql Server notified of it and does it roll back the transaction
> right away or does it keep the transaction waiting around, thus
> filling up the transaction log? What is the default time out before
> Sql Server notices that the client got disconnected and that the
> transaction must be rolled back?
> I have the following code running. Is it possible that it would make
> the transaction log grow when exceptions occur
> // m_Connection is a SqlConnection object.
> DbTransaction trans = m_Connection.BeginTransaction();
> try
> {
> // Some transaction uploading large files and
> // updating several records in several tables
> trans.Commit();
> }
> catch( SqlException sqlEx )
> {
> trans.Rollback();
> if( sqlEx.Number == SqlTimeoutError )
> {
> throw new NeedRetryException( "SqlTimeoutException
> is occured.", sqlEx );
> }
> else
> {
> throw;
> }
> }
> catch
> {
> trans.Rollback();
> throw;
> }
> finally
> {
> trans.Dispose();
> m_Connection.Close();
> }
> Any help or pointer greatly appreciated.
> Tony.
>|||Hi Linchi,
Thank you for the answer. The transactions I'm running may be large at
times (i.e. inserting perhaps 50MB of data), but the transaction log
grows larger than 1GB. That's a 20 time larger so to me it does not
sound like 1GB is a normal size for the transaction log. I can expect
100MB to be, but 1GB seems way too high.
What else could be the reason for that log file growing?
Thank you,
Tony.
On Dec 11, 10:44 pm, Linchi Shea
<LinchiS...@.discussions.microsoft.com> wrote:
> > I have configure the database recovery model to SIMPLE so normally the
> > transaction log file should never grow large, yet in some occasions, I
> > found it growing to several Gigs.
> The SIMPLE mode doesn't mean you won't get a large tran log. It's the size
> of a transaction that matters. In other words, you can have your database in
> the simple mode, but if you run a large single transaction, you can still
> blow up your log file.
> Linchi
> "tony.newsg...@.gmail.com" wrote:
> > Hi newsgroup.
> > Can someone help me figure out what happens to the transaction log
> > file when you run an ADO.Net transaction that times out? I have
> > configure the database recovery model to SIMPLE so normally the
> > transaction log file should never grow large, yet in some occasions, I
> > found it growing to several Gigs.
> > If the connection times out in the C# code and is eventually closed,
> > is Sql Server notified of it and does it roll back the transaction
> > right away or does it keep the transaction waiting around, thus
> > filling up the transaction log? What is the default time out before
> > Sql Server notices that the client got disconnected and that the
> > transaction must be rolled back?
> > I have the following code running. Is it possible that it would make
> > the transaction log grow when exceptions occur
> > // m_Connection is a SqlConnection object.
> > DbTransaction trans = m_Connection.BeginTransaction();
> > try
> > {
> > // Some transaction uploading large files and
> > // updating several records in several tables
> > trans.Commit();
> > }
> > catch( SqlException sqlEx )
> > {
> > trans.Rollback();
> > if( sqlEx.Number == SqlTimeoutError )
> > {
> > throw new NeedRetryException( "SqlTimeoutException
> > is occured.", sqlEx );
> > }
> > else
> > {
> > throw;
> > }
> > }
> > catch
> > {
> > trans.Rollback();
> > throw;
> > }
> > finally
> > {
> > trans.Dispose();
> > m_Connection.Close();
> > }
> > Any help or pointer greatly appreciated.
> > Tony.|||This is a multi-part message in MIME format.
--060303080103060806080308
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
The log file will only grow when it is full, you are modifying more data
in the database and autogrow is turned on for the transaction log.
(Yes, this is regardless of whether you are using the SIMPLE recovery
model or not. The SIMPLE recovery model just means the tlog is
truncated from time to time based on a number of factors.) What are
your autogrow settings on that logical file?
--
Mike
http://sqlnerd.blogspot.com
tony.newsgrps@.gmail.com wrote:
> Hi Linchi,
> Thank you for the answer. The transactions I'm running may be large at
> times (i.e. inserting perhaps 50MB of data), but the transaction log
> grows larger than 1GB. That's a 20 time larger so to me it does not
> sound like 1GB is a normal size for the transaction log. I can expect
> 100MB to be, but 1GB seems way too high.
> What else could be the reason for that log file growing?
> Thank you,
> Tony.
> On Dec 11, 10:44 pm, Linchi Shea
> <LinchiS...@.discussions.microsoft.com> wrote:
>> I have configure the database recovery model to SIMPLE so normally the
>> transaction log file should never grow large, yet in some occasions, I
>> found it growing to several Gigs.
>> The SIMPLE mode doesn't mean you won't get a large tran log. It's the size
>> of a transaction that matters. In other words, you can have your database in
>> the simple mode, but if you run a large single transaction, you can still
>> blow up your log file.
>> Linchi
>> "tony.newsg...@.gmail.com" wrote:
>> Hi newsgroup.
>> Can someone help me figure out what happens to the transaction log
>> file when you run an ADO.Net transaction that times out? I have
>> configure the database recovery model to SIMPLE so normally the
>> transaction log file should never grow large, yet in some occasions, I
>> found it growing to several Gigs.
>> If the connection times out in the C# code and is eventually closed,
>> is Sql Server notified of it and does it roll back the transaction
>> right away or does it keep the transaction waiting around, thus
>> filling up the transaction log? What is the default time out before
>> Sql Server notices that the client got disconnected and that the
>> transaction must be rolled back?
>> I have the following code running. Is it possible that it would make
>> the transaction log grow when exceptions occur
>> // m_Connection is a SqlConnection object.
>> DbTransaction trans = m_Connection.BeginTransaction();
>> try
>> {
>> // Some transaction uploading large files and
>> // updating several records in several tables
>> trans.Commit();
>> }
>> catch( SqlException sqlEx )
>> {
>> trans.Rollback();
>> if( sqlEx.Number == SqlTimeoutError )
>> {
>> throw new NeedRetryException( "SqlTimeoutException
>> is occured.", sqlEx );
>> }
>> else
>> {
>> throw;
>> }
>> }
>> catch
>> {
>> trans.Rollback();
>> throw;
>> }
>> finally
>> {
>> trans.Dispose();
>> m_Connection.Close();
>> }
>> Any help or pointer greatly appreciated.
>> Tony.
>
--060303080103060806080308
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>The log file will only grow when it is full, you are modifying more
data in the database and autogrow is turned on for the transaction
log. (Yes, this is regardless of whether you are using the SIMPLE
recovery model or not. The SIMPLE recovery model just means the tlog
is truncated from time to time based on a number of factors.) What are
your autogrow settings on that logical file?</tt> 
<pre class="moz-signature" cols="72">--
Mike
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></pre>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></pre>
 
 
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:tony.newsgrps@.gmail.com">tony.newsgrps@.gmail.com</a> wrote:
<blockquote
cite="mid:fe4cb261-5e49-4db9-9b19-5c67612ed748@.w40g2000hsb.googlegroups.com"
type="cite">
<pre wrap="">Hi Linchi,
Thank you for the answer. The transactions I'm running may be large at
times (i.e. inserting perhaps 50MB of data), but the transaction log
grows larger than 1GB. That's a 20 time larger so to me it does not
sound like 1GB is a normal size for the transaction log. I can expect
100MB to be, but 1GB seems way too high.
What else could be the reason for that log file growing?
Thank you,
Tony.
On Dec 11, 10:44 pm, Linchi Shea
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:LinchiS...@.discussions.microsoft.com"><LinchiS...@.discussions.microsoft.com></a> wrote:
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">I have configure the database recovery model to SIMPLE so normally the
transaction log file should never grow large, yet in some occasions, I
found it growing to several Gigs.
</pre>
</blockquote>
<pre wrap="">The SIMPLE mode doesn't mean you won't get a large tran log. It's the size
of a transaction that matters. In other words, you can have your database in
the simple mode, but if you run a large single transaction, you can still
blow up your log file.
Linchi
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:tony.newsg...@.gmail.com">"tony.newsg...@.gmail.com"</a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi newsgroup.
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">Can someone help me figure out what happens to the transaction log
file when you run an ADO.Net transaction that times out? I have
configure the database recovery model to SIMPLE so normally the
transaction log file should never grow large, yet in some occasions, I
found it growing to several Gigs.
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">If the connection times out in the C# code and is eventually closed,
is Sql Server notified of it and does it roll back the transaction
right away or does it keep the transaction waiting around, thus
filling up the transaction log? What is the default time out before
Sql Server notices that the client got disconnected and that the
transaction must be rolled back?
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">I have the following code running. Is it possible that it would make
the transaction log grow when exceptions occur
// m_Connection is a SqlConnection object.
DbTransaction trans = m_Connection.BeginTransaction();
try
{
// Some transaction uploading large files and
// updating several records in several tables
trans.Commit();
}
catch( SqlException sqlEx )
{
trans.Rollback();
if( sqlEx.Number == SqlTimeoutError )
{
throw new NeedRetryException( "SqlTimeoutException
is occured.", sqlEx );
}
else
{
throw;
}
}
catch
{
trans.Rollback();
throw;
}
finally
{
trans.Dispose();
m_Connection.Close();
}
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">Any help or pointer greatly appreciated.
Tony.
</pre>
</blockquote>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</body>
</html>
--090507060108060606060307--

ADO.Net connection timeout and transaction log

ADO Timeout & SP

Hi,
Can someone please tell me the expected behavior of a stored procedure
when called via an ADO command object.
My question is, if ADO hits a timeout, will the SP (updates a table)
rollback what it was doing?
Thanks in advance,
Dan.It will either rollback or commit, it won't leave the transaction
hanging, if that's what you're asking. Usually when ADO times out it's
when it's trying to connect, at which time none of your stored
procedures will be in call. If you want to examine what's going on on
the server, open a Profiler trace and step through your code a line at
a time.
-- Mary
MCW Technologies
http://www.mcwtech.com
On 21 Jan 2004 03:43:53 -0800, danielcoates@.yahoo.com (Dan Coates)
wrote:

quote:

>Hi,
>Can someone please tell me the expected behavior of a stored procedure
>when called via an ADO command object.
>My question is, if ADO hits a timeout, will the SP (updates a table)
>rollback what it was doing?
>Thanks in advance,
>Dan.

Saturday, February 25, 2012

ADODB Connection Open: Intermittent Timeout Expired Errors

ADODB Connection Open: Intermittent Timeout Expired Errors

ADO.NET Timeout Exception - I have tried everything

ADO.NET Timeout Exception - I have tried everything

ADO.NET Timeout Exception - I have tried everything

Friday, February 24, 2012

ADO.Net connection timeout and transaction log

ADO.Net connection timeout and transaction log

ADO.Net connection timeout and transaction log

ADO Timeout & SP

ADO Timeout & SP

SQL Adding a User account

Blog Archive

About Me