Recovering from resource problems

Hi,
Yesterday we had some 701 resource messages on one of our production
servers. This was followed by a number of Downgrading backup log buffers
from 1024K to 64K messages in our backup jobs. This is an SQL2005 build 3159
server on W2K3 SP2. We had similar problems last year with an SQL2000 server
and we found that a reboot or stop/start SQL appeared the only way to stop
the problem. We did this last night on this server.
Is this the best way on SQL2005 or will it recover from its resource
problems without a reboot or stop/start SQL?
Thanks
Chris
It is hard to say if this would address the specific problem but this is
about as close as you can get w/o a restart:
dbcc dropcleanbuffers
DBCC FREESYSTEMCACHE ( 'ALL' ) WITH MARK_IN_USE_FOR_REMOVAL
exec sp_msforeachdb 'alter ? set single_user with rollback immediate'
exec sp_msforeachdb 'alter ? set multi_user with rollback immediate'
Be careful. I'd use this like a hail mary with 2 seconds left.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O$Ta6z0jIHA.5084@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Yesterday we had some 701 resource messages on one of our production
> servers. This was followed by a number of Downgrading backup log buffers
> from 1024K to 64K messages in our backup jobs. This is an SQL2005 build
> 3159 server on W2K3 SP2. We had similar problems last year with an SQL2000
> server and we found that a reboot or stop/start SQL appeared the only way
> to stop the problem. We did this last night on this server.
> Is this the best way on SQL2005 or will it recover from its resource
> problems without a reboot or stop/start SQL?
> Thanks
> Chris
>
|||That is pretty harsh!!! I was hoping that SQL2005 was more robust than
SQL2000.
Thanks
Chris
"Jason Massie" <jason**R3move**@.statisticsio.com> wrote in message
news:ud9ZVO1jIHA.4940@.TK2MSFTNGP02.phx.gbl...
> It is hard to say if this would address the specific problem but this is
> about as close as you can get w/o a restart:
> dbcc dropcleanbuffers
> DBCC FREESYSTEMCACHE ( 'ALL' ) WITH MARK_IN_USE_FOR_REMOVAL
> exec sp_msforeachdb 'alter ? set single_user with rollback immediate'
> exec sp_msforeachdb 'alter ? set multi_user with rollback immediate'
> Be careful. I'd use this like a hail mary with 2 seconds left.
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:O$Ta6z0jIHA.5084@.TK2MSFTNGP04.phx.gbl...
>
sql

Recovering from resource problems

Hi,
Yesterday we had some 701 resource messages on one of our production
servers. This was followed by a number of Downgrading backup log buffers
from 1024K to 64K messages in our backup jobs. This is an SQL2005 build 3159
server on W2K3 SP2. We had similar problems last year with an SQL2000 server
and we found that a reboot or stop/start SQL appeared the only way to stop
the problem. We did this last night on this server.
Is this the best way on SQL2005 or will it recover from its resource
problems without a reboot or stop/start SQL?
Thanks
ChrisIt is hard to say if this would address the specific problem but this is
about as close as you can get w/o a restart:
dbcc dropcleanbuffers
DBCC FREESYSTEMCACHE ( 'ALL' ) WITH MARK_IN_USE_FOR_REMOVAL
exec sp_msforeachdb 'alter ? set single_user with rollback immediate'
exec sp_msforeachdb 'alter ? set multi_user with rollback immediate'
Be careful. I'd use this like a hail mary with 2 seconds left.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O$Ta6z0jIHA.5084@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Yesterday we had some 701 resource messages on one of our production
> servers. This was followed by a number of Downgrading backup log buffers
> from 1024K to 64K messages in our backup jobs. This is an SQL2005 build
> 3159 server on W2K3 SP2. We had similar problems last year with an SQL2000
> server and we found that a reboot or stop/start SQL appeared the only way
> to stop the problem. We did this last night on this server.
> Is this the best way on SQL2005 or will it recover from its resource
> problems without a reboot or stop/start SQL?
> Thanks
> Chris
>|||That is pretty harsh!!! I was hoping that SQL2005 was more robust than
SQL2000.
Thanks
Chris
"Jason Massie" <jason**R3move**@.statisticsio.com> wrote in message
news:ud9ZVO1jIHA.4940@.TK2MSFTNGP02.phx.gbl...
> It is hard to say if this would address the specific problem but this is
> about as close as you can get w/o a restart:
> dbcc dropcleanbuffers
> DBCC FREESYSTEMCACHE ( 'ALL' ) WITH MARK_IN_USE_FOR_REMOVAL
> exec sp_msforeachdb 'alter ? set single_user with rollback immediate'
> exec sp_msforeachdb 'alter ? set multi_user with rollback immediate'
> Be careful. I'd use this like a hail mary with 2 seconds left.
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:O$Ta6z0jIHA.5084@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Yesterday we had some 701 resource messages on one of our production
>> servers. This was followed by a number of Downgrading backup log buffers
>> from 1024K to 64K messages in our backup jobs. This is an SQL2005 build
>> 3159 server on W2K3 SP2. We had similar problems last year with an
>> SQL2000 server and we found that a reboot or stop/start SQL appeared the
>> only way to stop the problem. We did this last night on this server.
>> Is this the best way on SQL2005 or will it recover from its resource
>> problems without a reboot or stop/start SQL?
>> Thanks
>> Chris
>

Recordset to Array

Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.
See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan
|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith

Recordset to Array

Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith

Recordset to Array

Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith

Recordset limit per connection?

Hi all,

I have recently moved over from SQL Server 2000 to 2005 and am now having an issue with my application with what appears to be, the number of recordsets that I can open/close on a single connection.

Here is a snippet of what I'm doing, in VB 2005 using ADO... (assuming the connection is already open and working)

Dim RS1 As RecordSet.

DIM RS2 As RecordSet

RS1.Open("SELECT...")

Do While Not RS1.EOF

count = count + 1

Console.Writeline(count)

RS2.Open("SELECT...")

..some processing...

RS2.Close

Loop

RS2 = Nothing

RS1.Close

RS1 = Nothing

Now as I said this all works fine when I connect to an SQL 2000 server but on SQL 2005 it bombs out when 'count' is approximately 1940 with an exception saying that the login failed. If I have Server Management Studio open, that connection will then freeze and throw up an error about how only one usage of each socket address is normally permitted - I think thats more a red herring though.

Any ideas? I've been through all the server settings and can not seem to find anything about recordset limits or timeouts. The only way I've been able to get around this problem at present is to open a new connection object for each iteration to be used by RS2.

Thanks everyone. Any pointers would be much appreciated.

For each RecordSet.open there can be a TCP socket open for communicating with the remote server and each socket will consume system resource and can only be cleaned up after a configurable timeout. If you open too freqently, your system resource for sockets can be exhausted and so the error message.

I recommend you to turn on connection pooling on your client.

-HTH

|||

Hi, and thank you for your response.

I am opening one connection, via a connection object and connection string that is then used by the rest of the application and passed as a parameter to the RecordSet.Open call so I thought it would only be using the one connection and not using any more ports?

As that is the case I didn't think another TCP port would be opened for each RecordSet. When viewing the active connections in SQL Server Management Studio there is only ever one open too..

|||What if you set RS2 to Nothing inside the loop, right after close. Could you try and see if this changes the behavior?|||

Another way to confirm whether it is too many socket issue, you can run the following command in a console,

"netstat -ano"

If you see lots timewait, then using pool would be good.

|||Just FYI. Connection pool is on by default it ADO.|||

Alas moving the RS2 = Nothing inside the loop doesn't help and checking the TCP/IP ports, only one connection is being open. It's very strange. I am getting around it at present by opening a new connection for each iteration. Not ideal at all.

Thanks for all your input though. I *will* get to the bottom of this!

|||

Just in case connection pooling might have been turned off using the registry, you might want to refactor your code slightly to open a connection object before the while loop then use that in the recordset open inside the while loop so for example,

Dim RS1 As RecordSet.

DIM RS2 As RecordSet

Dim cn as ADODB.Connection

set cn = new ADODB.Connection

cn.open <myconnectionstring> 'where <myconnectionstring> is your connection string

RS1.Open("SELECT...", cn)

Do While Not RS1.EOF

count = count + 1

Console.Writeline(count)

RS2.Open("SELECT...", cn)

..some processing...

RS2.Close

Loop

RS2 = Nothing

RS1.Close

RS1 = Nothing

|||Yeah that's what I am doing so I can't understand why it would fail after a certain time.. unless their is network trouble and the connection is getting dropped? I think a solution might be to try the failed login error and re-login.

Recordset limit per connection?

Hi all,

I have recently moved over from SQL Server 2000 to 2005 and am now having an issue with my application with what appears to be, the number of recordsets that I can open/close on a single connection.

Here is a snippet of what I'm doing, in VB 2005 using ADO... (assuming the connection is already open and working)

Dim RS1 As RecordSet.

DIM RS2 As RecordSet

RS1.Open("SELECT...")

Do While Not RS1.EOF

count = count + 1

Console.Writeline(count)

RS2.Open("SELECT...")

..some processing...

RS2.Close

Loop

RS2 = Nothing

RS1.Close

RS1 = Nothing

Now as I said this all works fine when I connect to an SQL 2000 server but on SQL 2005 it bombs out when 'count' is approximately 1940 with an exception saying that the login failed. If I have Server Management Studio open, that connection will then freeze and throw up an error about how only one usage of each socket address is normally permitted - I think thats more a red herring though.

