Recovery :: AG Databases Went Into Synchronizing / In Recovery After Upgrading To 2014
Sep 19, 2015
We have 3 replica AG setup. 2 replicas are in sync/automatic failover, the other(DR Server, different subnet) in asynchronous/manual mode…All these replicas were on sql server 2012, Recently we upgraded DR server to 2014. Since then we have a problem, the AG databases in 2014 instance went into ‘Synchronizing/ in recovery’ state…The SQL server error log has message, the recovery couldn’t start for the database ‘XYZ’…We tried to create a new database and add it to AG , it works for fine for other two 2012 replicas, but on 2014 we see the same issue
View 3 Replies
ADVERTISEMENT
Dec 23, 2014
is bulk logged recovery model support point in time recovery
View 9 Replies
View Related
Aug 17, 2015
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies
View Related
Sep 17, 2015
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
View 4 Replies
View Related
May 5, 2015
in the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
View 6 Replies
View Related
Oct 9, 2007
Hello!
I make a full backup of many databases (about 45).
How can I recovery all databases at once? With a script?
I use SQL Server 2005.
Thanks
ezinsi
View 4 Replies
View Related
Nov 1, 2015
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
View 2 Replies
View Related
Mar 20, 2007
I need to display the recovery mode of ALL databases on a server into a table. (Just like the sp_helpdb prcedure extended with the recoverymodel of each Database).
Can anyone give me a hint on this or provide me with a little tsql code snipplet to realize this?
Thanks alot in advance
View 4 Replies
View Related
Apr 10, 2008
Greetings all and thanks for reading this post.
Here is my situation... I have 2 fairly large databases. Full backups are 83gb & 63gb. I am in the process of moving these database to a new data center. I've taken full backups of these databases and shipped them to the new center. I have been taking transaction log backups (larger db every 24 hrs smaller db every 15 min ... from log shipping).
I want to restore these databases in the new data center. I've gone ahead and restored the dbs in the new location.
Question final cutover.. can I just apply the transaction logs to the databases on final cut-over or do I have to restore the database backup first then apply the transaction logs?
Is there an other way to do this that I'm missing?
Thanks.
Kurt
View 3 Replies
View Related
Sep 16, 2015
I have log shipping enabled on databases(primary and secondary) and works fine. I need to implement TDE on the database. I have experience on implementing TDE on databases which are not used for log-shipping.steps needed to setup TDE which are involved with log-shipping.
View 2 Replies
View Related
Mar 6, 2006
Hi
What are the pros and cons of putting the system databases in full recovery mode
I am asked by my manager to keep everything simple and uniform.We have all the user databases in Full recovery mode and he wants all the system databases as well to be full recovery mode.
I would like to get the points where in i can argue that, there is no point in having the system databases in full recovery mode.Unfortunately i dont see anything related to this in BOL or internet.
Plz help
Thanks
Sreenath
View 3 Replies
View Related
Jul 23, 2005
Hi All,I have a 2MB database set to SIMPLE recovery. This database is usedonly to generate new keys to web users. It has two columns - UID andLASTDATE. The UID column is only updated when users log in or log out(INSERT & DELETE). The LASTDATE column is updated every time theyswitch between pages.Please don't ask for the reason why we do it this way, it just IS.The maximum amount of rows in this table at any given time is about2000. There is a non-clustered index on the UID column.My problem is that all throughout the day I have constant blocks by theWRITELOG waittype. I know my disk I/O is very slow (will be upgradedsoon) but this is the only database on which these blocks occur. Whatis writing to the transaction log if I have the DB set for SIMPLErecovery?Thanks,Josh
View 4 Replies
View Related
Feb 8, 2008
When I create a database, is there a way to make sure that the default recovery model is Full instead of Simple?
View 1 Replies
View Related
Dec 15, 2014
My recommendation to my manager is to base recovery on .bak and .trn files. However the .mdf and .ldf files can be backed up without being detached and I am wondering about the value of these file backups. He has set up backups to external drive of the .mdf and .ldf but not of the .bak and .trn files that I have set up. His recovery strategy is based on complete backup of the machine and bare metal recovery. I am finding it hard to defend my preference for going about recovery with a new SQL Server installation, then restoring .bak files. Has tried recovery from .mdf and .ldf? Please note that all the database files (system and user) are on a single drive, I think this was set up to allow the machine to be clustered. We are intending to do a test rebuild of the machine from his backups.
View 9 Replies
View Related
Sep 15, 2006
Hello,
I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.
I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task manager on the server before the disaster recovery.
However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task manager and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.
----------------------------------------------------------------
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....
Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -S.SQLExpr
COPLEYNEWSDATABASEscriptsMASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
----------------------------------------------------------------------
My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.
Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.
I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.
This is some kind of issue with SQL Server Express being restored by tape backup.
Any suggestions, thanks.
View 8 Replies
View Related
May 1, 2015
SQL Server 2012: Out of all the databases in the instance we have a requirement in which we need to maintain a high availability for the databases around (128). Our team believed log shipping will apt for this requirement but not sure on it's limitations, how many databases are allowed or supports this log shipping from a single instance.
View 7 Replies
View Related
May 13, 2015
I believe to configure maximum number of replicas it is required to have 5 nodes (1 Primary & 4 Secondary replicas). But how many databases can be included in one availability group, it think it is 32767. When I am referring to below URL it is mentioned something like "An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. " What does this 'sets' means, is that maximum limit for only eight databases or maximum groups should be eight?
[URL] ....
View 3 Replies
View Related
Sep 10, 2015
If you are doing a disaster recovery of an entire SQL 2005 cluster, can you just install SQL server and restore the system database to get the configuration?
View 4 Replies
View Related
Aug 27, 2015
We have several read-only nodes in our AlwaysOn cluster, which are set to use Synchronous-commit mode, which ensures that the logs are updated on the read-only nodes before any update statements complete. Even with this option, if we query a read-only node before the logs have been processed, we can read old data. I would like to know a strategy to ensure that a read-only query will definitely return up to date information. I had an idea that if I just used a different transaction type, like Serializable, that it might block the read-only query from actually getting the data until after the log file was processed, but I have not tried it, yet.
I would like to move more queries to the read-only nodes, in an effort to offload CPU utilization from the primary node.
View 2 Replies
View Related
Nov 20, 2007
Hi all
What is best Disaster/Recovery methode to securing the system databases.
Master database allows only Full backup
MSDB allows Full ,Diff
View 5 Replies
View Related
Jul 27, 2015
I have a database that I am trying to recover space from, it consists mostly of unallocated space, but I can’t seem to get that unused space released.
Database size: 40,245.13 MB
DatafileMB: 38,063.63, DataAvailableMB: 37,085.15
LogfileMB: 2181.51
Sysfiles shows:
fileidgroupidsizemaxsizegrowthstatusperfname
114872144-112820SomeDB
202792332684354561010486420SomeDB_log
The DB is in simple recovery mode. There are no open transactions (used dbcc opentran).
The server is running SQL Server 2014 and the DB is in compatibility mode SQL Server 2008 (100). It was upgraded to 2014 a month or two ago.
I have tried to re-size the log to 100mb, but any way I have tried (none gave errors), the log file remains the same size. I have tied to shrink the log file (through the UI and via DBCC commands) without success; no errors, but also no change in file size.
I have checked Log Reuse Waits, just in case, and as expected it showed “NOTHING” (select log_reuse_wait_desc, name from sys.databases)
I tried running a checkpoint, but that did not allow any resize or shrink to work.
I have tied creating large transactions to move the used point in the log file, in case this was the issue. I did this by creating tables that I drop after large inserts. While it shows me that the log space % used increased, the log file still does not allow the space to be reduced.
The following is what I was using for the transactions to get the log used.
BEGIN TRAN
select a.* into testtable from sysobjects a, sysobjects b, sysobjects c
ROLLBACK TRAN
Each insert creates 93,576,664 rows.
Running dbcc SQLPerf(logspace) :
DBLogSize(MB)LogSpaceUsed(%)Status
SomeDB2181.534.204890
Running dbcc loginfo:
RecoveryUnitIdFileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
0211437342728192602640
02114373427211437424645701280
Do I just need to continue running large transactions until the log space used gets high enough to get the “end point” in the log to really move? Is there an easier way to accomplish this (I have several DBs that have the almost identical problem), what I am using moves the Log Space Percent Used about a percent on each execution.
View 9 Replies
View Related
Sep 12, 2015
We have 2 switch between primary and secondary this morning.
after that one database in secondary went to "initializing / recovery pending".
How can i exit from this situation ?
View 3 Replies
View Related
Jun 22, 2015
How to find last login date/time for user DML acitivity on databases on Instance?
Is there any way we can find our the last login date/time for databases?
Note: 1. We can find if the SQL Trace is running and store.This is not good solution
2. Audit logins off/on is also not good solution.
3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.
View 5 Replies
View Related
May 18, 2008
what's the best way to synchronize to mssql databases
(one is 2005 express and one 2005)
View 11 Replies
View Related
Nov 22, 2007
what's the best way to synchronize 2 databases(mssql 2005) on 2 different servers.
View 3 Replies
View Related
May 15, 2007
I have a SQL Server 2005 web database and a local SQL Server 2005 database. I want a tool that can synchronize both the database from the source (web) to the target (local).
View 3 Replies
View Related
Apr 4, 2005
Hi,
I have a problem that I have never considered before. I am developing with C#.Net, Visual Studio.Net ans SQL Server on Windows Server 2003.
Recently we have been upgrading our servers and have installed 2 servers (Live and Dev). The live server is Win Server 2003 Standard while the Dev server is 2003 enterprise.
Both servers are running their own versions of SQL Servers (both 2002). What I need to know, is there a way to copy or 'sync' the dev database to the live without over-writing the live data. I need to be able to copy the dev db structure (the tables anf fields) but not over-write the live data with the dev data (the live data changes rapidly and none can be lost). These 'syncs' would happen 2-3 times a month so the 2 db's, at the time of sync, will look very different so a simple copy of the live data to the dev db is not possible.
Does SQL Server have a wizard or utility? I have not been able to find one.
Thanks,
Justin
View 4 Replies
View Related
Apr 5, 2004
Hi,
Currently developping a c# database based application, which will be used in different establishments worldwide. I'm currently a bit confused by the ms sql features offered. Because the huge data amount which will be fetched during application use from the database, the best option is probably to put a ms sql server in each establishment to realise quick data fething. Problem is that the servers, which are all running on the same database design have to be synchronised real-time. I read about datareplication with the merge option which I thought was a nice solution. The subscribers can only have read-only rights on these subscriber databases, because the merging doesn't work appropriate i heard, so this is no good solution. Then I read about distributing transaction coordination. Seems a good solution as well, but what happens if a server system crashes for one day and then comes back online? It won't be up to date anymore... Right now I think about a combination of these 2 features. DTC in combination with merge replication, but there must be a better solution. Fact is that a lot of users will be editing data worldwide non stop, and everyone has to be up to date. Because of huge data fetching I don't think it's a good solution to let everyone work worldwide with one single database. Is there a possibility to realise this with ms sql server? Because I'm getting more and more confused... Thx in advance.
View 2 Replies
View Related
Mar 4, 2008
Hi,
I have a potential client who wants to run three web sites at different locations, each storing data to SQL Server (let's say it will be SQL Server 2005, for the sake of argument). Periodically, but at least once a day, they would like all changes to be made in any server to be reflected in the other two.
As a develeoper, my initial approach would be to write web services that can transmit and recieve changes. Triggers on the database tables would copy data into "shadow" tables and then the web services would periodically wake up and send their changes to the other two servers.
However, before I go down this route, I wondered if any of the standard SQL Server tools (like SSIS) would handle this scenario (maybe with an agent FTPing files between the servers)?
Alternatively, does anyone know of any off-the-shelf partner solutions that can acheive the same result?
Many Thanks,
Chris Boothroyd
View 3 Replies
View Related
Oct 28, 2005
I had a merge subscription expire and now need to synchronize two disparate databases. How do I do so without overwriting the subscriber database with the publication snapshot? Thanks for any help you can give.
View 3 Replies
View Related
Jan 27, 2015
I Create an availibility Group with a primary and a secondary. (For test)
Then run Planed Failover.
Switched to secondary. That's ok.
After that I update some tables on secondary (That now is primary)
I Run Again Planed Failover on server 2.
Switched. OK
But primary database Get (Not synchronizing) Status.
And in primary I don't have that updates.
How to sync these databases and exit from Not synchronizing.
View 5 Replies
View Related
Jun 14, 2007
Please help!
Windows Server 2003 SE
SQL 2000 w/ 4
Veritas Netbackup 5.1 w/ MP5 (for backup/recovery)
Sometimes, not always, when I attempt to recovery a database I will follow the process of restoring the DB and it will complete with a status of successful. However when I attempt to view the DB in Enterprise Manager it states (loading) next to the DB - and never finishes.
As I said, this doesn't happen all the time, but of course when it happens I only know to start over, rebuild everything and go through the recovery process again. I am sure this is something simple.
I apologize for my lack of knowledge in regards to SQL.
Any insight greatly appreciated.
Thanks,
Kerry
View 8 Replies
View Related
May 19, 2008
Hi,
I want to drop my emp table.Is it possible to get back my emp table?
If possible pls give me the commands.
or give some useful website address
View 6 Replies
View Related
