| Author |
Topic |
|
shaminda
Starting Member
25 Posts |
 Posted - 2004-10-04 : 17:30:28
|
| We have a clustered server configuration with windows 2000 and SQL server 2000 database. One of the servers started acting and we switched to the other sever manually. It has been acting too for the last 4 or 5 days. The SQL Server has about 40 databases and about 250 users. All client computers have visual basic programs connecting to SQL sever databases. It slows down for about 15 or 20 minutes and it picks up performance again. When the server slows down the programs timeout too. It runs fine for about an hour and slows down again. We haven’t figured out what is going on. Does anybody know what the problem is? Or is there a free tool that I can run remotely to check the problem? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-04 : 17:31:41
|
| You can run Performance Monitor to determine if you have a hardware bottleneck. Also run SQL Profiler to see what activity is going on. Make sure you don't have any jobs kicking off during this slowdown such as optimizations job.Tara |
 |
|
|
shaminda
Starting Member
25 Posts |
Posted - 2004-10-04 : 17:50:47
|
| Our network administrator looked at Performance monitor and SQL Profiler and he said he doesn't see any problems. I am a programmer so I don't know much about SQL server either. I looked at SQL profiler and I don't have a clue even what to look for. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-04 : 18:12:51
|
| ok, here come the usual questions:1. any changes to the network infrastructure recently?2. any patches to the server or client machines recently?3. Any code changes to clients or at the database (stored procedures, triggers, indexes, etc)?4. Have you performed sql profiler traces that looked for: a) long running sql b) sql that takes a lot of cpu c) sql that uses a lot of memory?5. Are there any out of the ordinary blocking or deadlocks occuring? Microsoft provides an excellent script for this in the MS knowledgebase btw.6. How are the physical drives looking? Specifically the disk queue length?Lets start with those questions for now.-ec |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-10-04 : 18:55:45
|
| What time do backups run?Do you run transaction log backups?What type of backups do you do?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
shaminda
Starting Member
25 Posts |
Posted - 2004-10-05 : 11:15:13
|
| MichaelP,We buckup our databases and transaction logs 2:00AM everyday. We do tape backups and to hard drive. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-05 : 11:28:11
|
quote:
Originally posted by shaminda
MichaelP,We buckup our databases and transaction logs 2:00AM everyday. We do tape backups and to hard drive.
do you have any answers to the questions I asked? |
|
|
|
shaminda
Starting Member
25 Posts |
Posted - 2004-10-05 : 11:51:07
|
| eyechart,Here is what I found so far,1. no2. no pathches to server. Clients windows xp SP2 on about 10 clients.3. yes4. a. b. Yes. Under Process Info one of the database CPU and memomory usage is as follows CPU -- Memory Usage 353610 -- 1935 8065 -- 25 12795 -- 635 658 -- 144 c. Yes. (see b.)5. Haven't checked yet.6. When I opened task manager it shows that the server has only 5MB of physical memory free. |
|
|
|
shaminda
Starting Member
25 Posts |
Posted - 2004-10-07 : 15:47:03
|
| The problem is fixed now but we don’t know how? We did two things1. Some of the clients had spyware and we cleaned them using Adaware.2. We archived one of the database tables that had over 1.2 million records.We don’t know which one fixed it. Does anybody know which could have caused the problem? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-10-07 : 16:05:54
|
| Probably Item 2Over time, when the data fills up again I suspect you'll have the same issue.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
SQL server running slow on and off