Any ideas? I've been through all the server settings and can not seem to find anything about recordset limits or timeouts. The only way I've been able to get around this problem at present is to open a new connection object for each iteration to be used by RS2.

Thanks everyone. Any pointers would be much appreciated.

For each RecordSet.open there can be a TCP socket open for communicating with the remote server and each socket will consume system resource and can only be cleaned up after a configurable timeout. If you open too freqently, your system resource for sockets can be exhausted and so the error message.

I recommend you to turn on connection pooling on your client.

-HTH

|||

Hi, and thank you for your response.

I am opening one connection, via a connection object and connection string that is then used by the rest of the application and passed as a parameter to the RecordSet.Open call so I thought it would only be using the one connection and not using any more ports?

As that is the case I didn't think another TCP port would be opened for each RecordSet. When viewing the active connections in SQL Server Management Studio there is only ever one open too..

|||What if you set RS2 to Nothing inside the loop, right after close. Could you try and see if this changes the behavior?|||

Another way to confirm whether it is too many socket issue, you can run the following command in a console,

"netstat -ano"

If you see lots timewait, then using pool would be good.

|||Just FYI. Connection pool is on by default it ADO.|||

Alas moving the RS2 = Nothing inside the loop doesn't help and checking the TCP/IP ports, only one connection is being open. It's very strange. I am getting around it at present by opening a new connection for each iteration. Not ideal at all.

Thanks for all your input though. I *will* get to the bottom of this!

|||

Just in case connection pooling might have been turned off using the registry, you might want to refactor your code slightly to open a connection object before the while loop then use that in the recordset open inside the while loop so for example,

Dim RS1 As RecordSet.

DIM RS2 As RecordSet

Dim cn as ADODB.Connection

set cn = new ADODB.Connection

cn.open <myconnectionstring> 'where <myconnectionstring> is your connection string

RS1.Open("SELECT...", cn)

Do While Not RS1.EOF

count = count + 1

Console.Writeline(count)

RS2.Open("SELECT...", cn)

..some processing...

RS2.Close

Loop

RS2 = Nothing

RS1.Close

RS1 = Nothing

|||Yeah that's what I am doing so I can't understand why it would fail after a certain time.. unless their is network trouble and the connection is getting dropped? I think a solution might be to try the failed login error and re-login.

Recordset Counts

I am trying to count the number of records in a recordset I have created. It always returns a value of -1 nomatter how many records are actually there. I have other operations working fine using similar code, but this one will not work correctly. Can anyone help. Here is the code.

Set ashcontentcountcmd = New ADODB.Command
ashcontentcountcmd.ActiveConnection = ashconn
Set ashcontentcountrs = New ADODB.Recordset
ashcontentcountrs.ActiveConnection = ashconn

ashsource = lstash.List(lstash.ListIndex)

ashcontentcount = "select ID from Flyash where Source ='" + ashsource + "'"

ashcontentcountcmd.CommandText = ashcontentcount ashcontentcountrs.Open ashcontentcountcmd

Debug.Print ashcontentcountrs.RecordCount

Thank you in advance for any help.

MichaelYou need to make it a client-side cursor to return the recordcount or your cursor is the wrong type.|||I am new to this stuff. I don't know what you mean by a client-side cursor. This is an Access database I am connecting to.

records per page in ReportViewer

Hi Everyone,

I can't find this to save my life! Is there a way to set the number of records that show per page on the ReportViewer? I'd like to return more per page then returns with the default setting.

thanks,

jekerry

You have to use expressions for that purpose. Have a look at this article:

http://msdn2.microsoft.com/en-us/library/ms251668(VS.80).aspx

Look for Page Breaks section in this article.

|||

Awesome!!! Thanks!

jekerry

Records number of a table

Dear Sir,

Instead of using SELECT count(*) From TABLE_NAME,

Is there any property of a Table that provide the total_records of this table?

Many Thanks

Xueliang

There's a quick way that returns a rowcount that is not necessarily 100% accurate, but that is good enough for most purposes, see below.

Chris

SELECT rows AS [RowCount]

FROM sysindexes

WHERE OBJECT_NAME(id) = '<Table Name>'

AND indid <= 1

|||

if u hv any mumeric fields ..u can chk out by diiference.. like auto id can sense this..or if product id p001 to p022 u can chk this too,

or the solutin by chris may work i hvnt tried..

|||

great job done sir..

sir, will it b common for any table?

will u plz explain syntax?

|||just wonder is there anything like row_counts_fuction(TABLE_NAME) ?

I think the SQL Server System should keep the total records number somewhere.

Thanks a lot

Xueliang|||

Chris Howarth rights. Statement in his answer doesn't count rows in table, but gets it from system table.

If you want function, you could create it:

create function row_counts_fuction(@.table_name varchar(50)) returns int

as

begin

