Automatic failover failure

Hello,

we're running a mirrored database with High Availability for Automatic failover including a Witness instance for a web application.

When doing a manual failover on the database in Management studio, the roles are switched correctly and the database is in "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. The web application has no problems switching servers by using client failover with the jdbc driver. There is no problem accessing the database with Management Studio.

However, if we stop the SQL service on the Principal server the role is automatically failed over to the Mirror server by the Witness. The database is then in the mode "Principal, Disconnected" which should be fine. However, accessing the database from the web application or with Management Studio yields some strange results. It is not possible to write to the database, and reading from the database works inconsistently (the web application seems like it can do it, but not from the Management Studio).

Starting the SQL service on the former Principal server makes the database go into mode "Mirror, Synchronizing/Restoring" and "Principal, Synchronizing". And it will stay that way indefinitely. There are not that many updates/transactions made to the database that can make it stay in this state, especially if you can't write to the database in the first place.

The next step taken after being stuck in this state is to stop the SQL service on the Mirror (former Principal), restart the service on the Principal (former Mirror). Accessing the database now works. The database is in mode "Principal, Disconnected". Starting the SQL service on the Mirror (former Principal) makes the database go into the normal "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. Access to database is normal.

The same erroneous behaviour can be observed by unplugging the network cable on the Principal server, so it seems like we can only get a smooth transition by doing a manual failover.

Any ideas on what might be the problem? Has anybody experienced a similar situation?

Can you see any errors or warnings from SQL error log in Principal & mirror servers?|||

These are entires I've found on the principal (former mirror) server

2006-11-26 12:42:52.60 spid8s Database mirroring is inactive for database 'db'. This is an informational message only. No user action is required.
2006-11-26 12:42:52.60 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.60 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:52.60 spid8s The mirrored database "db" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.
2006-11-26 12:42:52.75 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.75 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:54.52 spid8s Recovery is writing a checkpoint in database 'db' (5). This is an informational message only. No user action is required.

These are entries I've found on the witness server

2006-11-26 12:45:03.36 spid27s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.36 spid27s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.50 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.50 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.82 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.82 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:04.03 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:04.03 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.

I guess the errors about receiving data from SERVER01 should be normal since the SQL service is stopped on that server during this time.

Thanks,

Ingmar

|||

We are experiencing the same issue.

Some additional information on our situation:

We are running a mirrored database with High Availability for Automatic failover including a Witness instance for a non web application. As in the original post we are stuck (Principal, Synchronizing) and (Mirror Synchronizing / Restoring...).

We are using the new SqlCacheDependency (command based vs. table based polling) cache notification.

We are able to access some of the data in the database via SQL Server Management Studio - query window, however we can not expand the database in the Object Explorer window and if we right click on the database and attempt to access the properties page we receive a 'Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)'.

This may not be pertinent but we also noticed that even though the query from sys.database_mirroring show that the databases are in sync we observe that the Modified and Accessed dates on the new Principal .mdf and .ldf files lags the new Mirror partner by 16 Hrs for the .ldf file and 7 Days for the .mdf file.

|||Check this KBA http://support.microsoft.com/kb/912422 is any help in this regard.|||

Hello,

We were able to reproduce the problem in a test environment and it seems that we're only having problems with databases that have fulltext indexes. And, after applying the SP2 CTP in this environment the problem went away. We haven't touched the production servers yet as we wait for the 'full' release of the SP2.

Regards,

Ingmar

|||

We are also experiencing the exact same problems with trying to mirror a database containing a Full Text index. Does anybody have an estimate as to when the "full" release of SP2 is to be released?

Thanks,

Jody

Automatic failover failure

Hello,

we're running a mirrored database with High Availability for Automatic failover including a Witness instance for a web application.

When doing a manual failover on the database in Management studio, the roles are switched correctly and the database is in "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. The web application has no problems switching servers by using client failover with the jdbc driver. There is no problem accessing the database with Management Studio.

However, if we stop the SQL service on the Principal server the role is automatically failed over to the Mirror server by the Witness. The database is then in the mode "Principal, Disconnected" which should be fine. However, accessing the database from the web application or with Management Studio yields some strange results. It is not possible to write to the database, and reading from the database works inconsistently (the web application seems like it can do it, but not from the Management Studio).

