Connections from Application are getting disconnected
-
Hello SQL Gurus,
I am experiencing some problems with low performance on my SQL system. The version details are
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
Apr 9 2002 14:18:16
Copyright (c) 1988-2002 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
sp_configure details are
Name
Minimum
Maximum
Config_value
Run_value
affinity mask
0
2147483647
0
0
allow updates
0
1
0
0
cost threshold for parallelism
0
32767
5
5
cursor threshold
-1
2147483647
-1
-1
default language
0
9999
0
0
default sortorder id
0
255
52
52
extended memory size (MB)
0
2147483647
0
0
fill factor (%)
0
100
0
0
index create memory (KB)
704
1600000
0
0
language in cache
3
100
3
3
language neutral full-text
0
1
0
0
lightweight pooling
0
1
0
0
locks
5000
2147483647
0
0
max async IO
1
255
32
32
max degree of parallelism
0
32
0
0
max server memory (MB)
4
2147483647
2040
2040
max text repl size (B)
0
2147483647
65536
65536
max worker threads
10
1024
255
255
media retention
0
365
0
0
min memory per query (KB)
512
2147483647
1024
1024
min server memory (MB)
0
2147483647
2040
2040
nested triggers
0
1
1
1
network packet size (B)
512
65535
4096
4096
open objects
0
2147483647
0
0
priority boost
0
1
1
1
query governor cost limit
0
2147483647
0
0
query wait (s)
-1
2147483647
-1
-1
recovery interval (min)
0
32767
0
0
remote access
0
1
1
1
remote login timeout (s)
0
2147483647
5
5
remote proc trans
0
1
0
0
remote query timeout (s)
0
2147483647
0
0
resource timeout (s)
5
2147483647
10
10
scan for startup procs
0
1
1
1
set working set size
0
1
1
1
show advanced options
0
1
1
1
spin counter
1
2147483647
10000
10000
time slice (ms)
50
1000
100
100
two digit year cutoff
1753
9999
2049
2049
Unicode comparison style
0
2147483647
196609
196609
Unicode locale id
0
2147483647
1033
1033
user connections
0
32767
0
0
user options
0
4095
1064
1064
The system is a 2.40 GHz with 4 CPUs and the RAM is 2560MB. Users are complaining that they are loosing the connection established from an application to the sql box during non-peak hours i.e., at midnight roughly between 1 to 3 pm.
Any ideas whether to modify the configuration settings to increase the performance will be greatly appreciated.
Thanks
Lucky -
Are there any jobs that run during that time such as application jobs, tape backups, database backups, reindex or checkdb ?
SQL = Scarcely Qualifies as a Language
-
At the point of sounding presumptuous, I'll restate the above reply in more frank terms.
FIX
your jobs that are running during that time, like backups, reindexing, shrinking, etc!
I don't know how many consultations I have been on in the last several years where this solved everything. Schedule them correctly, even if you have to set up another database just to keep track of when things are scheduled and give that to the developers.
Studdy
and if I am wrong....the building is haunted - Happy Halloween
-
Hello Carl / Studdy05
Thanks for the replies. Yes there are some jobs running during that time and some of them have been scheduled to run as follows:
1. Transaction Log Backups of 7 databases as
Mon - Sat between 1.10 AM to 3 AM
2. Integrity Checks & Optimizations of System database tables between 1AM to 2 AM.
3. One database backup from Sun - Sat @3AM.
I am still confused why the connection is getting disconnected only between Wednesday - Friday as other days it is fine from the users point.
Thanks
Lucky -
Hello Guys,
I have changed the schedules for the jobs mentioned in my earlier thread, but still the users are complaining that they get disconnected.
Please let me know your ideas and suggestions.
Thanks in advance.
Lucky -
just a thought. do you use maintenance plans? I had a similar problem. What turned out to be a culprit was "Attempt to repair any minor problems" switch which changed the database to a single-user mode. run profiler to see what is going on on the server at the time of disconnections.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply