Attach db failure cause log not primary

Hi Everybody,

I need URGENT help with this. Because of space problem my collegue create a second log file on another drive. I wanted to clean the thing, made a detach, renamed the ldf into old, but now it won't attach anymore, I get the following messages:

EXEC sp_attach_db @.dbname='TARIFINFO_GEN',
@.filename1 = 'F:\Data\TARIFINFO_GEN_Data.mdf'

Server: Msg 5171 F:\Data\TARIFINFO_GEN_log.ldf is not a primary database file
Server: Msg 1813 Device activation error the physical file name 'F:\Data\TARIFINFO_GEN_log.ldf' may be incorrect

Do someone know what to do or where to look for a solution. Many thanks in advance, Isabelle.Try sp_attach_single_file_db 'TARIFINFO_GEN',
'F:\Data\TARIFINFO_GEN_Data.mdf'

Also, in many instances to clean logs you should be able to run something like:

backup log TARIFINFO_GEN with truncate_only

and then a dbcc shrinkfile.

Instead of detaching and re-attaching.

Eric

Asynchronous Mirroring and Server Failure

Hi

Can anyone please tell me what happens if I have Asynchronous mirroring setup and my Primary server physically dies and not available then what happens?. Does

1. Automatic failover occur to Secondary server?

2. What does the Database state show as. Primary, disconnected?.

3. what happens to my transactions. Are they lost?

4. Does any data loss occur?

If I rebuild a new server how do I sync back my current primary to the new one? In that case is it going to be just a fail back?

Any information is appreciated,

Thank you

AK

In Asynchronous mirroring the following occurs,
1. failover has to be forced only as it will be in High performance mode and witness server will not be present
2. safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal)
3. yes the if the principal server is down it will be shown as disconnected
4. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service.
5. you need to failover using the option force_service allow data loss.......you need to run the below command in mirror server,

Code Snippet

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable.

refer the below links,
http://sql-articles.com/articles/dbmrr.htm
sql-articles.com

Thanxx
Deepak

|||

Hi Deepak

Thanks for your answers. So my question is after failing forceover and the mirror database has been recovered what do we need to do to apply those transaction log blocks from principal that have not been recieved?. does restoring the transaction logs backups from the principal (if they are available) and applying them on recoverd mirror database is something that guarantees no data loss?

Thank you

AK

|||Hi Ankith,
Definitely data loss is bound to be present in High performance mode (Asynchronous mirroring). You cannot apply the transaction logs from principal because mirroring works on the concept of transactions and not transaction logs........transaction logs are present only in log shipping ! in mirroring all the transactions occur internally and cannot perform any restorations

Thanxx
Deepak
|||

Hi Deepak,

Great Explanation. In such a case, how can we

1.Find out how much data loss has occured. Is there any way to know this? (I am thinking not).

2.what is the best way to verify to know if data loss has occured?

3. Is there any way to mitigate or reduce the data loss if we cant prevent it?

Thanks

AK

|||Hi Ankith,
1. I don't think its possible to identify the data loss
2. Definitely data loss will occur but i am not sure
3. The only thing that comes to my mind is High availability mode which has automatic failover as witness server is present ! since you need to perform forced failover i.e you need to run the query so that failover occurs instantly ! if you want to mitigate it in asynchronous mode i think you need to configure alerts which will let you know the status of mirroring so that you can perform failover

Asynchronous Mirroring and Server Failure

Hi

Can anyone please tell me what happens if I have Asynchronous mirroring setup and my Primary server physically dies and not available then what happens?. Does

1. Automatic failover occur to Secondary server?

2. What does the Database state show as. Primary, disconnected?.

3. what happens to my transactions. Are they lost?

4. Does any data loss occur?

If I rebuild a new server how do I sync back my current primary to the new one? In that case is it going to be just a fail back?

Any information is appreciated,

Thank you

AK

In Asynchronous mirroring the following occurs,
1. failover has to be forced only as it will be in High performance mode and witness server will not be present
2. safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal)
3. yes the if the principal server is down it will be shown as disconnected
4. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service.
5. you need to failover using the option force_service allow data loss.......you need to run the below command in mirror server,

