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 |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2007-07-31 : 11:17:18
|
| Here's what happened.SQL Enterprise 2000, sp3, active/active cluster.SQL server db, close 200 G in size, started giving problems in one table where it not even run the select top 10 * would not work. Then it did not even let us create any table even with a single column and single row.We thought there were hanging transactions that were causing them to grow/increase the size of the ldf which was set to grow by 10% instead of a fixed size which is probably recommended.So we rebooted the server assuming they would be released. But it was in the same state??Microsoft consultant after looking at errorlog file and trying various things had us bring the db in single user mode and back to multi. That problem finally went away. I can't understant it was the single user mode that did the trick?Shouldn't reboot solve the problem?The ERRORlog file showed latching type 2 errors.DBCC did not show any problems?Any ideas? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-31 : 23:12:39
|
| Maybe have other open transactions? Sql will do rollback/rollforward after restarting sql, that takes a while if had long running open transactions. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-08-01 : 00:46:23
|
| never reboot a sql server like that. You could have a long running transaction that would have to be rolled back. On startup, the system could be in crash recovery for a really long time.first thing is that you are downlevel on your service pack. I would be running SP4 w/hotfix rollup 2187 applied.did you run sp_who2 while you were having this problem? it would have been helpful to run this in another session while your SELECT query was hung. that would have told you a little bit about what was going on.-ec |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2007-08-02 : 17:40:22
|
| Commenting of what eyechart said regarding SP4.We've just find out yesterday that the SP3a was no longer support since last month unless you have "Extended Warranty" but it doesn't mean they are not going to help but Ms will push to upgrade to at least SP4 roll up.I saw from MSDN blog that Ms will do the same for SP4 next year on April 2008. |
|
|
|
|
|
|
SQL server problems