| Author |
Topic |
|
dineshsatam
Starting Member
19 Posts |
 Posted - 2007-09-14 : 14:49:28
|
| My one VB Exe used to connect SQL 2000 using windows SQL Server Driver. But same exe is giving problem of SQL Connection Timed out error in SQL 2005 - SP1.As this exe refers 5 tables from database and insert as well as update the data. Also client pc and server pc's ping test is around 30 to 40 ms.Is there any way where we can increase timed out level at SQL 2005.One more thing which i noticed in 2005. its really very heavy software. even my IBM Xeon_346 server 3GB Dual CPU, 3 GB RAM also not able to handle.Thanks in AdvanceDinoDina Satam |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-14 : 14:55:33
|
| Rather than increase a timeout value, why not see if your queries and schema can be tuned?How big is your database? What kind of performance problems are you seeing?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
dineshsatam
Starting Member
19 Posts |
Posted - 2007-09-14 : 15:07:40
|
| My Database is too much big. Its having every days 41 GB of Full database dump.almost 79000 txn inserts in one of the table and we keep last six months data.Its really heavyRegardsDineshDina Satam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-14 : 15:16:02
|
| 41GB is not a large database and neither is 79,000 inserts in one table, regardless if that is per day.You are really low on RAM though. 3GB is not nearly enough for a 41GB database. Have you looked at the SQL Server memory performance counters in Performance Monitor to see if you should add more memory?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
dineshsatam
Starting Member
19 Posts |
Posted - 2007-09-14 : 15:22:50
|
| Its 79000 per day.and SQL Server starts crying. I have to monitor this server the way I breathsDina Satam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-14 : 15:29:40
|
| 79,000 inserts into one table in one day is not a lot. That's only 55 rows per second. SQL Server can definitely keep up with that. You've probably got some database architecture problems as well as a lot of poor performing queries.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-09-14 : 16:22:02
|
| Did you upgrade the database to SQL Server 2005 computability mode and rebuild all the indexes when you converted it to SQL Server 2005? That made a big difference in my environment. As Tara says, 41GB isn't that big.Is it the login that is timing out? How busy is the server when this happens?=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
Kristen
Test
22859 Posts |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-09-14 : 22:25:06
|
| Um... I'm confused... OP said his database worked fine when it was in SQL Server 2000... it didn't start performing badly until he converted the database to SQL Server 2005. What in the conversion to 2k5 would do that?--Jeff Moden |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-14 : 22:26:53
|
| And ensure there is no blocking on the server. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-14 : 22:45:12
|
| "What in the conversion to 2k5 would do that?"Leaving the compatibility mode at the (default after a conversion) of 80Failing to rebuild all indexes, which in turn updates the stats to something that the query optimiser can use more readily.Plus there may be issues with queries - I think in particular for JOINs that require an implicit Cast because the datatypes being compared are not identical. (I think this is no different to the change that was made in SP4 for SQL2000, so may well not be a "new" issue).There could be some other, similar, incompatibility issues with SQL 2005 of course, although I have not seen many reported that are actually "hurting"Kristen |
|
|
|
dineshsatam
Starting Member
19 Posts |
Posted - 2007-09-15 : 10:16:44
|
| Hey The problem in SQL 2005 is because of Job Overlapping. As we faces this problem on SAT and SUn Morning 5.30 am to 8.30 am as we noticed Index Rebuid job overlap on Shrinkfile log job which stop my external C+ Exe to insert a data in tables. Also We migrated from 2K to 2K5 as our application also change and it supports 2k5 with SP1. Now the problem which i am facing is1) one of Client side VB Exe is giving SQL Timed out error.2) Software Background: As this is Fraud Marking Utility in Card Industry. having back end as SQL 2k5 Now after migrations users are saying that they are getting less alerts in particularly 3 rules. Rules are the stored procedure which updates the record level in a table so that frontend shows that Txn as a alert. Now we had 3 procedures and this procedures are run every night [Using JOB]. Now before migration this procedures are able to generates alerts around 1000 [It was SQL 2000]and now same procedures are generates the alerts only 500{this is with 2k5 with SP1}. we checked both the procedures also compatibility level we have kept as 80 so that 2k procedure also dont have a problem in 2k5.If you need any procedures then let me know I will give u for your referance[both procedures 2k and 2k5]Dina Satam |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-09-15 : 12:29:38
|
| Did you upgrade the existing server? Is this a new server? If it's a new server is the hardware the same or better?=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-15 : 12:38:02
|
You have a job to SHRINK your log file?Why on earth would you like to do that?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-16 : 03:13:55
|
"compatibility level we have kept as 80 so that 2k procedure also dont have a problem in 2k5"I think this is a false assumption.SQL2k5 is new, compared to SQL2k, and therefore there is no guarantee that there won't be inconsistencies with your code anyway (although very unlikely I agree)There is published evidence that Compatibility mode 80 WILL be slower than changing to native mode for SQL2005 (i.e. mode = 90)If you have some particular procedures that are called frequently (you mention on which generates alerts) it would be worth checking what the query plan is for that Sproc, and perhaps comparing it to a query plan from SQL 2000. Also worth checking if any of you join comparisons have implicit data conversion. I think you should check that you have identical datatypes - so if you are comparing a smallint and an int, for example, I would recommend that you put an explicit cast in (or change the datatypes so they match  )I do wish there was a LINT type utility that could Warn about these sorts of "hidden" issues ...Kristen |
|
|
|
|
SQL 2005 Screwed my Life