Starting the SQL service on the former Principal server makes the database go into mode "Mirror, Synchronizing/Restoring" and "Principal, Synchronizing". And it will stay that way indefinitely. There are not that many updates/transactions made to the database that can make it stay in this state, especially if you can't write to the database in the first place.

The next step taken after being stuck in this state is to stop the SQL service on the Mirror (former Principal), restart the service on the Principal (former Mirror). Accessing the database now works. The database is in mode "Principal, Disconnected". Starting the SQL service on the Mirror (former Principal) makes the database go into the normal "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. Access to database is normal.

The same erroneous behaviour can be observed by unplugging the network cable on the Principal server, so it seems like we can only get a smooth transition by doing a manual failover.

Any ideas on what might be the problem? Has anybody experienced a similar situation?

Can you see any errors or warnings from SQL error log in Principal & mirror servers?|||

These are entires I've found on the principal (former mirror) server

2006-11-26 12:42:52.60 spid8s Database mirroring is inactive for database 'db'. This is an informational message only. No user action is required.
2006-11-26 12:42:52.60 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.60 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:52.60 spid8s The mirrored database "db" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.
2006-11-26 12:42:52.75 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.75 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:54.52 spid8s Recovery is writing a checkpoint in database 'db' (5). This is an informational message only. No user action is required.

These are entries I've found on the witness server

2006-11-26 12:45:03.36 spid27s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.36 spid27s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.50 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.50 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.82 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.82 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:04.03 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:04.03 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.

I guess the errors about receiving data from SERVER01 should be normal since the SQL service is stopped on that server during this time.

Thanks,

Ingmar

|||

We are experiencing the same issue.

Some additional information on our situation:

We are running a mirrored database with High Availability for Automatic failover including a Witness instance for a non web application. As in the original post we are stuck (Principal, Synchronizing) and (Mirror Synchronizing / Restoring...).

We are using the new SqlCacheDependency (command based vs. table based polling) cache notification.

We are able to access some of the data in the database via SQL Server Management Studio - query window, however we can not expand the database in the Object Explorer window and if we right click on the database and attempt to access the properties page we receive a 'Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)'.

This may not be pertinent but we also noticed that even though the query from sys.database_mirroring show that the databases are in sync we observe that the Modified and Accessed dates on the new Principal .mdf and .ldf files lags the new Mirror partner by 16 Hrs for the .ldf file and 7 Days for the .mdf file.

|||Check this KBA http://support.microsoft.com/kb/912422 is any help in this regard.|||

Hello,

We were able to reproduce the problem in a test environment and it seems that we're only having problems with databases that have fulltext indexes. And, after applying the SP2 CTP in this environment the problem went away. We haven't touched the production servers yet as we wait for the 'full' release of the SP2.

Regards,

Ingmar

|||

We are also experiencing the exact same problems with trying to mirror a database containing a Full Text index. Does anybody have an estimate as to when the "full" release of SP2 is to be released?

Thanks,

Jody

Automatic failover failure

Hello,

we're running a mirrored database with High Availability for Automatic failover including a Witness instance for a web application.

When doing a manual failover on the database in Management studio, the roles are switched correctly and the database is in "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. The web application has no problems switching servers by using client failover with the jdbc driver. There is no problem accessing the database with Management Studio.

However, if we stop the SQL service on the Principal server the role is automatically failed over to the Mirror server by the Witness. The database is then in the mode "Principal, Disconnected" which should be fine. However, accessing the database from the web application or with Management Studio yields some strange results. It is not possible to write to the database, and reading from the database works inconsistently (the web application seems like it can do it, but not from the Management Studio).

Starting the SQL service on the former Principal server makes the database go into mode "Mirror, Synchronizing/Restoring" and "Principal, Synchronizing". And it will stay that way indefinitely. There are not that many updates/transactions made to the database that can make it stay in this state, especially if you can't write to the database in the first place.

The next step taken after being stuck in this state is to stop the SQL service on the Mirror (former Principal), restart the service on the Principal (former Mirror). Accessing the database now works. The database is in mode "Principal, Disconnected". Starting the SQL service on the Mirror (former Principal) makes the database go into the normal "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. Access to database is normal.

