April 10, 2010 at 11:37 am
Hello all,
My first post on SQLservercentral, been browsing for a while though and love the forum.
I ran into a problem related to recovery of a SQL server 2008 database that I was hoping to get some help with.
This is what happened:
1. I initiated a long transaction (update of a data warehouse).
2. In mid-transaction, my computer hung and had to be rebooted.
3. When I relaunched SQL server 2008 management studio, my database appeared in the object explorer with the words (in recovery) next to it.
4. After a while, I refreshed my database list and (in recovery) no longer appeared next to my database, so I assumed that the transaction had been rolled back and my database was available.
5. I tried to run a simple select statement to see the contents of a table, but it was taking much longer than it should have to return results. I noted that my 4gb RAM was being fully utilized even though the table I was trying to access has only about 200 records in it.
6. I canceled the transaction and rebooted the computer.
7. The database was still appearing in object explorer and queries would start but not finish (I waited a few hours for results that should have taken a few seconds).
8. When I right-clicked my database and selected 'Properties', I got an error message which unfortunately I didn't write down but I think it said that the database was locked, try again later.
9. Thinking that maybe the recovery hadn't fully completed even though (in recovery) wasn't appearing next to my database, I left it alone for a few days.
10. The situation didn't change (still could query but results wasn't getting results back) so I went read about recovery problems a bit and as a result checked the error logs to see if there were any unusual messages there.
Here is what I found:
When the database went into recovery, Phase 1 of 3 seems to have completed, then phase 2 of 3 started.
Phase 2 of 3 got to the point where it was saying that recovery was 99% complete and approx 17 seconds remained
Then this error message happened:
SQL Server has encountered 1 occurence(s) of I/O requests taking longer than 15 seconds to complete on file [W:\MyDatabaseName.MDF] in database [MyDatabaseName] (17). The OS file handle is 0x0000000000000708. The offset of the latest long I/O is: 0x00003565a80000
This was followed by this error message:
SQL server has encountered 23065 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [W:\MyDatabaseName.MDF] in database [MyDatabaseName] (17). The OS file handle is 0x0000000000000708. The offset of the latest long I/O is: 0x00001d25a04000
And then this one:
AppDomain 2 (mssqlsystemresource.sys[runtime].1) is marked for unload due to memory pressure.
And then this:
AppDomain 2 (mssqlsystemresource.sys[runtime].1) unloaded.
After that there's a bunch of messages telling me that SQL is setting database option compatibility level to 100 for the ReportServer and ReportServerTempDB databases.
11. When I discovered these error messages, I tried to take the database offline and online again using SSMS. However the take database offline dialogue box seems to be stuck now, it says 1 remaining, status in progress and its been like that for a couple of hours.
So a few questions:
1. Am I right to think that the recovery process isn't complete since the log didn't record a 'Recovery is complete' message?
2. Any ideas on what went wrong?
3. Any ideas on what I should do next to get my database up and running again, rolled back to where it was before the transaction it was processing when the computer hung?
April 10, 2010 at 11:54 am
try to shut down the database using command [not using UI].
try using detach and attach [Not much sure about problems if any]
April 10, 2010 at 3:42 pm
I tried to run the following code to take the database offline instead of using the UI:
ALTER DATABASE [MyDatabaseName]
SET OFFLINE WITH ROLLBACK IMMEDIATE
Now it says 'executing query' just below the results pane and in the log file I have 2 log entries that say 'Setting database option OFFLINE to ON for database [MyDatabaseName]. One of these entries is from when I tried to set the database offline using the UI and the other is from the code I just ran.
April 11, 2010 at 3:03 pm
Looks like my first hunch was correct! Database was recovering - just checked logs and before the take offline processed (which took ages because the DB was waiting to finish recovering), the database recovery completed. I took it back online and can use it now - mystery solved.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply