SQL Server Automatic Recovery

Question

SQL Server Automatic Recovery

GregLyon

SSCommitted

Points: 1946
More actions
April 27, 2004 at 2:51 pm

#85068
I had quite a scare today. This is mostly a story of the events, but there is a question at the end if you want to skip the story part.
The story:
I'm running SQL server 7.0 developer edition on my desktop. It's been quite busy lately running some huge queries that I wouldn't dare run in a production environment.

Anyway, after cancelling a very long running query I decided to stop and restart MSSQL and MSDTC. After doing so I couldn't refresh and reconnect in ISQL. I tried Enterprise manager to see what was up, and got this message:
```
(Connection failed, Check SQL Server Registration Properties)
```
That looked bad, so I rebooted the PC. When I opened ISQLW again I get a Connection timeout. So I try stopping and restarting the MSSQLServer and MSDTC services again which appears to work fine except I still can't connect to my sqlserver.
Finally it crossed my mind to have a look at event manager. In the application log there were a whole bunch of 'information' MSSQLServer entries for event ID 17055. The details read:
3450 :
Recovery progress on database 'Foo' (7): 3 percent.
That looked bad at first until I realized that each time I stopped and restarted SQL Server it had to start the recovery process all over again. So I finally waited about an hour and my 23 GB database was finally operational again...Whew! THE END.
P.S. I included the story so the next person frantically searching the web or this site will at least get this page. I was flabbergasted at how few entries there were out there for these error numbers/text.
So, finally, my question. Is this something that I should be alarmed about? Is it an indicator of worse problems about to happen? Or is it pretty normal for SQL Server to have to recover? I've been running this database pretty continually for the past year and a half, I just looked through the log and there have been a few recoveries but they all ran in under a minute, so I never noticed before.
I don't know if it's related, but in the past couple days I've written queries that resulted in SQL Server terminating processes with fatal errors too.
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mssql_rules SSCrazy Points: 2783 More actions · Answer 1

Without actually understanding the nature of transactions and applications, it is hard to postulate what was precisely going. Any way, couple of things to look into, "recovery interval" configuration option, that controls how long it takes to recover the database. I would not tweak with that option without understanding the plus/minus points of changes. There may be runaway transactions too.

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 2

Every time sqlserver starts (or starts a db) it will check if data is consistent.

If it detects there were running transactions at the previous stop, it will rollback those transactions so all data is consistent ! The db will not be available until it is back in a consistent state.

It reports such a recoveries state with messages like :

Recovery progress on database 'Foo' (7): 3 percent

When you would have taken a look at the point when you canceled your query, you might have seen that spid in rollback-state.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

GregLyon SSCommitted Points: 1946 More actions · Answer 3

Thanks, both of you. It makes sense now. I was running a huge query that was in the middle of inserting 2 million rows of data. It was probably nearly finished when I rebooted, which meant it had to go back and clean out those records...

I suppose this is one of those things SQL Server does that goes unnoticed until it happens in such a way that it takes a long time to recover.

mssql_rules SSCrazy Points: 2783 More actions · Answer 4

This is one more reason why you would want to keep your transactions manageable (ie small). So the server does not have to do mammoth undoing when restarted in the middle of the transaction.