The same erroneous behaviour can be observed by unplugging the network cable on the Principal server, so it seems like we can only get a smooth transition by doing a manual failover.

Any ideas on what might be the problem? Has anybody experienced a similar situation?

Can you see any errors or warnings from SQL error log in Principal & mirror servers?|||

These are entires I've found on the principal (former mirror) server

2006-11-26 12:42:52.60 spid8s Database mirroring is inactive for database 'db'. This is an informational message only. No user action is required.
2006-11-26 12:42:52.60 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.60 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:52.60 spid8s The mirrored database "db" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.
2006-11-26 12:42:52.75 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.75 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:54.52 spid8s Recovery is writing a checkpoint in database 'db' (5). This is an informational message only. No user action is required.

These are entries I've found on the witness server

2006-11-26 12:45:03.36 spid27s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.36 spid27s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.50 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.50 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.82 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.82 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:04.03 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:04.03 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.

I guess the errors about receiving data from SERVER01 should be normal since the SQL service is stopped on that server during this time.

Thanks,

Ingmar

|||

We are experiencing the same issue.

Some additional information on our situation:

We are running a mirrored database with High Availability for Automatic failover including a Witness instance for a non web application. As in the original post we are stuck (Principal, Synchronizing) and (Mirror Synchronizing / Restoring...).

We are using the new SqlCacheDependency (command based vs. table based polling) cache notification.

We are able to access some of the data in the database via SQL Server Management Studio - query window, however we can not expand the database in the Object Explorer window and if we right click on the database and attempt to access the properties page we receive a 'Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)'.

This may not be pertinent but we also noticed that even though the query from sys.database_mirroring show that the databases are in sync we observe that the Modified and Accessed dates on the new Principal .mdf and .ldf files lags the new Mirror partner by 16 Hrs for the .ldf file and 7 Days for the .mdf file.

|||Check this KBA http://support.microsoft.com/kb/912422 is any help in this regard.|||

Hello,

We were able to reproduce the problem in a test environment and it seems that we're only having problems with databases that have fulltext indexes. And, after applying the SP2 CTP in this environment the problem went away. We haven't touched the production servers yet as we wait for the 'full' release of the SP2.

Regards,

Ingmar

|||

We are also experiencing the exact same problems with trying to mirror a database containing a Full Text index. Does anybody have an estimate as to when the "full" release of SP2 is to be released?

Thanks,

Jody

autogrow of log file possibly cause timeout?

i had a problem with a server instance where an app processing a file and putting the data into the database using a stored procedure was timing out. the app is a service and in the case of errors, will try to process the file until it has been succesfully added data to the database. in the event viewer i keep seeing this message during this timeout period:

"Autogrow of file 'mydatabase_log' in database 'mydatabase' was cancelled by user or timed out after 15687 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."

is there a possibility that this process was causing my SP to timeout? are there any other log or debug files that SQL Express creates that might be helpful in discovering what caused the timeout? currently the log is at... ouch 10gigs restricted... maybe trying to autogrow from this while restricted is hogging resources?

As SQL Server Express databases are limited to 4GB per database, I just wonder why you need 10GB of logs ? Did you consider backing up your database and shrink your logfiles ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||when we created the database, we used the default setting for the log file, which was "By 10 percent, restricted growth ". currently we backed up the database, detached the DB, removed the old log file, and reattached the DB. it created a new log file and we set it to a smaller size. as i understand it the log file holds the uncommitted transactions, is this true? if we want the log file to stay at a certain size maybe i need to uncheck AutoGrowth? and going back to the original question, if SQL was trying to autogrow a 10 gig file, could this have used all the resources at the moment and timeout any stored procedures running during this autogrowth process?|||

Hi nattylife,

for an instance assump your Log File size is 12 GB, you have set up Auto Growth option in 20% , now when ever your T-Log file need to grow it will grow 12 GB * 20 % means it will grow 2.4 GB in size and while this process is running it occupy the server resource (yes it will effect while this happens in pick time).

If you stop/uncheck/disabled Auto Grow , you may be in trouble because if your T-log need to grow and Auto Grow is disabled it might be resulted in SUSPECT status of your database, so don't disabled Auto Grow set it in appropriate % / MB as per your *requirement/perdiction* in growth of your database. BTW what is your Recovery Model of your database?