declare @.result int

SELECT @.result = rows

FROM sysindexes

WHERE OBJECT_NAME(id) = @.table_name

AND indid <= 1

return @.result

end

Then:

select count(*) from Sales.SalesOrderDetail

--Returns 121317, works 0,21 sec

select dbo.row_counts_fuction('SalesOrderDetail')

--Returns 121317, works 0,00 sec

|||

>will u plz explain syntax?

Rather than me re-iterate what Microsoft say about sysindexes, let me point you towards the relevant topic in BOL - here you can see why the 'indid' column is included in the WHERE clause of the query.

http://msdn2.microsoft.com/en-us/library/ms190283.aspx

Chris

|||

If AUTO_CREATE_STATISTICS is not ON and not manually updated, then do I get Proper record

count from sysindexes ?

|||

To improve accuracy you should run DBCC UPDATEUSAGE before running the query.

See the following links for more info:

SQL Server 2000

http://msdn2.microsoft.com/en-gb/library/aa258283(sql.80).aspx

SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms188414.aspx

Apparently, according to MS, there should be no need to run the command in SQL Server 2005 as the statistics are maintained correctly.

Chris

RecordNumber by Group?

Hello,

Is it possible to show a record number or count by group? I know the RecordNumber field will show me the number for the whole report but what I would like is the following:

Group A
1
2
3
Group B
1
2
3

Instead of group B being 4,5,6...

I'm trying to figure out how to number the lines per group heading. I hope that makes sense.

Many thanks,

StephenInsert a count() into the footer and count how you want. It gives the choices|||Thank you for the suggestions but I'm looking to get a count (like a row number) at the detail level however and I don't think I can do it that way (that I'm aware of).|||You have to do it manually for each Group. Increment and show the counter in the detail and reset in the Group Header/footer.|||You can create a running total:

select a field to summarize;
type of summaries: count
evaluate: for each record
reset: on change of group (select one you need)
:wave:

Recordet Problem

I'm creating a Visual Basic program linked to an SQL server DB. In one of my tables I have a field for phone number which is an nvarchar datatype that is stored in the following format (example: 617-782-6415).

I have a form where I want to view the contents of the following recordset:

SELECT d .ACCOUNT_CODE, d .DURATION, d .[DATE], d .[HOUR], d .NUMBER_DIALED, p.ACCOUNT_CODE AS Expr1, p.LAST_NAME, p.FIRST_NAME,
p.SUMMARY_GROUP, p.DIRECT_DIAL, p.TITLE, p.LOCATION, p.DIVISION, p.DEPARTMENT, p.DIRECTOR, p.OLD_CARD_NUM, p.REISSUE, p.CLASS,
p.PHONE
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d .ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE number_dialed = " & astring & "

The value for astring is pulled from a combobox that stores all the values for number_dialed. However when i run this program i get the error message

"syntax error converting the nvarchar value '401-781-7078' to a column of datatype int"

I dont need to convert it to a column of datatype int so I dont understand this error... can anyone help me?The only thing that can be forcing a convertion is

WHERE number_dialed = " & astring & "

What is this tryin to do?
Take one value frm teh combo box?
I assume as you have " as the string delimitter then you are trying to form the select in VB.
You would be better to create an SP and pass the parameter.
For your string here try

WHERE number_dialed = '" & astring & "'"

i.e. making astring into a string in the query.|||Yeah, I'm trying to form the select in VB because I'm more familiar with that way... I'd prefer to do it in VB if at all possible... Why is it converting it?|||WHERE number_dialed = " & astring & "

will give something like

WHERE number_dialed = 617-782-6415

it will evaluate 617-782-6415 giving a negative number then try to convert number_dialed to an integer for the compare.

you need
WHERE number_dialed = '617-782-6415'
to do a string compare
hence the quotes in my previous post.

Record Selection Formula Help

I have the following data for example

GROUP SECTION
Invoice Number [Aug 8, 2007]

DETAILS SECTION
ItemNo | Description | Latest PurchaseDate
SAMPLEA | DESCRIPTIONA | Aug 7, 2007
SAMPLEA | DESCRIPTIONA | Jul 1, 2007
SAMPLEA | DESCRIPTIONA | Jun 5, 2007
SAMPLEB | DESCRIPTIONB | Jun 6, 2007
SAMPLEB | DESCRIPTIONB | May 5, 2007

Is there a way i can only select in the detail section the maximum date of the latest purchase where the latest purchase date should be <= Invoice Number Date


Thanks for the help.I am not sure what you want to do.

One way to show only the latest detail is: 1
1. sort the details section on the appropriate field so that the latest detail appears last.
2. Move all the fields in the detail section down to the group footer, maintaining their location across the page.
3. If you have some sort of totaling in the group footer, create a group footer B and move the summary fields there.
4. Hide or suppress the details section.