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 |
|
dave2118
Starting Member
8 Posts |
 Posted - 2007-11-14 : 15:19:40
|
| Hi, I'm unsure what happened, but I'm using SQL Server 05 Developer Edition. I've been working with a local database where I tried to add a clustered index on a huge table (about 50M records). Well, I had to stop the query since it was maxing out my work computer. Since then, I've been unable to open the database or run queries against it. When I open SQL Server Management Studio the database says In Recovery. Once it's done, when I try to expand tables, I eventually get this error: TITLE: Microsoft SQL Server Management Studio------------------------------Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476------------------------------ADDITIONAL INFORMATION:Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476------------------------------BUTTONS:OK------------------------------ If I run a Select * from INFORMATION_SCHEMA.TABLES query, it will go on for a while If I try to access the properties of the Database (right clicking), I get this error: TITLE: Microsoft SQL Server Management Studio------------------------------Cannot show requested dialog.------------------------------ADDITIONAL INFORMATION:Cannot show requested dialog. (SqlMgmt)------------------------------An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476------------------------------BUTTONS:OK------------------------------I'm unsure where to go from this point forward. |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-11-14 : 15:24:35
|
| Did you do a reboot of the SQL Server. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-14 : 16:01:28
|
| You will have to wait until the rollback completes before you will be able to access the database.Since your transaction involved millions of rows, that can take a long time, as you have already found out.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-14 : 16:08:56
|
quote:
Originally posted by TRACEYSQL
Did you do a reboot of the SQL Server.
It'll still have to rollback the transaction at startup time. Might as well let it rollback without a reboot to ensure you don't mess things up further.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-14 : 16:24:21
|
| Yep, had a similar issue on a Prod server back in 2000. It was SQL 6.5. I was told to reboot the server though (I hadn't stopped sql, only tried to kill the connection causing the issue). I rebooted and it took over 44hrs to recover...which meant over 44hrs for sql to start back up. It had to rollback that transaction. I could have bypassed recovery mode but was told not to do that by Microsoft and management since the db could be left with corrupt data. Company was down for 2days as this was the only db used by the company for everything. 300-400 employees sitting at their desks getting paid to do nothing. Issue wound up being a corrupt index on the largest table in the system. I had known about corrupt index pages in that index causing 2610 errors for over 3 months. Why didn't I come in on a weekend and rebuild that index? Because the CIO woudn't let me. I had went to him every week saying I needed to rebuild that index before something bad happened...he kept saying no. He was also the one that instructed me to reboot the server during the issue. After all of that, I was allowed to come in the following weekend and rebuild the index. lolAs a matter of fact, I ran a checkdb after the index rebuild was done that Saturday night. It was still running Sunday night at 11pm so I went in the see what I could do (they didn't want it to be running at 6am on Monday when people started coming in to work). All you can do in SQL 6.5 to stop a checkdb is stop and start sql and I was terrified to do that. So I called Microsoft back. I sent them my error logs and they saw one with the 2610 error from a couple weeks earlier and told me that was a bad bad error and needed to be fixed immediatly and to shut the company down if I had to to get it fixed but that it had to be fixed asap. I told them I had already fixed it and that yea it can cause substantial issues as we had just experienced a few days earlier. |
|
|
|
dave2118
Starting Member
8 Posts |
Posted - 2007-11-14 : 19:43:49
|
| Yeah, even after the rollback I would get those errors. I waited a couple of hours after the rollback, and still had the errors above.I've rebooted, restarted the service, same thing. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-14 : 22:51:24
|
| Tried check the table with 'dbcc checktable'? |
|
|
|
dave2118
Starting Member
8 Posts |
Posted - 2007-11-15 : 07:31:11
|
| Yeah, didn't work. I'm going to have to blow this away. Other databases work. |
|
|
|
|
|
|
|
|
Database Crashed?