Refer T-Log architecture in BOL to understand more, BOL is your best friend.

Hemantgiri S. Goswami

|||

Your way of removing the log file is dangerous and not best practise. You should take a backup of your database and shrink the log afterwards, rather than just *deleting* it and let it recreate by SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Your way of removing the log file is dangerous and not best practise. You should take a backup of your database and shrink the log afterwards, rather than just *deleting* it and let it recreate by SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

we backed up the database before we do any questionable practices. the database is filled with 3 text files that our app recieves every day. inside each file are the records for the database. we archive these every day for 90 days in case we have any database issues. after doing some more research, i found how to shrink the log in the CTP which was the approach i was looking for, just didnt find until after the fact. i didnt realize that this log file would grow coninuously, i was under the assumption it would eventually get written over. so we are discussing a way to archive the ldf file now too.

|||

Hi,

you should consider using the backup functions of SQL Server. You are able to do either full / differential or transaction log backups. A combination of those will help you to make your databases disaster-recoverable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||i checked the backup options in the CTP. i see where i can do a log backup, but is this a manual process or when will it do it on a regular basis since i dont see any option to automate it. if i wanted to automate it, would i need to implement this with my own service or such?|||Hi,

SQL Server Agent is not shipped with SQL Server Express. An approach could be to script out the backup command (using the functionality of the management studio) and schedule the execution of the script using an AT command (or any other scheduler) by executing the script with SQLCMD.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

AutoClose

How do you set the AutoClose property of a SQLExpress db? Also, can the user instance timeout property be set at the database level too to make for easier application distribution.

Is there any downside to turning off AutoClose in order to speed up the initial startup times after a period of inactivity.

I think the AutoClose property is true by default in SQL Server Express. You can look it up and set it in Management Studio Express / Databases / Database / Properties / Options.

I have a question too:
If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?
Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure?

How does connection pooling has effects?
What must a developer do to be sure that the database files are in a copy able state?

I asked this a few days before, but i did not get an answer.

|||

Hi Markus,

"If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?"

No the database *could* be not consistent in the time of the copy, cause you would also need the log with possible pending transactions.

"Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure? "

The safest way would be to detach the database for cpoying (or stopping the server service). You can′be sure that in the meantime of copying another user could open the database again and change something in there (unless you change the state of the database to single_user mode). ANother option for a *hot* backup (which can be done along with open connections is to use backup instead of the *cold* method trhe detaching)

"How does connection pooling has effects? "

Same for connection pooling.

"What must a developer do to be sure that the database files are in a copy able state?"

You could query the server for the database state, but you could never be sure that the database in this state during the whole copy work. The status can be retrieved using the SMO enumeration DatabaseStatus on the Database object. (un?less you change the state of the database to single_user mode)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hello Jens,

thank you very much for your answer. Ok, safest way is detach or use backup.

> No the database *could* be not consistent in the time of the
> copy, cause you would also need the log with possible
> pending transactions.

Excuse me that i have another question, but i like to understand it. When a power outage occurs during pendig transactions this is handled from Sql Server at next startup.

When an attach attaches the database in the state as it was during detach, why is this a problem then?

What is the inconsistency and why is this a problem for Sql Server? Or do you mean that there can be changes in the files during the copy and only a part of these changes are in the copied files?

Regards,
Markus

|||

Commonly spoken this is right.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||When I attach this db to Management Studio and turn off AutoClose and then detach it. Upon re-attaching the AutoClose is reverted to being True again. This is a user instance level db so I dont need it to remain attached to the SQLExpress instance. How do you set the field again so the setting sticks.
|||Acutally I think you can′t. But you have another option from the MSDN archives:

"A system administrator on the parent instance can set the duration of the time-out period for a user instance by using sp_configure to change the user instance timeout option. The default is 60 minutes."

http://msdn2.microsoft.com/en-us/ms254504.aspx

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de
|||

> Commonly spoken this is right.

Sorry, may i ask you to explain it to me more exactly? So that even i can understand it?
It is still so that i don't really understand why copy of the .mdf and .ldf file is not safe. It's only a feeling that it may be so.