| Author |
Topic |
|
jeff.chastain@hp.com
Starting Member
5 Posts |
 Posted - 2005-12-15 : 10:14:12
|
| I have a SQL 2000 database whose MDF is approx. 105 GB. Everything has been working fine for months until this past weekend when the database server dropped offline. I restarted the services and SQL server looks to have started up okay. However, trying to access it via Enterprise Manager causes EM to hang. Checking the Windows event log, there are a whole series of messages stating ...3455 : Analysis of database 'ptportal' (7) is XX% complete (approximately 61 more seconds)This 'analysis' ran for over 12 hours ... I have never seen this run before, much less this long. Now, there are a new series of messages stating ...3450 : Recovery of database 'ptportal' (7) is XX% complete (approximately 56526 more seconds) (Phase 2 of 3).This 'recovery' is still running. Does anybody more knowledgeable have any idea what has happened here or what is going on? My guess is that this database has been corrupted and SQL Server is trying to do a repair? Anybody know how long this usually takes and what the success liklihood would be?Thanks-- Jeff Chastain |
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-15 : 10:54:32
|
| Well, I can answer some of this. Recovery is merely rolling transactions forward and back, based on whether or not they committed before the failure event. This happens each time SQL Server starts. Normally, this doesn't take very long (i.e. 5 minutes or so). If there is a very large transaction that was running during the failure, it will take awhile to roll it back, which could lead to a situation like this. SQL Server may be corrupted, but then again it may not. I've seen EM and even QA hang like this, because it's trying to enumerate databases that are in recovery mode. You might try looking at SQL Server with OSQL or with SQLCMD (2005). This will tell you if others are able to get in. Also you should be able to do a sp_who2 and see the recovery process and whether or not the DISKIO or CPU counters are increasing. If they are I would say let it run. If they are not, you might have a hung recovery process, which I've never actually seen before.HTHJon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
 |
|
|
jeff.chastain@hp.com
Starting Member
5 Posts |
Posted - 2005-12-15 : 11:17:22
|
| Trying to run osql is not working any better. After waiting several seconds after trying to connect, I am getting back a 'General network error'.I am continuing to get messages in the event log, so it looks like it is doing something. It just makes me wonder when it has been recovering this database since about 5 this morning ... 5+ hours ago and we are still sitting at 2% complete.Thanks-- Jeff Chastain |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-12-15 : 13:05:04
|
| How is the box itself doing? Is it buried under I/O requests? Processor pegged to 100%?It sounds like you had a large transaction running when this happened, or for some reason, the recovery interval was set so high it kept all the day's changes in memory.I would not get overly excited about the 2% complete estimate. This does not translate directly into %time to completion. It is an estimate, and usually a worst case one. |
|
|
|
jeff.chastain@hp.com
Starting Member
5 Posts |
Posted - 2005-12-15 : 13:28:56
|
| The box itself would appear to be fine. sqlservr.exe is using 25% of the CPU and about 3/4G of memory. There is no network traffic on this box as it is only a database server and the application it supports is offline because this database is down.My main concern is that this app had already been down for a couple of days because of this and now that it is still sitting at 2% for the last 7 hours, I am wondering how many days this is going to take.Thanks-- Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-15 : 14:35:01
|
| Your userid indicates that you work for HP. If this is the case, I'd highly suggest that you contact MS directly as I'm positive that you have a support contract in place with them. If that is not the case, post your question in the Data Corruption forum here. Paul Randall from MS and author of DBCC frequents that forum as well as some of the other people on his team.I'd start restoring your database from the last known good full backup in case you are hosed. You could then apply the tlogs up to the point of failure. This might faster than waiting for SQL Server to recover. But you might lose data depending upon how often you backup the tlogs. You should at least get started on this that way if SQL Server doesn't come up sucessfully, you have at least started plan B.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-15 : 14:55:26
|
| "I'd start restoring your database ... in case you are hosed."Good plan Tara, stored for future reference - and hoping it will never be needed!(Presume you meant to restore it into a "new" database, which could then be re-named to the "original" name if Jeff has to give up on the "recovery" his DB is busy doing)Kristen |
|
|
|
jeff.chastain@hp.com
Starting Member
5 Posts |
Posted - 2005-12-15 : 14:59:30
|
| >> I'd highly suggest that you contact MS directly as I'm positive that you have a support contract in place with themYou would thing that, wouldn't you. Unfortunately, this is not the case. I will repost this in the forum you mentioned and see if I can find some more help there.Thanks-- Jeff Chastain |
|
|
|
|
Corrupted Database?