Principal Db In Principal, Synchronizing State, The Mirror In Restoring State
Sep 26, 2007
We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.
This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.
Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?
Thanks in advance.
View 3 Replies
ADVERTISEMENT
Jan 4, 2006
I am using asynchronous mirroring. When I try to failover on the mirror server using €śalter database xxx set partner force_service_allow_data_loss€? (having disconnected the primary server from the network) it sometimes fails to failover (it reports that the database is not in the correct state to failover). I can re-connect the primary server, get them sync€™d again, then unplug the primary and it will failover ok.
Any ideas... or is this just an issue that will be fixed in the next SP?
View 5 Replies
View Related
Nov 21, 2007
Hi guys, right now I am having insufficient hard disc space at the secondary server (Mirror side) due to the large db that I have. Therefore, I get a new HDD on the server (Assuming located at drive G) and would like to shift the current mirroring db to that new drive. Is it possible for me to do that ? Hope able to get any suggestion from here ASAP. Thanks.
Best Regards,
Hans
View 4 Replies
View Related
Aug 12, 2015
I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).
View 4 Replies
View Related
Apr 13, 2007
I have a Server Agent Job that does a full backup of my principal database. I then have a separate job which does an incremental backup of the log files every 20 mins throughout the working day - the incremental backup sends me an email if it fails.
If I establish a failover to the mirror server, the backups continually try to run, sending me an email every 20 mins telling me that it can't back up the database as it's a Mirror.
Is there any way to script a method in the job that checks to see if the DB is a principal or mirror. if it's a principal I want the backup to happen, if it's a mirror I want it to skip the job.
Thanks in advance to anyone that can help
Niels
View 8 Replies
View Related
Apr 2, 2007
Hi guys, right now I am facing this scenario several times and I wonder why it can be happen. Both SQL servers are in Enterprise SP1 edition. The mirroring setup can be work fine to me. Once my principal get attacking by DDOS, then mirroring session change the status to 'DISCONNECTED'. However, after DDOS attack solved and line connection gets back on the principal server, the mirroring session still remain to the status ('DISCONNECTED'). If I restart the SQL services on principal server or mirror server, then only the mirroring session change to 'SYNCHRONIZING' status. Does anyone faced this happen before? I need some assistance on it and hope able to get any explanation or solution from you guys. Thanx and have a nice day.
Best Regards,
Hans
View 1 Replies
View Related
Nov 29, 2006
Hi,
can we control how transaction send from principal to mirror ?
If application inserting 10000000 rows in one transaction to principal database
how infomation will be transfered once it is commited and where it will be stored before it is replayed on mirror database?
1. Is it going to be 1 big data packet ?
2.is it going to be split on many packets (of what size ?)
Thanks
Alex
View 4 Replies
View Related
Mar 3, 2006
Hi there.
Imagine this scenario in High Availability mode :
The principal sends a transaction log to the mirror, then the mirror saves the log on disk and then notify the principal. What happen if the acknowledge is lost, because a network failure(back on track in 1 or 2 seconds) and the principal dont receive it ?
In this case an automatic failover will not occurr, and i dont really know what happen next...
Will the mirror resend the ack ?
The heartbeat mechanism plays an important role in solving this scenario ?
The principal must receive the ack to commit the transaction, and i´m not seeing how the problem is solved.
I have one other question. In "Database Mirroring in SQL Server 2005" document, is said that "...unlike a distributed transaction, failures to commit on the mirror will not cause a transaction rollback on the principal".
But in this case the mirror will be inconsistent with the principal. How is this problem solved ?
Thanks in advance.
View 3 Replies
View Related
Oct 16, 2006
Hi!
Is it possible to use teamed network adapters for Principal and Mirror in a quorum?
I need to use a VLAN networkconnection in order to setup a Synchronized Database Mirror with Witness session.
Thanks in advance!
View 3 Replies
View Related
Jul 6, 2007
In documentation about SQL mirroring, the only thing about version requirement are that: 1) enterprise, standard, developer edition; 2) SP1 for official support
Is there any requirement for the Principal and Mirror SQL instances? For example, can one be Enterprise, the other one be Standard? Can one be SP1 and the other one be SP2?
It seems we are having some problem with the above scenario, not sure if that's supported..
Thanks!
View 4 Replies
View Related
Nov 7, 2006
We were having problems setting up the mirroring, so I did it via command lines. I found out the "alter...set partnership" command works on the mirror server going to the principal, but gets a 1418 error when going from the principal to the mirror.
So if A is the principal and B is the mirror, A to B fails but B to A works.
If I reverse it so that B is the principal and A is the mirror, B to A fails and A to B works.
Any suggestions?
View 3 Replies
View Related
Aug 10, 2015
I have checked my Production databases, both are went to Disconnected state, both are unable to connect to the application, i checked all the services and its working fine. and restarted the end points on Principal and Mirror, still issue not solved, after restart the witness server End point both the databases are working fine.
View 3 Replies
View Related
Jan 31, 2008
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
Thanks,
View 9 Replies
View Related
Jun 22, 2006
I have SQL 2005 mmirroring sucessfully working in an ASP.NET 2.0 web application. I also have session state being maintained in SQL Server using the built-in functionality in ASP.NET. The problem is, even with "allowCustomSqlDatabase=True" and specifying a failover partner in the DSN, it appears that ASP.NET does not work with the failover partner. It always tries to get the session info from the "data source" server specified in the DSN.
Is there a way to get the session state to fail over to the mirrored server without writing a custom session state provider?
View 7 Replies
View Related
Feb 19, 2007
we config our SAP system to use SQL Server 2005 database mirror. but the mirror server hang by accident, after restart mirror server,the server return to normal,but the mirror can't be resume.
ALTER DATABASE R3P
SET PARTNER resume
the error is:
Msg 1447, Level 16, State 21, Line 1
ALTER DATABASE "R3P" command cannot be executed until both partner server instances are up, running, and connected. Start the partner and reissue the command.
View 2 Replies
View Related
Nov 8, 2007
Hi there,
I'm new to this forum but was wondering if you could advise me on getting the DB back to an accessible state. I tried restoring the DB but unfortunately I don't have enough disk space. The error message appeared instantly informing me of the lack of disk space. Now it says its in (Loading state) and I cannot access the DB. Is there anyway I can manually change it back??
View 3 Replies
View Related
Jun 6, 2006
The database in Mirrored server is always in the state of restoring only.
the database in mirrored server is restored with norecovery
then we started mirroring.
the status of mirroring is successfull and is synchronized.
but the Mirrored server is always in the state of restoting
not allowing use to open the database.
could any one know why this problem is comming and give some solution for this problem so that the mirrored database can be accessible opened and queryed to really verfiy that the changes made in principal database is mirrored to Mirrored database.
View 3 Replies
View Related
Oct 5, 2007
I'm testing log shipping locally and had it running between two boxes. I tried to remove log shipping so on the primary I unchecked "Enable this as a primary database in a log shipping configuration" as per another thread. The primary stopped after a bit but the secondary never came out of the (Restoring...) state.
I'm obviously missing a step that needs to be run on the secondary because what I did wouldn't work if site A exploded but I can't figure out what the last step is.
Thanks,
Jeff
View 4 Replies
View Related
Oct 29, 2007
When performing a tail log backup to an online database through SSMS, the database is placed in restoring state. Is there a proper procedure to take the database out of restoring state after the backup is completed?
View 1 Replies
View Related
Apr 23, 2008
I am new to this environment and was asked to ensure that the transaction log shipping for SQL 2005 on W2K3 boxes is working properly. I noticed the db's on the secondary server are show "Restoring..." I am not sure if these were set up in No Recover Mode or Standby Mode. I have no access to the secondary db's. I get an error message when trying to access them (error 927). Monitoring was not set up initially and as you may or may not know can't be turned on after the fact...unless you delete the job and start over.
My question is is "Restoring..." normal and what does it indicate?
View 3 Replies
View Related
Oct 23, 2007
I have a database called PrimaryJunk that is being log shipped to another location, secondary database is secondaryjunk. From PrimaryJunk to SecondaryJunk logs ship and apply fine with no issues. So I figured lets make sure that I am able to perform a role change and swap the roles and that is not working well. My original primary db is stuck in restoring state.
I manually backed up the active transaction log on primary server by performing a transaction log backup with the option 'backup the tail of the log and leave the db in restoring state'
MS site has the same step but mentions NORECOVERY. I am not sure if my step above does that automatically. http://msdn2.microsoft.com/en-us/library/ms191233.aspx
Wonder if thats the reason my original primary db is still in restoring state. any idea?
View 4 Replies
View Related
Jun 22, 2015
I noticed that after a SQL AlwaysOn failover, one of the DB in the secondary replica is stuck in Restoring state. The primary replica shows that it is in a synchronized state. These are the error logs from SSMS. How do I trace the cause of the problem?
Error: 5901, Severity: 16, State: 1.
Nonqualified transactions are being rolled back in database for an AlwaysOn Availability Groups state change. Estimated rollback completion: 0%. This is an informational message only. No user action is required
Error: 18400, Severity: 16, State: 1.
One or more recovery units belonging to database  failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
The background checkpoint thread has encountered an unrecoverable error. The checkpoint process is terminating so that the thread can clean up its resources. This is an informational message only. No user action is required.
View 4 Replies
View Related
Apr 7, 2015
Why do I leave my DB in Restoring State after attaching to the instance? Could not this fire a redo activity on Transaction Log?
Or, How does sql server undo all uncommitted transactions on active vlf during recovery?
View 2 Replies
View Related
Aug 3, 2006
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
Thanks.
View 1 Replies
View Related
Aug 29, 2006
Hello,
I am starting to use SQL Server 2005. I am having trouble connecting remotely. I tried to enable the 'sa' user and got this error:
Cannot use the special principal 'sa'
Is the 'sa' user no longer valid for remote connections?
Thank you
View 11 Replies
View Related
Jul 7, 2014
I have deleted a windows login user under security in SQL 2012 Management Studio. The users have also been removed from the associated databases. When I try to add back the login, I receive the message 'The server principal 'xxxx' already exists'. What do I need to do so that I can re-add the login ?
View 1 Replies
View Related
Apr 19, 2008
I am trying to grant various users permissions and am using the following sql command
GRANT Delete ON Resident TO hector AS barbara
barbara is a member of the fixed server role sysadmin and has Delete permission on the Resident table.
I log in to the server as barbara and try and execute this command and get:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'Resident', because it does not exist or you do not have permission.
If I just run
GRANT Delete ON Resident TO hector
then everything works fine but the grantor is then dbo. I want to be able to have it recorded that barbara was the one that actually gave hector permissino to delete on the Resident table. Is there a way to do this?
View 2 Replies
View Related
Aug 8, 2007
I can successfully execute my package via Business Studio but when I schedule it in SQL Agent I get the error message, 'could not get proxy data for proxy id = 2.' I am trying to execute the scheduled job with that proxy (call it abcd_proxy) because I have a logon id (call it abcd) with access to a specific network drive folder, which holds a required source flat file. The abcd logon is defined as a sysadmin account. I am unable to see that the abcd logon id is an added principal within the abcd_proxy, which I think is because the abcd logon principal has a sysadmin role. My proxy id = 2 definition looks to be defined the same way that my first proxy id was.
Does anyone have an idea about what the message means and how I can fix it?
View 1 Replies
View Related
Jun 26, 2007
Hello,
In the ErrorLog of my Sql Server , i found this line :
2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Operating System XP Home SP2
SQL Server 2005 Express Edition with Advanced SP1 )
( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express
My problem is :
i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )
i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN
How can i do it ? ( activating NTLM ? but how ?)
I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.
As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.
I don't know whether i'm querying with the "correct" forum.
Sorry for my poor english.
I shall appreciate any help about this problem
Have a nice day
View 1 Replies
View Related
Dec 14, 2006
Hi all,
I've got two VLDBs on 64bit hardware platform + 64Bit-W2003K Std R2 + 64Bit SQL2005 Std Edition.
If I set this guy up as the Principal, can I have the standby/member on 32Bit HW + 32Bit W2003 Std + 32Bit SQL2005?
Will this actually work?
Regards,
Uday Shivamurthy
View 4 Replies
View Related
Aug 7, 2006
Hi,
Here is my testing environment:
Database server: SQL Server 2005 Developer Edition with SP1
Application: An application developed by Visual Studio 2005 using C# (.Net framework 2.0) and ADO .Net 2.0.
Principal server: computerA
Mirror server: computerB
Witness server: computerC
Mirroring mode: High availability with auto failover
Connection String:
Data Source=computerA;Failover Partner=computerB;Initial Catalog=test_mirroring;Persist Security Info=True;User ID=sa;
Code used for failover:
while (true)
{
thisDataSet = new DataSet();
try
{
thisConnection = new SqlConnection(connectionString);
thisAdapter = new SqlDataAdapter(SQL, thisConnection);
thisAdapter.Fill(thisDataSet, name);
break;
}
catch (SqlException e)
{
thisConnection = null;
thisAdapter = null;
thisDataSet = null;
}
}
Test Case:
(Part A) At the beginning of the test, computerA was in principal role. I started my testing application and connected to computerA without any problem. Then I disconnected the connection of computerA to the network by unplugging the network cable of computerA. The failover of database from computerA to computerB was carried out without problem. computerB was in principal role at that time. The application was pending for about 45 seconds and running again without problem. Then I re-connected computerA to network and it became the mirror server. computerB was still in principal role. Up to this point, all works fine, but the problem was coming next. (Part B) I disconnected computerB from network, database failover occurred, computerA became principal again. But my application cannot switch the database connection to computerA and then kept pending. Then, I re-connected computerB to network. It was surprised that the application switched the database connection to computerA successfully at that point.
My questions:
1. I think Part B is abnormal, isn€™t it? The application should be able to failover from computerB to computerA, because it works fine when failover from computerA to computerB.
2. Is there anything wrong in my code leads to the abnormal behavior of the application in Part B.
3. How can I achieve Part B? Any suggestion or idea?
Thanks for your attention.
View 6 Replies
View Related
Dec 13, 2006
Is it correct to say
in any case exept when data or log file not available pricipal db will be availabe
and manual failover could be done only from from principal?
View 1 Replies
View Related
Jan 2, 2007
Hello,
I have setup database mirroring on two Windows 2003 R2 x64 servers using SQL Server 2005 SP1 Developer Edition. Our application is connecting to SQL Server using a SQL Server login. The application is using ADO and SQL Native Client to connect to the server. After a failover, our app attempts to reconnect to the database. The reconnect fails with the error:
Cannot open database "db1" requested by the login. The login failed.
The login is not associated with a user in the new principal database. I run sp_change_users_login to reconnect the user and login. sp_change_users_login says that it fixed 1 orphaned user. Our app then reconnects successfully.
I have tried several failovers, and each time I see the same behavior. The association between the login and user gets lost.
The issue is definitely with the login. I tried using sa to connect to the database, and then our app was able to reconnect after a failover.
Is this a known issue with database mirroring? Is it fixed in SP2?
Thanks,
Heather
View 3 Replies
View Related
