Database In Recovery

Hello all,

Is there a way to check the status of a database in recovery. Like how far along it may be. If not is there a way to stop a database in recovery and just drop it.

Thanks in advance,

Mike

There should be progress messages in the errorlog indicating percent complete and how long it is expected to continue?

What version of SQL are you running?

|||

Sorry im running SQL SERVER 2005 Standard Edition.

And you were correct it is in there. It looks like im looking at 9 hours to recover. Is there a way to stop the recovery? I can drop the database and reload the tables faster than that.

Thanks again,

Mike

|||

The only way to prevent recovery is to stop the server and delete or rename one of the database files. Then you can drop it after it comes up recovery pending on the server restart.

What phase of recovery is the database in? Is there just an enormous transaction to rollback?

|||

The recovery is in phase 2. The trans logs are about 58GB. I think i will do what you said that way I can just drop and create.

Thank You,

Mike

|||

Having same problem with database being in recovery. Has been running for a number of hours.

Below is message that repeats frequently in log file. However, I don't really know how to interpret it.

Process 16:0:0 (0xb78) Worker 0x03D880E8 appears to be non-yielding on Scheduler 1. Thread creation time: 12831081295625. Approx Thread CPU Used: kernel 265 ms, user 76778796 ms. Process Utilization 49%. System Idle 49%. Interval: 76832057 ms.

Any advise would be appreciated.

Database design for status field

Hello,

I have a database design question. I want to store a status in a table. In the ASP.NET interface for the user this status will be adapted to the language of the user.

This is how it would look like:

StatusID 1 = "yes" for English, "oui" for French, "ja" for Dutch
StatusID 2 = "no" for English, "non" for French, "neen" for Dutch
StatusID 3 = "error" for English, "erreur" for French, "fout" for Dutch

I don't want to do the translation in ASP.NET and it should be done in one query.

How would it look like in the database design.

WimVM, how about this,

if you are asking about how to design a table or tables, you could have one table (status) like this:

statusID is you primary key and three additional fields (english, french, dutch)

so for instance if you have an english speaker your "yes" would be returned by

declare @.statusintset status = (getYourIdFromApp)select english from status where statusId = @.status

hope this is helpful -- jp|||

Hello,

Thanks for your reply. This would indeed do it, nice and easy. What I forgot to say was that the language is set by an ID, LanguageID. This holds the value 1 for English, 2 for Dutch, 3 for French and 4 for German.

I would need a way that I have more flexibility when I add or disable a language. In the above example you need to know the position of the value linked to the value. Sorry for not informing you about this in the first place.

Thanks

|||

WimVM:

Hello,

Thanks for your reply. This would indeed do it, nice and easy. What I forgot to say was that the language is set by an ID, LanguageID. This holds the value 1 for English, 2 for Dutch, 3 for French and 4 for German.

I would need a way that I have more flexibility when I add or disable a language. In the above example you need to know the position of the value linked to the value. Sorry for not informing you about this in the first place.

Thanks

I still have not found a solution for this. It would be great if somebody could help me with this. Thanks.

|||

WimVM:

Thanks for your reply. This would indeed do it, nice and easy. What I forgot to say was that the language is set by an ID, LanguageID. This holds the value 1 for English, 2 for Dutch, 3 for French and 4 for German.

I would need a way that I have more flexibility when I add or disable a language. In the above example you need to know the position of the value linked to the value. Sorry for not informing you about this in the first place.

Hi Wim, I have some suggestions.

Add a new table to your database called Languages. This table will have 2 fields: LanguageID and LanguageName. The data would look like this:

LanguageID LanguageName
1 English
2 Dutch
3 French
4 German

Add another table called Statuses. This table will have 2 fields: StatusID and StatusName. This table would not necessarily be used in queries, but could be used to store metadata about the Statuses. The data would look like this:

StatusID StatusName
1 Yes Status
2 No Status

Add another table called StatusTranslation. This table will have 3 fields: StatusID, LanguageID, and Translation. The data would look like this:

StatusID LanguageID StatusTranslation
1 1 yes
2 1 no
1 2 ja
2 2 neen
1 3 oui
2 3 non
1 4 ja
2 4 nein

Now, assuming your main data table is called MyTable. This table might look something like this:

ID Column1 Column2 UserLanguageID StatusID
1 Value1 Value2 2 1
2 SomeValue SomeValue2 1 2
3 Value1 Value2 3 1

The query to pull out the status data, translated for the user's language, would look like this:

SELECT
ID,
StatusTranslation
FROM
myTable
INNER JOIN
StatusTranslation ON myTable.StatusID = StatusTranslation.StatusID AND myTable.UserLanguageID = StatusTranslation.UserLanguageID

And the result of that query would be:

ID StatusTranslation
1 ja
2 no
3 oui

This has made some assumptions, such as that you have the UserLanguageID in the same table as the StatusID. You'd need to adjust the queries and data tables to fit your situation, but perhaps this will help get you started.

For some background, you might like Alister Jones' (SomeNewKid) blog post:Localizing the Content, which lead's to Karl Seguin's article:Creating multilingual websites - Part 2.

|||Thankstmorton!