I've been receiving consistency errors for the past few weeks
whenever the indexes on 1 table are rebuilt (which they are each
evening). I restored the production database to our development
server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
were the same (which would rule out hardware errors, right?)
DBREINDEX results:
A possible database consistency problem has been detected on database
'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
database 'database_name'.
Connection Broken
Then I ran CHECKDB with repair_allow_data_loss (results below) and that
fixed the consistency errors (CHECKDB came back with no errors the
next time).
I read online (not BOL) that only index, header and consistency
information are removed with repair_all_data_loss, no actual data.
>From the info below, can you tell if the only thing that changed was
the clustered index? And if so, would it be safe (i.e., no data loss)
to run repair_allow_data_loss on the production database? I'd like
to clean this error up without resorting to a month old backup, which
would definitely mean data loss.
Thanks!!
CHECKDB with repair_allow_data_loss results:
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
ALL_ERRORMSGS
DBCC results for 'dbowner.f8'.
Server: Msg 8944, Level 16, State 4, Line 1
Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
seen in the scan although its parent (1:155073) and previous (1:155092)
refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
missing a reference from previous page (1:155093). Possible chain
linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 10, text ID 41680896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 11, text ID 1340932096 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 12, text ID 23986176 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 0, text ID 96010240 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 1, text ID 817430528 is not referenced.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:155093) has been deallocated from object ID 1648724926,
index ID 0.
Clustered index successfully restored for object 'dbowner.f8' in
database 'database_name'.
There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
CHECKDB fixed 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Select count (*) from dbowner.f8 on development server shows: 1140058
Select count (*) from dbowner.f8 on production server shows: 1145787
> I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
I doubt it will rule out hw errors. I can imagine that hw errors were introduced on the prod
machine, corrupted the db and is carried over by your backup on the test machine.
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
Que! Can you post that link? AFAIK, The option does what it say. If will remove all corrupted pages,
resulting in possible data loss. In your case, you seem to have problems in index 0 (a data page)
for a user object (just reading the first error you posted). That page will be removed = data loss.
I strongly encourage you to open a case with MS Support, assuming that you value the database > the
few hundred bucks a support issue costs. They might not be able to help you further, but you have
tried. And make sure you do far more frequent CHECKDBs. Also, you might want to check
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127328673.857606.24480@.f14g2000cwb.googlegro ups.com...
> I've been receiving consistency errors for the past few weeks
> whenever the indexes on 1 table are rebuilt (which they are each
> evening). I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
> DBREINDEX results:
> A possible database consistency problem has been detected on database
> 'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
> database 'database_name'.
> Connection Broken
>
> Then I ran CHECKDB with repair_allow_data_loss (results below) and that
> fixed the consistency errors (CHECKDB came back with no errors the
> next time).
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
> the clustered index? And if so, would it be safe (i.e., no data loss)
> to run repair_allow_data_loss on the production database? I'd like
> to clean this error up without resorting to a month old backup, which
> would definitely mean data loss.
> Thanks!!
> CHECKDB with repair_allow_data_loss results:
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
> ALL_ERRORMSGS
> DBCC results for 'dbowner.f8'.
> Server: Msg 8944, Level 16, State 4, Line 1
> Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
> Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
> seen in the scan although its parent (1:155073) and previous (1:155092)
> refer to it. Check any previous errors.
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
> missing a reference from previous page (1:155093). Possible chain
> linkage problem.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 10, text ID 41680896 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 11, text ID 1340932096 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 12, text ID 23986176 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 0, text ID 96010240 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 1, text ID 817430528 is not referenced.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> Repair: Page (1:155093) has been deallocated from object ID 1648724926,
> index ID 0.
> Clustered index successfully restored for object 'dbowner.f8' in
> database 'database_name'.
> There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> CHECKDB fixed 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Select count (*) from dbowner.f8 on development server shows: 1140058
> Select count (*) from dbowner.f8 on production server shows: 1145787
>
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Tuesday, March 27, 2012
Tuesday, March 20, 2012
Connections not closing?
Hi, hoping someone can offer some explanations.
I've rigorously checked my java code and confirmed that every place I open a connection is done in a try {} block and the connection is closed in the follwoing finally {} block. There is one exception to this. I'm using the log4j JDBCAppender which open
s a single connection to the database and keeps that connection open.
Looking at the performance monitor, I can see that I have many, many open connections. Looking at SQLServer, though, I see that these are all sleeping.
Any thoughts on this? I did notice this page http://support.microsoft.com/default...;en-us;313220. Could that be what I'm seeing? If so, will additional writes over that connection re-use the implicitly opened connections?
Thanks for any help.
I'd have to see the code, but it hints at
a multithreading issue. I'll bet it goes away
if you *define* the connection as a method-scoped
variable, in the method(s) where it's used, just
before the try block that sets it. That way,
two simultaneous threads won't set the same variable
with two newly-obtained connections, with the last one
overwriting the first, both using and closing the second,
and the first hangs till GC'ed and maybe never closed...
Joe Weinstein at BEA
Chris Baird wrote:
> Hi, hoping someone can offer some explanations.
> I've rigorously checked my java code and confirmed that every place I open a connection is done in a try {} block and the connection is closed in the follwoing finally {} block. There is one exception to this. I'm using the log4j JDBCAppender which op
ens a single connection to the database and keeps that connection open.
> Looking at the performance monitor, I can see that I have many, many open connections. Looking at SQLServer, though, I see that these are all sleeping.
> Any thoughts on this? I did notice this page http://support.microsoft.com/default...;en-us;313220. Could that be what I'm seeing? If so, will additional writes over that connection re-use the implicitly opened connections?
> Thanks for any help.
|||Thanks, Joe.
Actually, my only connection definitions are method scoped. The only one that does not fit this is for the log4j JDBCAppender; however, only one instance of that class is created. All of my methods that use connections look like:
Connection connection = null;
try {
connection = getConnection();
// do stuff
} finally {
closeConnection(connection);
}
Where getConnection() does the normal DriverManager call and closeConnection() just ensures that the passed connection object is not null before trying to close it.
|||Ok, then
Chris Baird wrote:
> Thanks, Joe.
> Actually, my only connection definitions are method scoped. The only one that does not fit this is for the log4j JDBCAppender; however, only one instance of that class is created. All of my methods that use connections look like:
> Connection connection = null;
> try {
> connection = getConnection();
> // do stuff
> } finally {
> closeConnection(connection);
> }
> Where getConnection() does the normal DriverManager call and closeConnection() just ensures that the passed connection object is not null before trying to close it.
Ok. The code looks good then. Here's what I would do: Whenever I got a connection,
I would record or print out the spid, and whenever I closed a connection I would
again print out it's spid before closing. In fact I might query *all* the spids
before closing one, and after (via another connection). The only suspect might
be the driver itself pooling connections. Ah, but note that this driver, unless you
set selectMethod=cursor, will create cloned connections under the covers to
implement multiple concurrent statements. If you're sure to use only one statement
at a time, and close it before opening another, it might go away.
Joe Weinstein at BEA
sqlsql
I've rigorously checked my java code and confirmed that every place I open a connection is done in a try {} block and the connection is closed in the follwoing finally {} block. There is one exception to this. I'm using the log4j JDBCAppender which open
s a single connection to the database and keeps that connection open.
Looking at the performance monitor, I can see that I have many, many open connections. Looking at SQLServer, though, I see that these are all sleeping.
Any thoughts on this? I did notice this page http://support.microsoft.com/default...;en-us;313220. Could that be what I'm seeing? If so, will additional writes over that connection re-use the implicitly opened connections?
Thanks for any help.
I'd have to see the code, but it hints at
a multithreading issue. I'll bet it goes away
if you *define* the connection as a method-scoped
variable, in the method(s) where it's used, just
before the try block that sets it. That way,
two simultaneous threads won't set the same variable
with two newly-obtained connections, with the last one
overwriting the first, both using and closing the second,
and the first hangs till GC'ed and maybe never closed...
Joe Weinstein at BEA
Chris Baird wrote:
> Hi, hoping someone can offer some explanations.
> I've rigorously checked my java code and confirmed that every place I open a connection is done in a try {} block and the connection is closed in the follwoing finally {} block. There is one exception to this. I'm using the log4j JDBCAppender which op
ens a single connection to the database and keeps that connection open.
> Looking at the performance monitor, I can see that I have many, many open connections. Looking at SQLServer, though, I see that these are all sleeping.
> Any thoughts on this? I did notice this page http://support.microsoft.com/default...;en-us;313220. Could that be what I'm seeing? If so, will additional writes over that connection re-use the implicitly opened connections?
> Thanks for any help.
|||Thanks, Joe.
Actually, my only connection definitions are method scoped. The only one that does not fit this is for the log4j JDBCAppender; however, only one instance of that class is created. All of my methods that use connections look like:
Connection connection = null;
try {
connection = getConnection();
// do stuff
} finally {
closeConnection(connection);
}
Where getConnection() does the normal DriverManager call and closeConnection() just ensures that the passed connection object is not null before trying to close it.
|||Ok, then
Chris Baird wrote:
> Thanks, Joe.
> Actually, my only connection definitions are method scoped. The only one that does not fit this is for the log4j JDBCAppender; however, only one instance of that class is created. All of my methods that use connections look like:
> Connection connection = null;
> try {
> connection = getConnection();
> // do stuff
> } finally {
> closeConnection(connection);
> }
> Where getConnection() does the normal DriverManager call and closeConnection() just ensures that the passed connection object is not null before trying to close it.
Ok. The code looks good then. Here's what I would do: Whenever I got a connection,
I would record or print out the spid, and whenever I closed a connection I would
again print out it's spid before closing. In fact I might query *all* the spids
before closing one, and after (via another connection). The only suspect might
be the driver itself pooling connections. Ah, but note that this driver, unless you
set selectMethod=cursor, will create cloned connections under the covers to
implement multiple concurrent statements. If you're sure to use only one statement
at a time, and close it before opening another, it might go away.
Joe Weinstein at BEA
sqlsql
Monday, March 19, 2012
Connections
I've got a third party download app that creates ten connections to Sql
Server 2000 per user! We've got an average of 40 users/day and so we have
around 400 connections floating around all day just for this one small app!
We asked the vendor and all they will say is "it's working as designed".
So here are my questions:
1. Is there ANY reason on planet earth, performance or otherwise, that
someone would require ten connections?!?
2. How can I get it across that this is unacceptable? How can I calculate
how many connections Sql Server can handle? Are there any rules of thumb
that I can use for leverage?
Thx.
CLM wrote:
> I've got a third party download app that creates ten connections to
> Sql Server 2000 per user! We've got an average of 40 users/day and
> so we have around 400 connections floating around all day just for
> this one small app! We asked the vendor and all they will say is
> "it's working as designed".
> So here are my questions:
> 1. Is there ANY reason on planet earth, performance or otherwise,
> that someone would require ten connections?!?
Possibly, but not likely.
> 2. How can I get it across that this is unacceptable? How can I
> calculate how many connections Sql Server can handle? Are there any
> rules of thumb that I can use for leverage?
>
Maybe you can turn on connection pooling in control panel if the app is
using ODBC connections to SQL Server. However, you may need to enable
connection pooling in the application using SQLSetEnvAttr and you
probably can't do this. ADO.Net provides connection pooling
automatically.
Maybe you can find out from the vendor if they support connection
pooling. Then send or email someone high up at the company (like the
CTO) your concerns about the product and the way it uses connections
very liberally.
Each connection consumes 12 KB + (3 * Network Packet Size). So, even
with 400 connections using a 4K packet size, you're only consuming about
10MB of memory.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes, but I've been reading that if Sql Server goes over 255 threads, Sql
Server will start to go over 2 connections per thread and resources will get
shared.
Plus, in addition to what you wrote, isn't Sql Server using some cpu
resources to manage these 400 inactive connections?
"David Gugick" wrote:
> CLM wrote:
> Possibly, but not likely.
>
> Maybe you can turn on connection pooling in control panel if the app is
> using ODBC connections to SQL Server. However, you may need to enable
> connection pooling in the application using SQLSetEnvAttr and you
> probably can't do this. ADO.Net provides connection pooling
> automatically.
> Maybe you can find out from the vendor if they support connection
> pooling. Then send or email someone high up at the company (like the
> CTO) your concerns about the product and the way it uses connections
> very liberally.
> Each connection consumes 12 KB + (3 * Network Packet Size). So, even
> with 400 connections using a 4K packet size, you're only consuming about
> 10MB of memory.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||CLM wrote:
> Yes, but I've been reading that if Sql Server goes over 255 threads,
> Sql Server will start to go over 2 connections per thread and
> resources will get shared.
If they're mostly idle, then you probably won't reach the default of 255
worker threads. Even if you do (it would require about 6+ active batches
run on each of your 40 clients), SQL Server will temporarily have
batches wait until a worker thread is available for use. From BOL:
"Having all worker threads allocated does not mean that the performance
of SQL Server will degrade. Typically, a new batch has only a short wait
for a free thread. Allocating more threads may degrade performance
because of the increased work required to coordinate resources among the
threads. Many SQL Server systems running in production reach this state
and run with very high performance levels."
> Plus, in addition to what you wrote, isn't Sql Server using some cpu
> resources to manage these 400 inactive connections?
Not much if they are inactive.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:7A9AD5AD-E23C-469F-B8C6-0124D2D2788C@.microsoft.com...
> Yes, but I've been reading that if Sql Server goes over 255 threads, Sql
> Server will start to go over 2 connections per thread and resources will
> get
> shared.
That's not true. Or rather, it's not relavent. A connection is assigned
not to a thread, but to a scheduler. Each scheduler has a pool of workers
(threads) to carry out work. So there is not a 1-1 relationship between the
number of connections and the number of threads on the server.
> Plus, in addition to what you wrote, isn't Sql Server using some cpu
> resources to manage these 400 inactive connections?
No.
David
Server 2000 per user! We've got an average of 40 users/day and so we have
around 400 connections floating around all day just for this one small app!
We asked the vendor and all they will say is "it's working as designed".
So here are my questions:
1. Is there ANY reason on planet earth, performance or otherwise, that
someone would require ten connections?!?
2. How can I get it across that this is unacceptable? How can I calculate
how many connections Sql Server can handle? Are there any rules of thumb
that I can use for leverage?
Thx.
CLM wrote:
> I've got a third party download app that creates ten connections to
> Sql Server 2000 per user! We've got an average of 40 users/day and
> so we have around 400 connections floating around all day just for
> this one small app! We asked the vendor and all they will say is
> "it's working as designed".
> So here are my questions:
> 1. Is there ANY reason on planet earth, performance or otherwise,
> that someone would require ten connections?!?
Possibly, but not likely.
> 2. How can I get it across that this is unacceptable? How can I
> calculate how many connections Sql Server can handle? Are there any
> rules of thumb that I can use for leverage?
>
Maybe you can turn on connection pooling in control panel if the app is
using ODBC connections to SQL Server. However, you may need to enable
connection pooling in the application using SQLSetEnvAttr and you
probably can't do this. ADO.Net provides connection pooling
automatically.
Maybe you can find out from the vendor if they support connection
pooling. Then send or email someone high up at the company (like the
CTO) your concerns about the product and the way it uses connections
very liberally.
Each connection consumes 12 KB + (3 * Network Packet Size). So, even
with 400 connections using a 4K packet size, you're only consuming about
10MB of memory.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes, but I've been reading that if Sql Server goes over 255 threads, Sql
Server will start to go over 2 connections per thread and resources will get
shared.
Plus, in addition to what you wrote, isn't Sql Server using some cpu
resources to manage these 400 inactive connections?
"David Gugick" wrote:
> CLM wrote:
> Possibly, but not likely.
>
> Maybe you can turn on connection pooling in control panel if the app is
> using ODBC connections to SQL Server. However, you may need to enable
> connection pooling in the application using SQLSetEnvAttr and you
> probably can't do this. ADO.Net provides connection pooling
> automatically.
> Maybe you can find out from the vendor if they support connection
> pooling. Then send or email someone high up at the company (like the
> CTO) your concerns about the product and the way it uses connections
> very liberally.
> Each connection consumes 12 KB + (3 * Network Packet Size). So, even
> with 400 connections using a 4K packet size, you're only consuming about
> 10MB of memory.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||CLM wrote:
> Yes, but I've been reading that if Sql Server goes over 255 threads,
> Sql Server will start to go over 2 connections per thread and
> resources will get shared.
If they're mostly idle, then you probably won't reach the default of 255
worker threads. Even if you do (it would require about 6+ active batches
run on each of your 40 clients), SQL Server will temporarily have
batches wait until a worker thread is available for use. From BOL:
"Having all worker threads allocated does not mean that the performance
of SQL Server will degrade. Typically, a new batch has only a short wait
for a free thread. Allocating more threads may degrade performance
because of the increased work required to coordinate resources among the
threads. Many SQL Server systems running in production reach this state
and run with very high performance levels."
> Plus, in addition to what you wrote, isn't Sql Server using some cpu
> resources to manage these 400 inactive connections?
Not much if they are inactive.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:7A9AD5AD-E23C-469F-B8C6-0124D2D2788C@.microsoft.com...
> Yes, but I've been reading that if Sql Server goes over 255 threads, Sql
> Server will start to go over 2 connections per thread and resources will
> get
> shared.
That's not true. Or rather, it's not relavent. A connection is assigned
not to a thread, but to a scheduler. Each scheduler has a pool of workers
(threads) to carry out work. So there is not a 1-1 relationship between the
number of connections and the number of threads on the server.
> Plus, in addition to what you wrote, isn't Sql Server using some cpu
> resources to manage these 400 inactive connections?
No.
David
Connections
I've got a satellite group that acts as their own dba's on
a 7.0 server. They claim that they cannot kill certain
connections, i.e. they typed in kill and nothing
happened. I have seen that happen with web-based
connection pooling apps but not with a regular app that
they're working with.
Anyway, what I wanted to ask is: is the kill command the
only way to kill a connection besides the winner-take-all
methods of taking the database offline or stopping and
restarting services? Just wondering if there's some magic
bullet that I hadn't heard of.
Kill can only be used to terminate user processes (typically spid>50) that
is not executing an extended procedure. Perhaps, that's what they're seeing.
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> I've got a satellite group that acts as their own dba's on
> a 7.0 server. They claim that they cannot kill certain
> connections, i.e. they typed in kill and nothing
> happened. I have seen that happen with web-based
> connection pooling apps but not with a regular app that
> they're working with.
> Anyway, what I wanted to ask is: is the kill command the
> only way to kill a connection besides the winner-take-all
> methods of taking the database offline or stopping and
> restarting services? Just wondering if there's some magic
> bullet that I hadn't heard of.
|||Thx but from what they're saying, it was definitely a user
process.
>--Original Message--
>Kill can only be used to terminate user processes
(typically spid>50) that
>is not executing an extended procedure. Perhaps, that's
what they're seeing.
>
>"NTel" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
on[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
magic
>
>.
>
|||Sometimes you get "ghost connections". There are some reasons this can happen, the connection
executing an extended stored procedure is one such possibility. There can be other things as well,
but this has been getting better with versions and service packs.
Note that the connection might be in the middle of a large rollback, and it will not die until the
rollback is done.
No magic bullets here. Wait for a possible rollback to complete, if you feel certain you have waited
long enough, recycling the SQL Server is the way to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3c8901c48fae$fcf54370$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx but from what they're saying, it was definitely a user
> process.
> (typically spid>50) that
> what they're seeing.
> message
> on
> the
> all
> magic
a 7.0 server. They claim that they cannot kill certain
connections, i.e. they typed in kill and nothing
happened. I have seen that happen with web-based
connection pooling apps but not with a regular app that
they're working with.
Anyway, what I wanted to ask is: is the kill command the
only way to kill a connection besides the winner-take-all
methods of taking the database offline or stopping and
restarting services? Just wondering if there's some magic
bullet that I hadn't heard of.
Kill can only be used to terminate user processes (typically spid>50) that
is not executing an extended procedure. Perhaps, that's what they're seeing.
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> I've got a satellite group that acts as their own dba's on
> a 7.0 server. They claim that they cannot kill certain
> connections, i.e. they typed in kill and nothing
> happened. I have seen that happen with web-based
> connection pooling apps but not with a regular app that
> they're working with.
> Anyway, what I wanted to ask is: is the kill command the
> only way to kill a connection besides the winner-take-all
> methods of taking the database offline or stopping and
> restarting services? Just wondering if there's some magic
> bullet that I hadn't heard of.
|||Thx but from what they're saying, it was definitely a user
process.
>--Original Message--
>Kill can only be used to terminate user processes
(typically spid>50) that
>is not executing an extended procedure. Perhaps, that's
what they're seeing.
>
>"NTel" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
on[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
magic
>
>.
>
|||Sometimes you get "ghost connections". There are some reasons this can happen, the connection
executing an extended stored procedure is one such possibility. There can be other things as well,
but this has been getting better with versions and service packs.
Note that the connection might be in the middle of a large rollback, and it will not die until the
rollback is done.
No magic bullets here. Wait for a possible rollback to complete, if you feel certain you have waited
long enough, recycling the SQL Server is the way to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3c8901c48fae$fcf54370$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx but from what they're saying, it was definitely a user
> process.
> (typically spid>50) that
> what they're seeing.
> message
> on
> the
> all
> magic
Wednesday, March 7, 2012
Connection Timeout
I’ve 2 database servers. These servers communicate with the other’s databases
via TCP/IP with SQL authentication using VB, COM+ & ASP applications. Both
servers are running W2k & SQL2k, each with the latest SPs & hot fixes.
Neither server is a domain server. Server A also supports a VB application
the users access through Terminal Services sessions.
Recently we began experiencing a problem on server A. When logged on to the
server with an administrator account the user can connect to the other
server’s database through the VB applications & through SQL Query Analyzer.
However, when logged on as a user that isn’t an administrator, they cannot
connect to the other server; they get a Timeout Expired error message. This
occurs every time.
From server A when logged on as a standard user we can telnet to server B on
port 1433. We cannot odbcping nor use SQL Query Analyzer to connect to server
B. The IP address & the SQL user /password is included in all connections.
I ran Netmon on server B to see what was coming from server A. I only saw
ack messages. I’m not a Netmon expert, so interpreting these messages is a
little beyond me.
I also suspect the problem is on server A. Why else would one user be able
to connect & another not. Beyond that I’m lost.
The only error I see in any of the Event logs from server A is an error
described by KB 326912.
Thanks in advance for any ideas.
You need to make the connections from the client side and review them. See
if the tcp 3 way handshake is completing.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I'm able to see a 3 Way Hand Shake & Graceful Close. There is also some Push
activity in the trace.
Examining this activity is iffy at best, because these are production
servers. My problem only occurs when the user on Server A is not an
administrator. Some of the activity in the capture could be the result of
other user activity. I think the trace I captured was during an isolated
period, but I cannot be assured of this. The Hand Shake & Grageful Close were
the 1st & last events in the capture.
"Kevin McDonnell [MSFT]" wrote:
> You need to make the connections from the client side and review them. See
> if the tcp 3 way handshake is completing.
> Q169292 The Basics of Reading TCP/IP Traces
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Is there a policy defined on the system that might explain why there is a
permission problem?
gpresult.exe will show you if any policies are in place.
Are there any security templates in use?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I'm not aware of a policy that is set to do that.
I ran gpresult without learning much, which didn't surprise me since this is
a stand-alone server.
There are Local Policies, most are default values. I did read through all of
these & nothing stuck out as a possible culprit. Do you have any policies in
mind that may need further scrutiny?
Sam
"Kevin McDonnell [MSFT]" wrote:
> Is there a policy defined on the system that might explain why there is a
> permission problem?
> gpresult.exe will show you if any policies are in place.
> Are there any security templates in use?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Clients need "access this computer from the network" permission in order to
establish a connection.
Can the same client map a drive to the server?
Does the problem happen with Named Pipes as well as TCP/IP?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I checked the 'Access this computer from the Network' local policy. It
includes Users.
The user connects to Server A through a Terminal Services Connection.
Mapping a drive isn't an option I've pursued.
Since these are standalone servers I don't know how to connect from one to
the other using named pipes. (One is in Atlanta & the other in Washington DC.
They communicate via Internet. Force Encryption is on.) If I enter the
www.servername.com of Server B, I think that uses TCP/IP. I tried it in Query
Analyzer & got the Timeout message.
I installed FileMon from www.SysInternal.com on Server A. I saw some files
that were not found, but the system reverted to Winnt\system32 for the same
files & was succesful. I didn't see any unsuccessful attempts logged for the
User. This leads me to suppose it isn't a file permission problem.
I restarted Server A last night, but this didn't accompolish anything either.
Sam
"Kevin McDonnell [MSFT]" wrote:
> Clients need "access this computer from the network" permission in order to
> establish a connection.
> Can the same client map a drive to the server?
> Does the problem happen with Named Pipes as well as TCP/IP?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||I would run Microsoft Network Monitor from the Terminal Server machine to
trace the traffic to SQL.
Compare the admin trace to the user trace.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I have the traces. I was able to run them when there wasn't any other
activity between the systems.
Both traces are escentially the the same except in the admin trace I see
more TDS protocol packets. Both start with the 3 Way Hand Shake & end with
the Graceful Close. The 1st 12 packets of each trace are the same. Then the
admin trace has 12 TDS protocol packets with a description beginning with
'UNKNOWN EPM ACK Len ='. The trace that fails does not have any of these
packets.
The user trace does have some TDS protocol packets, but for some reason that
the trace doesn't show, it doesn't continue with the TDS packets the admin
trace has.
Sam
"Kevin McDonnell [MSFT]" wrote:
> I would run Microsoft Network Monitor from the Terminal Server machine to
> trace the traffic to SQL.
> Compare the admin trace to the user trace.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Hi Sam,
Sounds like you made a good capture. Unfortunately, the best way to
resolve this would be to open a case and have a SQL Engineer review the
traces. It would be too difficult to diagnose the traces in this forum.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
via TCP/IP with SQL authentication using VB, COM+ & ASP applications. Both
servers are running W2k & SQL2k, each with the latest SPs & hot fixes.
Neither server is a domain server. Server A also supports a VB application
the users access through Terminal Services sessions.
Recently we began experiencing a problem on server A. When logged on to the
server with an administrator account the user can connect to the other
server’s database through the VB applications & through SQL Query Analyzer.
However, when logged on as a user that isn’t an administrator, they cannot
connect to the other server; they get a Timeout Expired error message. This
occurs every time.
From server A when logged on as a standard user we can telnet to server B on
port 1433. We cannot odbcping nor use SQL Query Analyzer to connect to server
B. The IP address & the SQL user /password is included in all connections.
I ran Netmon on server B to see what was coming from server A. I only saw
ack messages. I’m not a Netmon expert, so interpreting these messages is a
little beyond me.
I also suspect the problem is on server A. Why else would one user be able
to connect & another not. Beyond that I’m lost.
The only error I see in any of the Event logs from server A is an error
described by KB 326912.
Thanks in advance for any ideas.
You need to make the connections from the client side and review them. See
if the tcp 3 way handshake is completing.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I'm able to see a 3 Way Hand Shake & Graceful Close. There is also some Push
activity in the trace.
Examining this activity is iffy at best, because these are production
servers. My problem only occurs when the user on Server A is not an
administrator. Some of the activity in the capture could be the result of
other user activity. I think the trace I captured was during an isolated
period, but I cannot be assured of this. The Hand Shake & Grageful Close were
the 1st & last events in the capture.
"Kevin McDonnell [MSFT]" wrote:
> You need to make the connections from the client side and review them. See
> if the tcp 3 way handshake is completing.
> Q169292 The Basics of Reading TCP/IP Traces
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Is there a policy defined on the system that might explain why there is a
permission problem?
gpresult.exe will show you if any policies are in place.
Are there any security templates in use?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I'm not aware of a policy that is set to do that.
I ran gpresult without learning much, which didn't surprise me since this is
a stand-alone server.
There are Local Policies, most are default values. I did read through all of
these & nothing stuck out as a possible culprit. Do you have any policies in
mind that may need further scrutiny?
Sam
"Kevin McDonnell [MSFT]" wrote:
> Is there a policy defined on the system that might explain why there is a
> permission problem?
> gpresult.exe will show you if any policies are in place.
> Are there any security templates in use?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Clients need "access this computer from the network" permission in order to
establish a connection.
Can the same client map a drive to the server?
Does the problem happen with Named Pipes as well as TCP/IP?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I checked the 'Access this computer from the Network' local policy. It
includes Users.
The user connects to Server A through a Terminal Services Connection.
Mapping a drive isn't an option I've pursued.
Since these are standalone servers I don't know how to connect from one to
the other using named pipes. (One is in Atlanta & the other in Washington DC.
They communicate via Internet. Force Encryption is on.) If I enter the
www.servername.com of Server B, I think that uses TCP/IP. I tried it in Query
Analyzer & got the Timeout message.
I installed FileMon from www.SysInternal.com on Server A. I saw some files
that were not found, but the system reverted to Winnt\system32 for the same
files & was succesful. I didn't see any unsuccessful attempts logged for the
User. This leads me to suppose it isn't a file permission problem.
I restarted Server A last night, but this didn't accompolish anything either.
Sam
"Kevin McDonnell [MSFT]" wrote:
> Clients need "access this computer from the network" permission in order to
> establish a connection.
> Can the same client map a drive to the server?
> Does the problem happen with Named Pipes as well as TCP/IP?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||I would run Microsoft Network Monitor from the Terminal Server machine to
trace the traffic to SQL.
Compare the admin trace to the user trace.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I have the traces. I was able to run them when there wasn't any other
activity between the systems.
Both traces are escentially the the same except in the admin trace I see
more TDS protocol packets. Both start with the 3 Way Hand Shake & end with
the Graceful Close. The 1st 12 packets of each trace are the same. Then the
admin trace has 12 TDS protocol packets with a description beginning with
'UNKNOWN EPM ACK Len ='. The trace that fails does not have any of these
packets.
The user trace does have some TDS protocol packets, but for some reason that
the trace doesn't show, it doesn't continue with the TDS packets the admin
trace has.
Sam
"Kevin McDonnell [MSFT]" wrote:
> I would run Microsoft Network Monitor from the Terminal Server machine to
> trace the traffic to SQL.
> Compare the admin trace to the user trace.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Hi Sam,
Sounds like you made a good capture. Unfortunately, the best way to
resolve this would be to open a case and have a SQL Engineer review the
traces. It would be too difficult to diagnose the traces in this forum.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Saturday, February 25, 2012
Connection Timeout
I’ve 2 database servers. These servers communicate with the other’s data
bases
via TCP/IP with SQL authentication using VB, COM+ & ASP applications. Both
servers are running W2k & SQL2k, each with the latest SPs & hot fixes.
Neither server is a domain server. Server A also supports a VB application
the users access through Terminal Services sessions.
Recently we began experiencing a problem on server A. When logged on to the
server with an administrator account the user can connect to the other
server’s database through the VB applications & through SQL Query Analyzer
.
However, when logged on as a user that isn’t an administrator, they cannot
connect to the other server; they get a Timeout Expired error message. This
occurs every time.
From server A when logged on as a standard user we can telnet to server B on
port 1433. We cannot odbcping nor use SQL Query Analyzer to connect to serve
r
B. The IP address & the SQL user /password is included in all connections.
I ran Netmon on server B to see what was coming from server A. I only saw
ack messages. I’m not a Netmon expert, so interpreting these messages is a
little beyond me.
I also suspect the problem is on server A. Why else would one user be able
to connect & another not. Beyond that I’m lost.
The only error I see in any of the Event logs from server A is an error
described by KB 326912.
Thanks in advance for any ideas.You need to make the connections from the client side and review them. See
if the tcp 3 way handshake is completing.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I'm able to see a 3 Way Hand Shake & Graceful Close. There is also some Push
activity in the trace.
Examining this activity is iffy at best, because these are production
servers. My problem only occurs when the user on Server A is not an
administrator. Some of the activity in the capture could be the result of
other user activity. I think the trace I captured was during an isolated
period, but I cannot be assured of this. The Hand Shake & Grageful Close wer
e
the 1st & last events in the capture.
"Kevin McDonnell [MSFT]" wrote:
> You need to make the connections from the client side and review them. Se
e
> if the tcp 3 way handshake is completing.
> Q169292 The Basics of Reading TCP/IP Traces
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Is there a policy defined on the system that might explain why there is a
permission problem?
gpresult.exe will show you if any policies are in place.
Are there any security templates in use?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I'm not aware of a policy that is set to do that.
I ran gpresult without learning much, which didn't surprise me since this is
a stand-alone server.
There are Local Policies, most are default values. I did read through all of
these & nothing stuck out as a possible culprit. Do you have any policies in
mind that may need further scrutiny?
Sam
"Kevin McDonnell [MSFT]" wrote:
> Is there a policy defined on the system that might explain why there is a
> permission problem?
> gpresult.exe will show you if any policies are in place.
> Are there any security templates in use?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Clients need "access this computer from the network" permission in order to
establish a connection.
Can the same client map a drive to the server?
Does the problem happen with Named Pipes as well as TCP/IP?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I checked the 'Access this computer from the Network' local policy. It
includes Users.
The user connects to Server A through a Terminal Services Connection.
Mapping a drive isn't an option I've pursued.
Since these are standalone servers I don't know how to connect from one to
the other using named pipes. (One is in Atlanta & the other in Washington DC
.
They communicate via Internet. Force Encryption is on.) If I enter the
www.servername.com of Server B, I think that uses TCP/IP. I tried it in Quer
y
Analyzer & got the Timeout message.
I installed FileMon from www.SysInternal.com on Server A. I saw some files
that were not found, but the system reverted to Winnt\system32 for the same
files & was succesful. I didn't see any unsuccessful attempts logged for the
User. This leads me to suppose it isn't a file permission problem.
I restarted Server A last night, but this didn't accompolish anything either
.
Sam
"Kevin McDonnell [MSFT]" wrote:
> Clients need "access this computer from the network" permission in order t
o
> establish a connection.
> Can the same client map a drive to the server?
> Does the problem happen with Named Pipes as well as TCP/IP?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||I would run Microsoft Network Monitor from the Terminal Server machine to
trace the traffic to SQL.
Compare the admin trace to the user trace.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have the traces. I was able to run them when there wasn't any other
activity between the systems.
Both traces are escentially the the same except in the admin trace I see
more TDS protocol packets. Both start with the 3 Way Hand Shake & end with
the Graceful Close. The 1st 12 packets of each trace are the same. Then the
admin trace has 12 TDS protocol packets with a description beginning with
'UNKNOWN EPM ACK Len ='. The trace that fails does not have any of these
packets.
The user trace does have some TDS protocol packets, but for some reason that
the trace doesn't show, it doesn't continue with the TDS packets the admin
trace has.
Sam
"Kevin McDonnell [MSFT]" wrote:
> I would run Microsoft Network Monitor from the Terminal Server machine to
> trace the traffic to SQL.
> Compare the admin trace to the user trace.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Hi Sam,
Sounds like you made a good capture. Unfortunately, the best way to
resolve this would be to open a case and have a SQL Engineer review the
traces. It would be too difficult to diagnose the traces in this forum.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
bases
via TCP/IP with SQL authentication using VB, COM+ & ASP applications. Both
servers are running W2k & SQL2k, each with the latest SPs & hot fixes.
Neither server is a domain server. Server A also supports a VB application
the users access through Terminal Services sessions.
Recently we began experiencing a problem on server A. When logged on to the
server with an administrator account the user can connect to the other
server’s database through the VB applications & through SQL Query Analyzer
.
However, when logged on as a user that isn’t an administrator, they cannot
connect to the other server; they get a Timeout Expired error message. This
occurs every time.
From server A when logged on as a standard user we can telnet to server B on
port 1433. We cannot odbcping nor use SQL Query Analyzer to connect to serve
r
B. The IP address & the SQL user /password is included in all connections.
I ran Netmon on server B to see what was coming from server A. I only saw
ack messages. I’m not a Netmon expert, so interpreting these messages is a
little beyond me.
I also suspect the problem is on server A. Why else would one user be able
to connect & another not. Beyond that I’m lost.
The only error I see in any of the Event logs from server A is an error
described by KB 326912.
Thanks in advance for any ideas.You need to make the connections from the client side and review them. See
if the tcp 3 way handshake is completing.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I'm able to see a 3 Way Hand Shake & Graceful Close. There is also some Push
activity in the trace.
Examining this activity is iffy at best, because these are production
servers. My problem only occurs when the user on Server A is not an
administrator. Some of the activity in the capture could be the result of
other user activity. I think the trace I captured was during an isolated
period, but I cannot be assured of this. The Hand Shake & Grageful Close wer
e
the 1st & last events in the capture.
"Kevin McDonnell [MSFT]" wrote:
> You need to make the connections from the client side and review them. Se
e
> if the tcp 3 way handshake is completing.
> Q169292 The Basics of Reading TCP/IP Traces
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Is there a policy defined on the system that might explain why there is a
permission problem?
gpresult.exe will show you if any policies are in place.
Are there any security templates in use?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I'm not aware of a policy that is set to do that.
I ran gpresult without learning much, which didn't surprise me since this is
a stand-alone server.
There are Local Policies, most are default values. I did read through all of
these & nothing stuck out as a possible culprit. Do you have any policies in
mind that may need further scrutiny?
Sam
"Kevin McDonnell [MSFT]" wrote:
> Is there a policy defined on the system that might explain why there is a
> permission problem?
> gpresult.exe will show you if any policies are in place.
> Are there any security templates in use?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Clients need "access this computer from the network" permission in order to
establish a connection.
Can the same client map a drive to the server?
Does the problem happen with Named Pipes as well as TCP/IP?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I checked the 'Access this computer from the Network' local policy. It
includes Users.
The user connects to Server A through a Terminal Services Connection.
Mapping a drive isn't an option I've pursued.
Since these are standalone servers I don't know how to connect from one to
the other using named pipes. (One is in Atlanta & the other in Washington DC
.
They communicate via Internet. Force Encryption is on.) If I enter the
www.servername.com of Server B, I think that uses TCP/IP. I tried it in Quer
y
Analyzer & got the Timeout message.
I installed FileMon from www.SysInternal.com on Server A. I saw some files
that were not found, but the system reverted to Winnt\system32 for the same
files & was succesful. I didn't see any unsuccessful attempts logged for the
User. This leads me to suppose it isn't a file permission problem.
I restarted Server A last night, but this didn't accompolish anything either
.
Sam
"Kevin McDonnell [MSFT]" wrote:
> Clients need "access this computer from the network" permission in order t
o
> establish a connection.
> Can the same client map a drive to the server?
> Does the problem happen with Named Pipes as well as TCP/IP?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||I would run Microsoft Network Monitor from the Terminal Server machine to
trace the traffic to SQL.
Compare the admin trace to the user trace.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have the traces. I was able to run them when there wasn't any other
activity between the systems.
Both traces are escentially the the same except in the admin trace I see
more TDS protocol packets. Both start with the 3 Way Hand Shake & end with
the Graceful Close. The 1st 12 packets of each trace are the same. Then the
admin trace has 12 TDS protocol packets with a description beginning with
'UNKNOWN EPM ACK Len ='. The trace that fails does not have any of these
packets.
The user trace does have some TDS protocol packets, but for some reason that
the trace doesn't show, it doesn't continue with the TDS packets the admin
trace has.
Sam
"Kevin McDonnell [MSFT]" wrote:
> I would run Microsoft Network Monitor from the Terminal Server machine to
> trace the traffic to SQL.
> Compare the admin trace to the user trace.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Hi Sam,
Sounds like you made a good capture. Unfortunately, the best way to
resolve this would be to open a case and have a SQL Engineer review the
traces. It would be too difficult to diagnose the traces in this forum.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Friday, February 24, 2012
Connection String has Semicolon (;) - how on earth can I get this working?
Ok, here's my setup. I've got a named instance in a SQL 2000 cluster. I only have dbo rights on my database, because it is a shared infrastructure. Here's my current web.config connection string (the meat, anyway):
When I'm at the office, this is my connection string, pretty normal:
connectionString="Data Source=ServerName\InstanceName;Initial Catalog=blah..."
But, when I connect through the VPN, I can't just connect through the named instance - I have a specific port. This is where things get odd.
First, if I try to connect through SQL Server Management Studio (2005), i get nothing. If I try to connect using"ServerName\InstanceName, (comma) Port Number" it connects, but not to my instance. I get a seperate set of databases that I believe are in the default instance. So, I changed the comma to a semicolon (;) - and it still connected to the same thing - connected to the database, but to the wrong set of databases. So, on a whim, I tried plunking my string, which was now"ServerName\InstanceName;(semicolon) PortNumber" into the SQL 2000 Tools and it worked in both Query Analyzer and in Enterprise Manager. So, I thought, I'll just slam this into my connection string and all will be well. No. I can't use a semicolon in my connection string, and I can't find an escape character to use. Double semicolons don't work, a comma doesn't connect me properly, double colons don't work, the JDBC brackets don't work {} - so I'm at a loss. I'm out of ideas. I've set up aliases, and those don't work earlier.
I'm using ASP.net 2 with VB & C# and Visual Studio 2005 Professional. Thanks for any help anyone can give on this!
Have you tried with a Colon instead of a semicolon?
|||I have - no dice. I've tried a colon after the servername but before the instance name (i.e.ServerName:Port\InstanceName) as well as at the end of the entire name (ServerName\InstanceName:Port).
|||Hi jdandison,
The standard format to conifgure your port number in connection string is:string connectionString = "Server=Servername\InstanceName;port=your port number;"
So, try to change your connection string to :connectionString="Data Source=ServerName\InstanceName;port=NewPortNumber;Initial Catalog=blah..."and try again.
Tell us if you have any further questions. thanks
Sunday, February 12, 2012
connection problems
Hi, I've installed VWD, SQL Express and IIS successfully using Windows autentication. Everything went smoothly and I had a functional connection to my new database. But suddenly without any obvious reason I could not connect. When I try to connect inside VWD I receive the error:
"An error has occurred while establishing a connection to the server. When connection to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error 26 - Error Locating Server/Instance Specified)."
When I try to connenct through SQL Server Management Studio Express I receive the error:
Cannot connect to I6000\SQLExpress. Additional info as described above.
I haven't done any changes as far I know, so what could be the issue here? I haven't any experience from working with SQL so I am very grateful for any help.
Open VS2005, go to Tools/Connect to Database and try from there to open the database. That should tell you what the problem is.
Cheers
Al
|||Hi cosmos,
You would have to habilitate TCP/IP protocol for your SQL Server Express.
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
Friday, February 10, 2012
Connection problem with NT4
Ive installed SQL2000 in a NT4-SP6 server, but I cant connect from another machine.
I receive the following msg in Query analizer : "sql server does not exists or access denied"
Ive tried both authentications methods, created alias with IP and server name but nothing works.
If I use query analizer locally I can connect using the server name, but cant if use the ip.
Please, save me :)
thanks in advanceHi Felipe,
I faced a similiar problem when using a Business-Intelligence Client
to connect from a NT-Client to our cube located on a XP-PC with
MS SQL and MS OLAP.
I tried realy everthing:
At the end the following worked.
I added every user, who wants to connect locally(!) as an account.
I added a role to the MS OLAP cube and added the new user to this
role.
Hint:
These users must be domain-users
(and in our case local users too )
hth
Michael|||thanks for your time Michael, but I cant connect with "sa"
I receive the following msg in Query analizer : "sql server does not exists or access denied"
Ive tried both authentications methods, created alias with IP and server name but nothing works.
If I use query analizer locally I can connect using the server name, but cant if use the ip.
Please, save me :)
thanks in advanceHi Felipe,
I faced a similiar problem when using a Business-Intelligence Client
to connect from a NT-Client to our cube located on a XP-PC with
MS SQL and MS OLAP.
I tried realy everthing:
At the end the following worked.
I added every user, who wants to connect locally(!) as an account.
I added a role to the MS OLAP cube and added the new user to this
role.
Hint:
These users must be domain-users
(and in our case local users too )
hth
Michael|||thanks for your time Michael, but I cant connect with "sa"
Subscribe to:
Posts (Atom)