Connection problems: number

Showing posts with label number. Show all posts

Tuesday, March 27, 2012

consistency errors and prevention

What causes consistency errors and what can be done to prevent them?
We have a large number (500 or so) of SQL Server 2000 installations with
database sizes ranging from 3 - 120 gig. Average database size would be 15-40
gig. They could be installed on Server 2000 or 2003 or something equivalent
- never on a workstation. Memory on the machines can range from 1gig to 4 gig.
We have lost close to 10 databases this year so far.Most errors are due to hardware problems these days. A failing disk array or
controller is a key culprit in getting corrupt data. But there are other
reasons as well. Bad UPS's, non-battery backed caching controllers, unstable
mother boards etc.
--
Andrew J. Kelly SQL MVP
"Stedak" <Stedak@.discussions.microsoft.com> wrote in message
news:935DF649-22D7-4B2F-9D81-189649200950@.microsoft.com...
> What causes consistency errors and what can be done to prevent them?
> We have a large number (500 or so) of SQL Server 2000 installations with
> database sizes ranging from 3 - 120 gig. Average database size would be
> 15-40
> gig. They could be installed on Server 2000 or 2003 or something
> equivalent
> - never on a workstation. Memory on the machines can range from 1gig to 4
> gig.
> We have lost close to 10 databases this year so far.|||I'm curious what hardware are you using?
I've seen an amazingly consistent string of database corruptions on Tyan
servers recently..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Stedak" <Stedak@.discussions.microsoft.com> wrote in message
news:935DF649-22D7-4B2F-9D81-189649200950@.microsoft.com...
> What causes consistency errors and what can be done to prevent them?
> We have a large number (500 or so) of SQL Server 2000 installations with
> database sizes ranging from 3 - 120 gig. Average database size would be
> 15-40
> gig. They could be installed on Server 2000 or 2003 or something
> equivalent
> - never on a workstation. Memory on the machines can range from 1gig to 4
> gig.
> We have lost close to 10 databases this year so far.|||In addition, below article has some details on what properties you want to ensure your storage
subsystem has:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:uE4IgAO9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> I'm curious what hardware are you using?
> I've seen an amazingly consistent string of database corruptions on Tyan servers recently..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Stedak" <Stedak@.discussions.microsoft.com> wrote in message
> news:935DF649-22D7-4B2F-9D81-189649200950@.microsoft.com...
>> What causes consistency errors and what can be done to prevent them?
>> We have a large number (500 or so) of SQL Server 2000 installations with
>> database sizes ranging from 3 - 120 gig. Average database size would be 15-40
>> gig. They could be installed on Server 2000 or 2003 or something equivalent
>> - never on a workstation. Memory on the machines can range from 1gig to 4 gig.
>> We have lost close to 10 databases this year so far.
>|||No Tyan servers. Sorry. They can be anything from a Dell to a Custom White Box.
"Greg Linwood" wrote:
> I'm curious what hardware are you using?
> I've seen an amazingly consistent string of database corruptions on Tyan
> servers recently..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Stedak" <Stedak@.discussions.microsoft.com> wrote in message
> news:935DF649-22D7-4B2F-9D81-189649200950@.microsoft.com...
> > What causes consistency errors and what can be done to prevent them?
> >
> > We have a large number (500 or so) of SQL Server 2000 installations with
> > database sizes ranging from 3 - 120 gig. Average database size would be
> > 15-40
> > gig. They could be installed on Server 2000 or 2003 or something
> > equivalent
> > - never on a workstation. Memory on the machines can range from 1gig to 4
> > gig.
> >
> > We have lost close to 10 databases this year so far.
>
>sql sql

consistency errors and prevention

Consecutive values

