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 |
|
WindChaser
Posting Yak Master
225 Posts |
 Posted - 2007-01-12 : 17:50:55
|
| Hi there!I need to execute: ALTER DATABASE MyDB SET SINGLE_USER which always returns Timeout Expired.I can't execute dbcc checktable (MyTable, REPAIR_ALLOW_DATA_LOSS) until the database is in single mode. How else can I set single-mode or can I bypass that requirement?Thanks!Mike |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2007-01-12 : 17:55:30
|
| First a question - why do you need to run repair? Don't you have a backup you can restore from? What's the error you're trying to repair?Try ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATEYou may have a long-running query that's going to take a long time to rollback - in which case if your query timeout is set to something like 30 seconds, the ALTER DATABASE may timeout before the rollback can complete. In that case try setting the query timeout to infinite for the connection doing the ALTER DATABASE.ThanksPaul RandalPrincipal Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)http://blogs.msdn.com/sqlserverstorageengine/default.aspx |
 |
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2007-01-12 : 20:13:52
|
| It's the database of someone who asked me to look at it to see if I can do something. It looks like the corruption dates to 2005 but they've been unaware about it until now because everything worked fine otherwise. That's why I figure that the damage can't be that bad... but we'll see. I tried setting SET LOCK_TIMEOUT -1 but I get the same timeout. I'm probably not doing this right. And I tried to run with the With RollBack Immediate condition but I get this error: Incorrect syntax near 'WithRollBack' meaning that the space between With and RollBack is not being recognized. In fact, I haven't been able to add any conditions to test, including ALL_ERRORMSGS .Thanks Paul. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2007-01-13 : 15:13:17
|
| I respectfully suggest your friend calls Product Support to help them with this problem, or tries to restore from their backups.ThanksPaul RandalPrincipal Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)http://blogs.msdn.com/sqlserverstorageengine/default.aspx |
|
|
|
|
|
|
Trying to correct corrupt table