| Author |
Topic |
|
mtl777
Yak Posting Veteran
63 Posts |
 Posted - 2012-09-13 : 13:26:54
|
| Hey guys, I've been pulling my hair out on this one and badly need your help. Our server containing our SS 2000 database crashed. We took the opportunity to upgrade our SS 2000 db to SS 2005 and migrate it to a new server. I restored the SS 2000 db full backup file to SS 2005. The restore was successful. But when users login through our app, or even if I just login through SSMS, it takes long, abnormally long. And when I'm able to login to SSMS, if I even just right-click on a table to view its properties, it also takes long before the menu where I can select Properties shows. And then when I select Properties that takes long again.The server's OS is Windows Server 2008 Standard. I have updated it to SP2, and also updated SS 2005 to SP4, all to no avail. I have also tried unchecking "Check for server certificate revocation" in IE Tools > Internet Options > Advanced, as suggested in some sites I read, but it didn't fix the problem.Oh, and one more thing. I also couldn't create maintenance plans. When I right-click on Maintenance Plans to attempt to create one, after a long wait it gives me an "OLE DB error 0x80004005 (Unable to complete login process due to delay in opening server connection) occurred while enumerating packages. A SQL statement was issued and failed."Any idea what's going on? I hope one of you has encountered this problem and already knows the solution.BTW, how do I change the login timeout of SQL Server?Your help would be so very appreciated. Thanks in advance! |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-13 : 13:32:59
|
| Did you Rebuild indexes/update stats? Also, you should run perfmon and see were your bottlneck is. -Chad |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-09-13 : 13:34:30
|
| You need to diagnose where the problem is via Performance Monitor and Profiler. You need to see where the bottleneck is. Is it disks, CPU, memory? Is it long-running queries or out-of-date stats? Could be lots of things. You need to diagnose where the problem is before proceeding further.Did you update stats with fullscan on all tables after the upgrade? That is definitely recommended.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-09-13 : 15:31:24
|
| Thanks for your replies!@Chadmat: Does SS 2005 have different indexing than 2000? Why the need to rebuild indexes? They were already rebuilt in 2000 shortly before the full backup that was restored in 2005.@tkizer: I'll try the update stats with fullscan, as well as the other stuff you mentioned. Thanks for the tip! |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-13 : 16:28:15
|
| It was more about updating stats, but if you rebuild the indexes, you get the update stats along with it, so I asked if you did either.-Chad |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-09-13 : 18:42:07
|
| In the SQL Server Configuration Manager, can I disable the SQL Server Browser service? What does the SQL Server Browser service affect? I'm trying to disable services that are not being used in our system or applications.Another thing I'm looking at is the Named Pipes protocol in SQL Server Configuration Manager. What is that for? Can I disable that too?Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Rimsky
Starting Member
12 Posts |
Posted - 2012-09-14 : 02:54:27
|
| Hi,Just another road to explore: Whenever you restore a database to a different path or server, your logins might get orphaned. For the user experiencing problems, try this on the master database: exec sp_change_users_login 'Auto_Fix', '<username>' |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-09-18 : 23:14:09
|
| I have run the Profiler. The CPU and Memory are fine. It's the Network and the Disk that seem to be maxing out, more so the Network. The strange thing is that the slowness causing the lock timeout error still happens even late at night when no one else is using the database. Is it an issue with the NIC? Or maybe there are BIOS settings that might be incompatible or inappropriate?I would also like to add that I have already done the Update Statistics With FullScan for all tables except a few ones that are not being used anymore. Database queries have become faster, though some users are still having a login timeout when logging in through our VB app. But once logged in, the database queries from the VB app run fast (at least compared to before when we had that old, slow server). So it seems to be just an issue when logging in, or anytime that a login is being checked, such as when you open a stored procedure or try to get the properties of a table in SSMS.I forgot to mention that the new server was hastily bought and built, with only a 2TB, 7200 rpm Western Digital SATA drive and no RAID for the database, and an SSD for the OS (Windows Server 2008 in 32-bit mode). The CPU is a single 3.2GHz Intel Core I7 3930K (6 cores, 12 threads). The memory is 16GB DDR3, but because we installed the OS in 32-bit mode, only 4GB of that is usable as far as I know. Just putting these specs out in case they're relevant to your analysis of the problem.I'm not sure where to go from here. Any ideas to solve this problem?Thanks! |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-19 : 18:21:07
|
| You really should reinstall in 64 bit, but if that is not possible, you can still use the memory for buffer pool. Just turn on AWE. There are so many other problems that can crop up in 3r bit though, I wouldn't recommend staying on 32 bit.-Chad |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-09-19 : 20:40:34
|
quote:
Originally posted by chadmat
You really should reinstall in 64 bit, but if that is not possible, you can still use the memory for buffer pool. Just turn on AWE. There are so many other problems that can crop up in 3r bit though, I wouldn't recommend staying on 32 bit.
We installed 32 bit because with our database coming from SS 2000 which is 32 bit, we thought we might have issues with 64 bit. Is SS 2005 fully compatible with 64 bit OS?What is AWE and how do I turn it on?If I choose to change to 64 bit, can I do an in-place upgrade of the OS and SS 2005 to 64 bit so that I won't have to reformat the hard drive and reinstall the OS and apps?BTW, when we were on SS 2000 (32 bit), we did not have this problem. So why should we now need to upgrade to 64 bit to fix the problem? Does SS 2005 have problems when running in 32 bit OS?Thanks! |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-20 : 14:48:10
|
| AWE is a config option (sp_configure, or server properties->memory tab).Not sure about in place upgrade, I would guess not. I have no idea if that is the cause of the problem that you are talking about, I just think it is a really bad idea to build a 32 bit SQL server in this day and age.-Chad |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-09-24 : 15:03:55
|
| Thanks Chad. I tried setting AWE to ON. Still the same thing. Upgrading to 64 bit may not be possible at this point due to the amount of work and downtime involved. Any other ideas? |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-09-28 : 16:08:29
|
| Update: If I go to the server machine itself and open SSMS directly at the server, I do not have the slowdown issue. It is only when I open SSMS at a workstation that I'm having this issue. So I am now almost certain that this problem is related to something in the network configuration or hardware. Does anybody have a similar experience with this? This is so frustrating and I would really appreciate any suggestions maybe on the networking aspect. Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-10-02 : 15:25:36
|
quote:
Originally posted by tkizer
http://blogs.msdn.com/b/euanga/archive/2006/07/11/662053.aspxTara Kizer
Thanks Tara! I tried the suggestions in the link you gave and unfortunately it didn't fix the problem. Actually I had already tried them before I started this thread, and I tried again last night but no dice. What could it possibly be? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Migration issue - slow login, slow SSMS response