Netstat TIME_WAIT on SQL Server Connections

If I understand correctly, the TIME_WAIT Status when you do a NETSTAT on the
server indicates that the connection has been closed, and it is waiting to
clear. Is that right? If so, we have a lot of SQL Server connections that
are in TIME_WAIT status mode. We may have well over 100 or so of these, and
they do not clear out very fast. Why would they stick around like that, and
how do I get them to clear out sooner? Could they potentially be causing
problems by not clearing out?
Thanks,
Jesse
Hi
Connection Pooling on the Client MDAC stack. By default the SQL Driver will
keep the connection open for 120 seconds after a "close" has been issued. If
another request comes along and it uses the same credentials to the same
server, it does not have to re-setup the TCP/IP connection. Instead it puts
it on the connection that has been around the longest in the TIME_WAIT
state.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jesse" <nospam@.fake.com> wrote in message
news:u9T48m3OFHA.1176@.TK2MSFTNGP12.phx.gbl...
> If I understand correctly, the TIME_WAIT Status when you do a NETSTAT on
> the server indicates that the connection has been closed, and it is
> waiting to clear. Is that right? If so, we have a lot of SQL Server
> connections that are in TIME_WAIT status mode. We may have well over 100
> or so of these, and they do not clear out very fast. Why would they stick
> around like that, and how do I get them to clear out sooner? Could they
> potentially be causing problems by not clearing out?
> Thanks,
> Jesse
>
|||I have looked into those settings, and they are set up as you stated,
however, these are staying in TIME_WAIT long after the 120 seconds. For
instance, I can log on to our server at 8:00 or 9:00 at night, when there is
no one on the server, and there are well over 100 connections left in
TIME_WAIT status. Any ideas why?
Jesse
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Ol4H%23U5OFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hi
> Connection Pooling on the Client MDAC stack. By default the SQL Driver
> will keep the connection open for 120 seconds after a "close" has been
> issued. If another request comes along and it uses the same credentials to
> the same server, it does not have to re-setup the TCP/IP connection.
> Instead it puts it on the connection that has been around the longest in
> the TIME_WAIT state.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jesse" <nospam@.fake.com> wrote in message
> news:u9T48m3OFHA.1176@.TK2MSFTNGP12.phx.gbl...
>

Netstat TIME_WAIT on SQL Server Connections

If I understand correctly, the TIME_WAIT Status when you do a NETSTAT on the
server indicates that the connection has been closed, and it is waiting to
clear. Is that right? If so, we have a lot of SQL Server connections that
are in TIME_WAIT status mode. We may have well over 100 or so of these, and
they do not clear out very fast. Why would they stick around like that, and
how do I get them to clear out sooner? Could they potentially be causing
problems by not clearing out?
Thanks,
JesseHi
Connection Pooling on the Client MDAC stack. By default the SQL Driver will
keep the connection open for 120 seconds after a "close" has been issued. If
another request comes along and it uses the same credentials to the same
server, it does not have to re-setup the TCP/IP connection. Instead it puts
it on the connection that has been around the longest in the TIME_WAIT
state.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jesse" <nospam@.fake.com> wrote in message
news:u9T48m3OFHA.1176@.TK2MSFTNGP12.phx.gbl...
> If I understand correctly, the TIME_WAIT Status when you do a NETSTAT on
> the server indicates that the connection has been closed, and it is
> waiting to clear. Is that right? If so, we have a lot of SQL Server
> connections that are in TIME_WAIT status mode. We may have well over 100
> or so of these, and they do not clear out very fast. Why would they stick
> around like that, and how do I get them to clear out sooner? Could they
> potentially be causing problems by not clearing out?
> Thanks,
> Jesse
>|||I have looked into those settings, and they are set up as you stated,
however, these are staying in TIME_WAIT long after the 120 seconds. For
instance, I can log on to our server at 8:00 or 9:00 at night, when there is
no one on the server, and there are well over 100 connections left in
TIME_WAIT status. Any ideas why?
Jesse
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Ol4H%23U5OFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hi
> Connection Pooling on the Client MDAC stack. By default the SQL Driver
> will keep the connection open for 120 seconds after a "close" has been
> issued. If another request comes along and it uses the same credentials to
> the same server, it does not have to re-setup the TCP/IP connection.
> Instead it puts it on the connection that has been around the longest in
> the TIME_WAIT state.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jesse" <nospam@.fake.com> wrote in message
> news:u9T48m3OFHA.1176@.TK2MSFTNGP12.phx.gbl...
>