Hi,
Given the following dataset, how can I determine the maximum number of
consecutive Bs for a given A?
A B
- -
2 1
2 2
2 7
3 2
3 3
3 4
3 6
3 8
3 9
3 10
3 13
3 14
3 15
3 16
4 1
4 3
4 5
4 6
4 7
4 8
4 10
5...
So the output should resemble:
A MAX Count for B
- -
2 2 -- for 1 2
3 4 -- for 13 14 15 16
4 1 -- no consecutive numbers
5...
Thanks
JerryHomework assignment?
Tom Dacon
Dacon Software Consulting
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u1EPMXv5FHA.2600@.tk2msftngp13.phx.gbl...
> Hi,
> Given the following dataset, how can I determine the maximum number of
> consecutive Bs for a given A?
> A B
> - -
> 2 1
> 2 2
> 2 7
> 3 2
> 3 3
> 3 4
> 3 6
> 3 8
> 3 9
> 3 10
> 3 13
> 3 14
> 3 15
> 3 16
> 4 1
> 4 3
> 4 5
> 4 6
> 4 7
> 4 8
> 4 10
> 5...
> So the output should resemble:
> A MAX Count for B
> - -
> 2 2 -- for 1 2
> 3 4 -- for 13 14 15 16
> 4 1 -- no consecutive numbers
> 5...
> Thanks
> Jerry
>|||untested, as I'm at home:
select a, max(maxb - b + 1)
from(select a, b, (select max(b) from t t1 where t.a=t1.a and t1.b=t.b+
(select count(*) from t t2 where t2.a=t1.a and t.b<t2.b and t2.b<t1.b)
) maxb from t) t
group by a
could be easier with row_number()|||>Homework assignment?
I googled up:
Jerry Spivey MCT, MCSE, MCSD Senior SQL|||Homework...nah...question from a friend.
Yeah...MCDBA, MCP...just too many letters! ;-)
Good question though :-)
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1131738216.490554.128630@.g43g2000cwa.googlegroups.com...
> I googled up:
> Jerry Spivey MCT, MCSE, MCSD Senior SQL
>|||Alexander,
Thanks for the post. The untested query yeilded the following resultset:
1 1
2 1
3 1
4 1
5 1
6 1
Any other queries to try?
Thanks
Jerry
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1131738038.314420.248450@.f14g2000cwb.googlegroups.com...
> untested, as I'm at home:
> select a, max(maxb - b + 1)
> from(select a, b, (select max(b) from t t1 where t.a=t1.a and t1.b=t.b+
> (select count(*) from t t2 where t2.a=t1.a and t.b<t2.b and t2.b<t1.b)
> ) maxb from t) t
> group by a
> could be easier with row_number()
>|||If my friend's enemy is my enemy, then is my friend's homework my homework?
:)
ML|||untested, as I'm still at home and bored:
assuming combination (a,b) is unique
select s1.a, max(s2.ctb-s1.ctb+1)
from
(select a, b, (select count(*) from tb t2 where t1.a=t2.a and
t1.b<=t2.b) ctb from tb t1 ) s1,
(select a, b, (select count(*) from tb t2 where t1.a=t2.a and
t1.b<=t2.b) ctb from tb t1 ) s2
where s1.a=s2.a
and (s2.b-s1.b)=(s2.ctb-s1.ctb)
group by s1.a
not sure what was wrong with the previous one, maybe because I used t
twice, both as the table name and as an alias.
another one:
select a, max(bmin - b +1) from
(
select left_end.a,left_end.b, min(right_end.b) bmin
from
(
select a, b where not exists(
select 1 from tb t1
where t1.a=t.a
and (t1.b+1)=t.b
from tb t)
) left_end,
(
select a, b where not exists(
select 1 from tb t1
where t1.a=t.a
and (t1.b-1)=t.b
from tb t)
) right_end
where left_end.a=right_end.a
and left_end.b<=right_end.b
group by left_end.a, left_end.b) intervals|||On Fri, 11 Nov 2005 11:26:19 -0800, Jerry Spivey wrote:

>Hi,
>Given the following dataset, how can I determine the maximum number of
>consecutive Bs for a given A?
(snip)
>A MAX Count for B
>- -
>2 2 -- for 1 2
>3 4 -- for 13 14 15 16
>4 1 -- no consecutive numbers
>5...
Hi Jerry,
Why should the series (4 5)/(4 6)/(4 7)/(4 8) not be reported as a
consecutive series? An error in your post, I presume.
The following is untested. Check out www.aspfaq.com/5006 if you prefer a
tested reply.
SELECT A, MAX(last - first)
FROM (SELECT f.A, f.B AS first, MIN(l.B) AS last
FROM YourTable AS f
INNER JOIN YourTable AS l
ON l.A = f.A
AND l.B >= f.B
WHERE NOT EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.A = f.A
AND b.B = f.B - 1)
AND NOT EXISTS
(SELECT *
FROM YourTable AS a
WHERE a.A = f.A
AND a.B = l.B + 1)
GROUP BY f.A, f.B) AS seq
GROUP BY A
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Tuesday, March 20, 2012

Connections in Pool Not Being Reused

I have a form that has a number of database calls. It does these sequentially. In some cases it's not possible (due to archetecture more than anything) to make these calls all on the same connection. However it does open then close the connection. (all connections created are in using statements)

What I'm seeing is that we're getting a timeout when the 100 connection limit is hit. Looking at Activity Monitor I can see these connections growing and growing over time in the activity monitor the more windows you open and close even though these connections are closed. (and they all use exactly the same connection string)

I read that it might be because of SQL Debugging being turned on in vs.net, but that is not enabled in either debug or runtime for us and we're seeing it in client sites as well as locally.

We're running .NET 2.0.

Does anyone have any ideas as to why it won't reuse these clearly properly closed connections?

Thanks!

Does it happen even with a connection alias ? For more information, please refer http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx|||Are the connections using the exact same connection string so you can take advantage of connection pooling? Even with a using block, you might want to try to explicitly close the connection.|||

Yes, they're using the exact same connection string, and yes I tried explicitly closing the connection and while it shows as waiting instead of inuse in the Activity monitor after doing that, they will not be recycled. If I dispose of the form that the connection is on (which is a custom drop down for a custom combo control) then it gets released and the connection gets reused.

I'm not using conneciton aliases. I have yet to see the point.

Connections in Pool Not Being Reused

I read that it might be because of SQL Debugging being turned on in vs.net, but that is not enabled in either debug or runtime for us and we're seeing it in client sites as well as locally.

We're running .NET 2.0.

Does anyone have any ideas as to why it won't reuse these clearly properly closed connections?

Thanks!

I'm not using conneciton aliases. I have yet to see the point.

Tuesday, March 27, 2012

consistency errors and prevention

consistency errors and prevention

consistency errors and prevention

Consecutive values

Tuesday, March 20, 2012

Connections in Pool Not Being Reused

Connections in Pool Not Being Reused

Connection problems

Blog Archive

About Me