Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Slive
Starting Member
8 Posts |
 Posted - 2012-05-09 : 07:48:07
|
| Hi, I've been having issues with mssql since Monday. The client is running Win2008 Small business server 16GB of RAM and an Intel Xeon 2.4Ghz CPU 2TB drive with 1.6TB free. (we recently upgraded their server/system) I'm not proficient with MSSQL but I know the basics which I need to set up the softwrae we use. Here's what happened. On Monday morning a certain DB randomly went into 'Suspect Mode' and caused the SQL service to restart intermittently. All the users bomb and I had to manually restart the service. Then in the afternoon it went into 'Recovery mode' I checked the error logs and most of the errors suggested that it's either an I/O error or something to do with the sa logon (it was still stopping the service randomly). Lastly it went into Single user mode and then the service was fine and everyone could connect for the rest of the day. Note: The users were logging onto a different database to the one that was giving issues as we have two ERP applications that use different DB's. I took the DB offline after hours and then detached it. I reattached the DB and it seemed fine but just to make sure I ran a DBCC CheckDB against it and it returned no errors. Then I ran a an integration for the application that connects to that DB and it worked fine. Next morning, same problem all over again. It was intermittent and the longest they could trade uninterrupted was about 2 hours. Again the main errors I saw were I/O and if I browse the event viewer it was sharepoint and msexchange errors(both of which they don't use). So last night I ran all the windows updates (which took the whole night) and this mornig came in restarted the server and reattached the DB again. It was working 100% again until I got more weird errors. One of the errors I get is "tempdb log file unavailable" so I restarted the service again which seemed to help but not for long. The templog file existed in the correct directory and I double checked it with a script. After that error stopped the DB went into Recovery mode AGAIN. I can post the log files if you need them. I don't know what else I can do to troubleshoot this? I've been thinking of a DB restore after it happened again today but the latest backup is this morning and I'm worried it's corrupt or - even worse - a waste of time because the DB is actually fine. Anything else that could be causing this? Help will be greatly appreciated. Kind Regards, Rogan. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-09 : 09:29:31
|
| Have you run any disk diagnostics? My guess is you have a bad disk(s) or bad disk controller. Get that diagnosed first.In the meantime, make sure you have a regular backup job scheduled for all of your databases, including master and msdb. DO NOT DETACH a suspect database if it occurs again, you could lose it entirely. See here for more details:http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx |
 |
|
|
Slive
Starting Member
8 Posts |
Posted - 2012-05-09 : 09:44:49
|
| Thanks robvolk. I'll run the diagnostics and let you know if that helps. I've got backups running every morning at 2:00AM. One more thing, I found this Error in event viewer right after I posted this topic. "The driver detected a controller error on \Device\Ide\IdePort0." Funny thing is these errors only started popping up today. Most probably a disk error then. Thanks a ton Rob. Kind Regards,Rogan |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-09 : 10:14:28
|
quote:
Funny thing is these errors only started popping up today.
I wouldn't count on that. Databases don't go suspect for no reason, and it's usually an I/O problem that causes it. You should be prepared to replace your disks and controller, or move your SQL Server to another machine if they can't be fixed. |
|
|
|
Slive
Starting Member
8 Posts |
Posted - 2012-05-09 : 11:07:15
|
| Ouch. I'm going to run diagnostics now. First going to change the cable/sata port for the disk. We have a backup server that's about half the power of the current server that we can switch the SQL to. I'll keep you posted. Thanks again. |
|
|
|
|
|
|
Annoying Recovery Mode