Nested transactions question.

Hope I am posting in the right forum. If I understand correctly, a ROLLBACK TRAN statement rolls all transactions (if they are nested) back to the original BEGIN TRAN.

I have a situation when an SP uses a transaction, performs a series of operations inside that transaction, including a call to a different SP, which uses a distributed transaction. If the transaction inside the child SP fails and needs to be rolled back, I get a warning message saying that the tran count on the way out is less than that on the way in. No problem, since it's not fatal. But the problem manifests when I attempt to use the SQL Agent to schedule a job to run the parent SP. It fails on that warning message, interpreting it as an error.

I was thinking of disabling the distributed transaction inside the child SP, and just have the transaction in the parent SP, which, again if I understand correctly, should be escalated to a distributed transaction once the child SP is called. The child SP will raise an error (if it's a real error) and then the trnasaction in the parent SP will handle the rollback of everything.

The reason for this elaborate setup is that I need to cycle through a cursor (yes, I know, sloppy, can't see an alternative) in the parent SP, and each iteration begins and commits (or rolls back) a transaction.

Wil this work? Can anyone suggest a better way?

will this help.

select name
into #tb
from master..systypes
go
select * from #tb
go
declare cc cursor forward_only
for select name from #tb

declare @.name sysname
open cc
fetch next from cc into @.name

while @.@.fetch_status=0
begin
begin tran
delete #tb
where name=@.name and name!='sysname'
if @.@.error=0
commit tran
else
rollback tran

fetch next from cc into @.name
end
close cc
deallocate cc
go
select * from #tb
select @.@.trancount
go

if object_id('tempdb..#tb') is not null
drop table #tb
go

|||Hmmm.... Not sure I understand. This is more or less the code I have. Except for where you have "DELETE #tb WHERE..." I have "EXEC spMyStoredProc @.myInputParam". and inside "spMyStoredProc" there is a distributed tran. So the @.@.TRANCOUNT at the beginning of spMyStoredProc is equal to 1. But inside the SP, let's say the transaction fails for whatever reason, and a ROLLBACK is called. The @.@.TRANCOUNT is set to 0 by the ROLLBACK statement. And now @.@.TRANCOUNT at the end of the SP is less than it was at the beginning. That generates a warning message. But apparently the SQL Agent that handles the Job execution treats it as an error. Or the error that caused the ROLLBACK was raised - I am not sure. In any case - Job ends. But I need it to continue. If I just run the code from QueryAnalyzer - it's ok. It generates warnings, but runs through all the cycles. How can I avoid being bounced out of a SQL Job?|||

(Hope I am posting in the right forum. If I understand correctly, a ROLLBACK TRAN statement rolls all transactions (if they are nested) back to the original BEGIN TRAN.)

That is actually not correct if you use Transaction Save Points which you can roll back as needed after service pack 3a of SQL Server 2000. Now to your question add Transaction save points to any Transaction you don't want rolled back with the error code. Run some tests, transaction is a unit of work but the vendors like Microsoft added Save Points which enable nested Transaction without a roll back to one, with Save Points if you fail at number 100 you will have 99 completed. Run a search for Save Points in the BOL. Hope this helps.

|||

Thanks for pointing out the SavePoints. I did research tehm, but I am not sure if this applies here. I guess I am not explaining the issue clearly enough. I DO want the transactions, both of them, rolled back if an error is encountered in either. However, I have a scenario where the parent transaction is in a cursor (just like that code sample above shows). Each cursor iteration begins and ends a transaction. Each of those transactions calls an SP with another (child) transaction. If anything breaks, I want the transactions to roll back, then I want to go on to the next cursor iteration. If I execute the code in QueryAnalyzer - it works fine. If I schedule the execution via an SQL Job - the Job abends on the first error, rolls back the transaction but does NOT go on to the next cursor iteration. My guess is, this is because the warning that is raised by the child SP (standard non-fatal warning that tran count at the beginning of the SP does not match that at the end) is treated as a fatal error by the SQL Agent, and it terminates the Job.

I could be wrong, and it could be the actuall error that caused the transaction rollback in the first place is being raised, and the Agent may be reacting to that. Whatever the case - My Job stops the cursor execution and exits with an error. Which is something I would like to avoid.

|||

If you can run it and the Agent is not running it, that maybe permissions related when you run it is it running under your admin level permissions but when the Agent runs it, it is running under the Agent's permissions so it will not continue after an error in transactions. You can enable xp_cmdshell in the Surface area configuration tool then create a proxy account for the Agent with your admin level permissions. Run a search for Agent proxy account in the BOL, xp_cmdshell is finally documented but disabled by default so you need to enable it in the Surface area configuration tool. If the permission does not solve the problem then you need to separate your transactions into blocks and connect them with sp_executesql or exec statements. Hope this helps.

|||Thanks. My agent is running with admin permissions already, though :-( Final question - my child SP is written in such a way that I can pass it a flag that governs whether or not I want to use transactions (did that for testing). If I disable the child transaction (its a distributed tran), will a rollback on the parent also roll back the changes made by the sub-called SP? I think that the parent transaction will be automatically escalated to distriburted tran in this case, but I am not sure. I can make it into a distributed transaction manually, if needs be. From what I can see - this would be my only option unless I want to duplicate a lot of code.|||

(will a rollback on the parent also roll back the changes made by the sub-called SP?)

No if each is separated by save points and if you are not using MSDTC maybe you should look into it.

|||

They are not separated by save points.

Main SP code:

Code Snippet

OPEN curCustomersToCancel
FETCH NEXT FROM curCustomersToCancel INTO @.CustID
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN DISTRIBUTED TRAN

EXEC @.ReturnCde = spCancelServices @.CustID
If @.ReturnCde <> 0
Begin
IF(@.@.TRANCOUNT > 0) ROLLBACK TRAN

Select @.SpErrDesc = @.retmsg
PRINT '******* @.SpErrDesc = ' + @.SpErrDesc
BREAK
End

Else

Begin

IF(@.@.TRANCOUNT > 0) COMMIT TRAN

End

FETCH NEXT FROM curCustomersToCancel INTO @.CustID

END
CLOSE curCustomersToCancel
DEALLOCATE curCustomersToCancel

The code in spCancelServices has no transactions - it's just a series of SQL statements. Will this properly roll back all modifications made inside spCancelServices in case of an error?

|||

In the link below download the file SQLServer2005_US_ALL on the left side and in there check samples for chapter 13 and 14 in the notepad files and the PDF note that one trigger in chapter 13 can do what you want. And you did not include @.@.Error in your code, if I remember correctly Fetch is an implicit transaction per ANSI SQL. The author Dusan Petkovic got bad book title but he covered SQL Server transaction better than most writers. Hope this helps.

http://www.mhprofessional.com/product.php?cat=112&isbn=0072260939

Nested Lists and Printing

Is there something I need to do to get Nested Lists to print correctly?
When I preview the report, it displays correctly, grouped by Year,
Resource and Part. Yet when I print it or even print preview it,
instead of printing on one page:
Year 2005
Resource A
Part 1
Part 2
Part 3
Resource B
Part 4
Part 5
It prints two pages:
Year 2005
Resource A
Resource B
as page one and:
Part 1
Part 2
Part 3
Part 4
Part 5
as page two.
If I place page one over page two and hold it up to the light, the
formatting and grouping is correct!
I'm using SQL Server 2000 Reporting Services and Visual Studio.NET 2003.Looks to me like you have a page break on the group... Edit the group and
ensure that page break before and after group is NOT selected.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"josef@.wainz.net" wrote:
> Is there something I need to do to get Nested Lists to print correctly?
> When I preview the report, it displays correctly, grouped by Year,
> Resource and Part. Yet when I print it or even print preview it,
> instead of printing on one page:
> Year 2005
> Resource A
> Part 1
> Part 2
> Part 3
> Resource B
> Part 4
> Part 5
> It prints two pages:
> Year 2005
> Resource A
>
>
> Resource B
>
> as page one and:
> Part 1
> Part 2
> Part 3
>
> Part 4
> Part 5
> as page two.
> If I place page one over page two and hold it up to the light, the
> formatting and grouping is correct!
> I'm using SQL Server 2000 Reporting Services and Visual Studio.NET 2003.
>|||Thanks for the suggestions Wayne, but that was one of the first things
I looked at.
I had to address the objects and their relationships.
This heirarchy works:
ReportHeader[Rectangle]
lstYear[List] (Grouping on Year)(Shows "Year 2005", "Year 2006" ...)
lstResource[List] (Grouping on Resource)
GroupHeader0[Rectangle] (Shows "Resource A", "Resource B" ...)
Detail[List] (Displaying "Part 1", "Part 2" ...)
There is still an issue concerning blank pages between filled pages,
but the odd grouping is resolved.|||After all the above work with objects and nesting, it may in fact be
related to page size and margins. The problem occurred again, but I
went through and adjusted page and object sizes and margins, making
sure everything fit inside of 8.5 inches and voila the problem went
away.

Negative values being summed incorrectly

I'm wondering if anyone has seen this before.

I have a cube in which the data is not being rolled up correctly when the values include negative numbers. The fact table and dimension tables are on an Oracle Server (9i). The fact table looks like:

Column Name

Data Type

Nullable

EDP_NO

NUMBER(10,0)

Yes

MONTH_YYYYMM

VARCHAR2(6 Bytes)

Yes

COGS

NUMBER

Yes

INV_VALUE

NUMBER

Yes

QTY_SOLD

NUMBER(10,0)

Yes

ITEM_NO

VARCHAR2(20 Bytes)

Yes

The dimension table looks like:

olumn Name

Data Type

Nullable

EDP_NO

NUMBER(10,0)

No

VENDOR_NAME

VARCHAR2(41 Bytes)

Yes

AP_VENDOR_NAME

VARCHAR2(41 Bytes)

Yes

DCS

VARCHAR2(4 Bytes)

Yes

DEPT_NAME

VARCHAR2(49 Bytes)

Yes

CLASS_NAME

VARCHAR2(49 Bytes)

Yes

SUB_CLASS_NAME

VARCHAR2(49 Bytes)

Yes

CATEGORY_NAME

VARCHAR2(49 Bytes)

Yes

BUYER

VARCHAR2(160 Bytes)

Yes

AP_VENDOR_NO_UI

VARCHAR2(10 Bytes)

Yes

VENDOR_NO_UI

VARCHAR2(10 Bytes)

Yes

ITEM_NAME

VARCHAR2(20 Bytes)

Yes

PLANNER

VARCHAR2(8 Bytes)

Yes

When I create the cube and browse

the data down to the most granular level (EDP_NO),and when the QTY_SOLD measure is less than

zero,SSAS reports it as being -1

larger.For example,if the value in the table is -3,then the cube browser reports it as -4.Summarized values are also off by a similar

amount.That is,if in the table the data is: -4, -3, -1,then the cube will report the total as

11.

I’m wondering if you’ve ever run

into a similar situation.I’ve verified

that the aggregation being used is SUM.

What’s weird about this is that I’m

trying to recreate a cube that already exists in SQL Server 2000.If I migrate the cube to 2005,it works correctly.It’s only when I create the cube manually

that I have this problem.

I appreciate any assistance you can offer.

Thanks,

Mike Hayes

Hi Mike,

Maybe there is incorrect integer truncation, when converting from the Oracle data type: NUMBER(10,0). Since a migrated cube works correctly, could you compare the DataType for the QTY_SOLD Measure in the 2 cubes? The possible DataType values are:

http://msdn2.microsoft.com/es-es/library/ms129408.aspx

>>

SQL Server 2005 Books Online

DataType Element (ASSL)

Defines the data type of the associated element.

...

The values for DataType are defined in the System.Data.OleDb.OleDbType enumeration. However, only the enumeration values in the following table are valid in the DataType element.

Value

Description

BigInt

A 64-bit signed integer. This data type maps to the Int64 data type in Microsoft .NET Framework and the DBTYPE_I8 data type in OLE DB.

Bool

A Boolean value. This data type maps to the Boolean data type in the .NET Framework and the DBTYPE_BOOL data type in OLE DB.

Currency

A currency value ranging from -2⁶³ (or -922,337,203,685,477.5808) to 2⁶³-1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit. This data type maps to the Decimal data type in the .NET Framework and the DBTYPE_CY data type in OLE DB.

Date

Date data, stored as a double-precision floating point number. The whole portion is the number of days since December 30, 1899, while the fractional portion is a fraction of a day. This data type maps to the DateTime data type in the .NET Framework and the DBTYPE_DATE data type in OLE DB.

Double

A double-precision floating point number within the range of -1.79E +308 through 1.79E +308. This data type maps to the Double data type in the .NET Framework and the DBTYPE_R8 data type in OLE DB.

Integer

A 32-bit signed integer. This data type maps to the Int32 data type in the .NET Framework and the DBTYPE_I4 data type in OLE DB.

Single

A single-precision floating point number within the range of -3.40E +38 through 3.40E +38. This data type maps to the Single data type in .NET Framework and the DBTYPE_R4 data type in OLE DB.

SmallInt

A 16-bit signed integer. This data type maps to the Int16 data type in the .NET Framework and the DBTYPE_I2 data type in OLE DB.

TinyInt

An 8-bit signed integer. This data type maps to the SByte data type in the .NET Framework and the DBTYPE_I1 data type in OLE DB.

UnsignedBigInt

A 64-bit unsigned integer. This data type maps to the UInt64 data type in .NET Framework and the DBTYPE_UI8 data type in OLE DB.

UnsignedInt

A 32-bit unsigned integer. This data type maps to the UInt32 data type in the .NET Framework and the DBTYPE_UI4 data type in OLE DB.

UnsignedSmallInt

A 16-bit unsigned integer. This data type maps to the UInt16 data type in the .NET Framework and the DBTYPE_UI2 data type in OLE DB.

WChar

A null-terminated stream of Unicode characters. This data type maps to the String data type in the .NET Framework and the DBTYPE_WSTR data type in OLE DB.

Inherited

The data type of the DataItem contained in the Source element of the Measure element.

Note:

Applicable only to Measure elements.

>>

|||Deepak,

Thanks for the information. I think you're on the right track. The data type of the column in the migrated cube is Double, and the data type of the created cube is Int64. They should both handle the math correctly. I'm in the process of testing this. I'm trying a different data source (using oledb drivers instead of the .net drivers). If that doesn't work, I'll explicitly change the data type using a query instead of a direct link to the table.

I appreciate the info. I'll let you know what happens.

Mike

NEEDED: SQL Server Express 2005 Setup Walkthrough

Someone please help me, Im trying to set up SQLServer on my website(IIS v4.0) and I cant seem to get any web apps that use SQL to intall correctly, I get errors like, SQL does not recieve remote connections by default, or just 'Server Not Found'.

This is what I need to know

- How to set SQL to accept incomming remote connections

- Where to find the Name of the SQL instance

- Is it a good idea to have a password on the db, and how do I add one

- A walkthrough on how to set up SQL Express 2005(what to do after the istall is complete)

As you can see Im very new to SQL. So any information you could give to help would be greatly appreciated.

Thanks

Admin @. Something-to-do.com

1. Here is a KB Article that goes through setting up remote connections. http://support.microsoft.com/kb/914277

2. With SQL Server 2005 Express the server install defaults to ".\SQLEXPRESS" for the instance. To find the name though you can check the windows service list in the Services mmc inside the administration folder under the control panel. The name will be inside brackets beside the SQL Server Service.

3. You should try and stay with Windows Logons for the security.. if you do need to use sql logons you should try a strong password on the database accounts.