| Author |
Topic |
|
whgt
Starting Member
6 Posts |
 Posted - 2009-12-15 : 12:15:26
|
| BACKGROUND:We recently migrated from an 8 year old server to a new HP ProLiant G6 server. With this we brought our domain, users, a (fresh) install of Win 2k3 Small Biz, and our existing SQL databases.We also installed a new gigabit switch (running over Cat 5e).The only role our server functions is for the 2 SQL databases (Lookup and Main) with an average 8 users connected most of the day (max is 11 usually) and it has a 15 user SQL license. It also acts as a very light file server (as in hardly ever accessed).PROBLEM:Every approx. 24 hours all clients simultaneously lose SQL connections. I believe it is only the MAIN database and not the lookup database that we are losing access to. This requires a full server restart to remedy.Secondly, the server takes at the very least 20 minutes to reboot, but usually 30 minutes. This should not be taking so long on a new state of the art server!STEPS SO FAR:I backed up and rebuilt the SQL databases this weekend. The problem with disconnects still exists. This PLUS the long reboot times leads me to believe it is an OS or hardware issue.The only things I can think to do right now is to backup and nuke the server and start fresh. And if that doesn't work, it's hardware.SUGGESTIONS PLEASE...? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-15 : 14:57:17
|
| Should not be rebooting so often. The long reboot time is for the databases to recover after you shut it down. It's not a hardware issue.Check to make sure nothing is taking exclusive use of the database every night...perhaps a job that sets it to single user mode, or a nightly DBCC Checkdb.Make sure domain controller isn't rebooting as well. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-15 : 14:58:44
|
| Also, your statement about backing up the db over the weekend makes me think you aren't taking regularly scheduled backups. If that's true, make it your #1 priority to start doing so. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
whgt
Starting Member
6 Posts |
Posted - 2009-12-15 : 22:45:41
|
| Agreed on the rebooting. But it is the only thing that fixes the issue.I will double check on the backing up issue. And the backup over the weekend was so that I could do an immediate rebuild right then.Also, our backup solution is to a NAS onsite which replicates to off-site. In addition, it isn't just an overnight problem...it can happen mid-day also. It's not like the backup runs overnight (which it does) and then first thing in the morning it isn't working. It happens every day at all times. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-16 : 07:24:31
|
| check the nic. any switches server is attached to. domain controller. did u check the things Tara said to look at yet? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-16 : 08:09:10
|
You said:quote:
Every approx. 24 hours all clients simultaneously lose SQL connections. I believe it is only the MAIN database and not the lookup database that we are losing access to. This requires a full server restart to remedy.
Can you still access the db through management studio? If so have you checked that there is not some crazy process blocking everything?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
whgt
Starting Member
6 Posts |
Posted - 2009-12-16 : 13:15:58
|
quote:
Originally posted by Transact Charlie
You said:quote:
Every approx. 24 hours all clients simultaneously lose SQL connections. I believe it is only the MAIN database and not the lookup database that we are losing access to. This requires a full server restart to remedy.
Can you still access the db through management studio? If so have you checked that there is not some crazy process blocking everything?
Can't try this until it goes down again. Here's the error the clients give:cbfSet_Recent_accessError (3157) ODBC--update on a linked table 'tblMain' failed.Does this give any clues? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-17 : 04:14:53
|
| could just be a process holding a lock on that table then.Your table is called: tblMain? Not very descriptive.Does your application read from and write to this table a lot?any bulk processes that target this table?I'm afraid that I don't have a lot of experience debugging such issues on sql server 2000. If this was 2005 I'd recommend changing the Isolation Settings (Cheers Tara) and could recommend running a server side trace or even just profiler on the database.Sorry -- I don't think I'm going to be much more help.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
whgt
Starting Member
6 Posts |
Posted - 2009-12-17 : 12:04:25
|
| Thanks for all of the tips people. I ended up pulling a 12 hour work day yesterday and did a full backup and restore of the two databases. After the rebuilding the server reboot cycle is only 5 minutes flat (opposed to 30 before).Fingers are crossed for the next 48 hours that there are no more hiccups. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-17 : 12:12:31
|
| what did you rebuild? |
|
|
|
whgt
Starting Member
6 Posts |
Posted - 2009-12-17 : 14:34:38
|
quote:
Originally posted by russell
what did you rebuild?
Just the tables...a normal full backup then restore. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-17 : 15:10:13
|
| simply backing up the databases and restoring them isn't going to solve anything (and isn't rebuilding anything).and sure a reboot right after ought to be faster because all the transactions were rolled forward during the restore.you need to look at why network connectivity keeps breaking -- if indeed it is.also, stop rebooting when it happens. at least try other things 1st.so you're using access as a front end, and allowing people to make updates that way? this is a bad idea in and of itself. when they open the table, they are doing a select * from it.seems you probably have blocking going on.next time it happens, use management studio and see if you can connect. then execute sp_who2 to see what's blocking |
|
|
|
whgt
Starting Member
6 Posts |
Posted - 2009-12-17 : 15:35:31
|
| Thanks. Hopefully there won't be a next time. It's actually real estate software that uses a special install of Access with its own front-end. People don't access it directly from Access.If it doesn't go down in the next day then chances are...it is fixed.FIXED for 5 days now. The outlook is great. |
|
|
|
|
Clients lose connection every ~24 hours