Code Snippet

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable.

refer the below links,
http://sql-articles.com/articles/dbmrr.htm
sql-articles.com

Thanxx
Deepak

|||

Hi Deepak

Thanks for your answers. So my question is after failing forceover and the mirror database has been recovered what do we need to do to apply those transaction log blocks from principal that have not been recieved?. does restoring the transaction logs backups from the principal (if they are available) and applying them on recoverd mirror database is something that guarantees no data loss?

Thank you

AK

|||Hi Ankith,
Definitely data loss is bound to be present in High performance mode (Asynchronous mirroring). You cannot apply the transaction logs from principal because mirroring works on the concept of transactions and not transaction logs........transaction logs are present only in log shipping ! in mirroring all the transactions occur internally and cannot perform any restorations

Thanxx
Deepak
|||

Hi Deepak,

Great Explanation. In such a case, how can we

1.Find out how much data loss has occured. Is there any way to know this? (I am thinking not).

2.what is the best way to verify to know if data loss has occured?

3. Is there any way to mitigate or reduce the data loss if we cant prevent it?

Thanks

AK

|||Hi Ankith,
1. I don't think its possible to identify the data loss
2. Definitely data loss will occur but i am not sure
3. The only thing that comes to my mind is High availability mode which has automatic failover as witness server is present ! since you need to perform forced failover i.e you need to run the query so that failover occurs instantly ! if you want to mitigate it in asynchronous mode i think you need to configure alerts which will let you know the status of mirroring so that you can perform failover

Astonishing slowly

hi everyone,

Primary platform XP Sp2 RAM 2gb. Sp2 for sql25k not applied yet.

Does anyone have any tip about how to speep up the development when you have plenty of tasks? My dtsx owns several loop container, for each, and ten tasks between sql and script tasks.

Indeed, is difficult work this way.

TIA,

You don't describe what is slow or how slow it is, but your astonishment leads me to think that it may be a validation problem that was introduced in SP1. See Jamie's post here or install SP2.

ASP/SQL Database question

Is there anyway of putting a prefix on an primary key field? I'll try explain with an example.

tblCodes

CodeID [PK] - Integer
CodeName
CodeDesc
CodeType

When a new code is created the ID is simply the next value as you would expect. To help with identifying the codes in my actual application, I would like the ID to be based on the CodeType.

For example: There are four types of code (red, green, blue, orange), if when creating a new code the user selects the type red, the CodeID will be "RED\1". If another is made using the type red, it will become "RED\2". The same applied the the others, a green code will have a prefix of "GREEN\" which increments.

Really not sure how to go about doing this, maybe a seperate table for CodeTypes is needed? I'm a novice programmer and i'm also new to SQL to please to be gentle!

There is and only needs to be 4 code types, if that's any help.

To give a bit more information on the reasoning for wanting the prefix on the CodeID.

Scenario
A user is inputting the amount of time he has spent on a code. There is a drop down value which he must select the CodeID from. At the moment there is no distinguishing between the CodeTypes, so he will just see 1, 2, 3 ,4 ,5.

If I can do what i'm wanting, the user will see GREEN\1, GREEN\2, RED\1, RED\2 and so on. Making it a fair bit more user friendly.

Any ideas?

I would suggest using a separate field to store the CodeType (Red, Green etc) in addition to the ID.

This will give you the flexibility of using it in different ways e.g. concatenate in sql statement to generate strings like "Green/1" or use it separately to say group by CodeType.

|||

Ideally, you need to create another table to store user/CodeType pair to deal with Many-To-Many relationship. The table at least includes two columns: UserID and CodeId.

|||

I don't really see why you are saying I need to have UserID in there at all.

I'm still not sure how to do this :(

It's only really needed for presentation reasons. So the actual value doesn't need to be stored as GREEN\1.

For example, I want the drop down menu to show GREEN\1 or RED\12 simply to make it easier for the user to tell what time of code his is picking, rather then all the codes looking the same but just with different numbers (1, 2, 3, 4, 5, 6).

Any ideas?