Standby Server - Applying Transaction Logs Automatically
Jun 21, 2001
Can anyone direct me to a code that would automatically apply transaction logs to a DB in the standby server? We have a process that dumps the transaction log backup from the primary server into the backup server every hour on the hour, but I need to apply that transaction log as soon as it is in the standby server.
I am sure someone will ask, why not do transactional replication or log shipping? My answer to that is I have yet to learn how to setup replication between servers. I need to get our backup server up and running in the next few days.
I have recently implemented a backup solution that keeps our standby server up-to-date with nightly database backup and restores. Ironed out all the problems with syslogins and orphan users. It's been working very well over the past few weeks.
On the production server we do hourly tran log dumps every hour between 8am and 10pm. I would like to implement some form of "log shipping" to bring the window of vulnerability down to 1 hour. By making some alterations to my current process I been able to incorporate the hourly log dumps in the same process.
However, when I try to restore the log on the standby servre I get the following message, Server: Msg 4305, Level 16, State 1, Line 1 Specified file 'HODB1SQLBACKUPRace_Prd_T_dump.200101231513' is out of sequence. Current time stamp is Jan 23 2001 2:01PM while dump was from Jan 23 2001 3:01PM.
After a bit of investigation I found the dumptrdate field in the sysdatabases table and it was set to Jan 23 2001 14:01. After updating this field to Jan 23 2001 14:01, I ran the load command again but received the same error message.
Is there something else that needs updating on the standby server? Is what I'm trying to do possible in 6.5? Any help or ideas would be greatly appreciated.
My replication application need to be able to skip the from-last-stop remaining logs (that means to skip the logs generated from the last stop time of my replication application), how can i realize it? thanks.
I have a windows app which is used in australia and uses web services to communicate with the database located in new zealand. I have a problem where dates that are returned from SQL are automatically modified to suit the timezone and i dont know how to turn it off.
For example, i insert the value '10/Jan/2006 20:00' into the database from a PC located in australia. When that data is selected back out, a 4 hour time offset is automatically applied to it and the time is returned as '10/Jan/2006 16:00'.
My current workaround is to modify my queries to say;
"Select convert(varchar, tb_date) tb_date from tablename"
Which returns the date as a string rather than a datetime which means the time offset is not applied.
This is a really poor solution and i'd be really keen to find out a better way... Thanks
I recently performed an SQL server 2000 installation. Other thanplacing the program files on C: and data on D:, I saw no option toinstall transaction logs in an alternate location.What is the best practice with SQL server 2000 for location oftransaction logs? I remember that Microsoft used to take the positionthat transaction logs should be placed on their own FAT partition.
I am trying to setup 2 SQL servers (ver 6.5) - A live server and a standby server. The plan is to have regular transaction logs dumps from the Live server databases to the standby server and then have these logs automatically applied.
I have managed to get the logs dumping automatically but when I tried to apply them I keep getting synchronisation/sequence errors.
Has anyone done/tried anything similar. Any advice on this would be very useful.
Thanks Samir
P.S I can't do this via replications as most tables in the database do not have primary keys.
I have a SQL Server 2005 HTTP endpoint. I create a transaction scope in a client application and call two methods on that endpoint. Will the operations performed in the two methods, really, be in a transaction?
In other words, will SQL Server enlist in the ambient transaction even when I am accessing it through a HTTP endpoint?
@RemoteQuery consists of a SELECT four-table join, all tables are on the same linked server.
The Linked server has been set up on MyLocalServer using the "Microsoft OLE DB for SQL Server" provider. In the "Provider Options" for the linked server properties I checked "Non transacted updates" and "dynamic parameters". In the "Server Options" tab I have checked "RPC", "RPC Out", "Data Access".
The EXECUTE part of the query runs great (and returns the data very fast) by itself. But with the INSERT part, the query fails and returns the error:
"Server: Msg 7391, Level 16, State 1, Line 17 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
The two servers are seperated by firewalls, so I believe the reason the query is failing is that I haven't followed the procedures for setting up the ports etc described in one of the microsoft support articles: e.g.: 250367.
Configuring the ports involves too much company politics, and besides, for what this query does, it does not need the benefits of a distributed transaction.
How can I execute my query without SQL Server automatically trying to upgrade it to a distributed transaction?
More Info: I can execute the query as a straight INSERT/SELECT linked-server query and it does the INSERT on the local SQL Server just like I want it to, so I assume it is not trying to use distributed transactions; but it takes around 7 seconds to run even though the entire SELECT is executed on the linked server, whereas executing with sp_executesql takes only 1 second.
I thought selected "non-transacted updates" in the provider would solve this problem, but it did not.
I've been reading a million and one posts on replication
My scenario is that i have a live SQL 2000 server. In a DR invokation, i'e i've lost my live sever, i want to be able to access the same data at the DR (SQL 2000) site and have it accessable to the users. DR server has a different name to the live box.
Replicate
Master CRMDatabase
Data changes all the time but can have hourly replication of transaction logs for this example. I've currently researched a sp called update logons but this has to be fed each account name to enable them on the new server. There must be a way to activate all CRMDatabase logons with the new server?
Could someone be kind enough to lead me through a step by step guide on the best solution.
I am about to apply DBCC DBREINDEX to a large database that is part of transactional replication and synchronised every 3 minutes. What are the likely implication and what precautions I must take.
What is the best way to clear the transaction logs. My backup job each night is ending because it says it is running out of disk space and I need to clear up the transaction logs. Any help is appreciated. I see many different options (trucate option, auto shrink, etc.), just need some assistance tosome more specific best approaches. Thanks
Im having issues truncating my transaction logs. I have logs in excess of 40 gigs. All the info in the BOL is very vague. Any assistance would be apreciated.
to take the transaction log back up regularly I should have truncate log on check point false. If I do so then how will I truncate the log. regards, Renu
I am trying to import records via bcp (about 1,500,000 records) and I keep running out of disk space. Is there any way to limit or do away with the transaction log (and still be able to import)?
When I look at the Database maintience plan history entry for backup I have a message that reads: "Backup can not be performed on this database. This sub task is ignored".
Have anyone come across this error before?? As part of the Maintenance plan some transaction log are being backup and some aren't instead they receive the message above.
i have several sql servers doing maintenance plans and backing up the transaction logs to tape. unfortunately it seems that the server keeps adding the transaction logs to the same tape, without overwriting them. Nowadays a transaction log backup to disk takes 2 minutes but when done to tape it is taking up 1hr54minutes. What can i do so that the tape is automatically initialised without having to do it manually... Thanks
Is there a way to view the transactions from a .TRN transaction log file? If so can I overwrite some of the transactions on the file and then restore from it? I am just curious.
Hi, I know just about nothing about SQL Server. I am getting this error:The log file for database 'my_database' is full. Back up the transaction logfor the database to free up some log space.I can't access the transaction logs to back them up. I am told that my ISP'stech support should have it set up to shrink those logs automatically everyso often. Is that true?Why are they needed? Up till Monday, the logs are just of our getting SQLServer set up, so couldn't the logs just be deleted? Most of my site can'trun with this problem.I'd appreciate any enlightenment anyone can give me!Thanks, Jill
This seems like it'd be a really stupid question, but for the life of me Ihaven't been able to find an answer that works....I have a database that's approximately 400MB when shrunk... and thetransaction logs are at about 4.8GB when shrunk... I can't seem to get thetransaction log any smaller, no what I try. How can I get the log downbelow 1GB and keep it there? I've only got an 18GB hard drive and I need torun 2 copies of this database.-steve
Hello, I am trying to figure out the time a certain store procedure was executed. I know the SP's name and approximetly the time it was executed. Is this possible to do? Honestly, i am not sure if SQL server 2005 is smart enough to keep track of commited transaction on the server.
I have seen quite a few people post this type of problem, but I am finding few solutions. Your advice and/or experiences are greatly appreciated.
Here's my scenario:
Environment: Windows NT 4.0 SP 6, SQL 7.0 (set up for Transactional Replication)
Problem: We have several remote dB machines configured for full recovery. On these machines are several dB's that are capturing aprox. 280 data points per second per unit. (Each dB represents one unit, and we have 21 units) No problem here. The problem is the transaction logs, that obviously grow profusely, will not SHRINK after, backups, dbcc commands and TSQL has been issued in failed attempts to shrink the logs. (in other words we've tried everything)
My questions are:
1. Because we are replicating, is it absolutely necessary to configure dB's for FULL recovery? How do I check in 7.0 if the dB is certainly in FULL vs. SIMPLE recovery mode?
2. I work with SQL 2000 and shrinking files is no problem. How can we shrink these log files in SQL 7.0?
It is rather urgent I find a solution as we are running out of hard drive space on our remote machines. Please help :-(
I am having trouble Truncating a Transaction Log. I`ve tried everything in Book Online. I`ve backed up the database, I`ve tried DBCC SHRINKFILE, DBCC SHRINKDATABASE, BACKUP LOG TRUNCATE_ONLY ...etc, but it will not shrink. Any suggestions ? Thanks.
Currently, my databases and their corresponding transaction logs are all on the same disk array. I finally was able to acquire a seperate disk, specifically to seperate the logs, in case of failure, etc. Now, I need to figure out how to go about moving the tansaction logs off the current disk array and on to the new disk. In Enterprise Manager, I brought up "Properties" for the first database, and went to the "Transaction Log" tab. Clicked on the "browse" or "..." button in the location field and got the following message: "A transaction log file's physical name cannot be changed once the transaction log file has been created". :(
I have been looking through Books On Line, but have been unable to find anything helpful yet.
Can anyone help me figure out how to go about moving a transaction log's location? There has to be some way. Even if it involves shutting down the server, altering system tables, etc. I need to get these moved.
I have set up a maintenance plan to backup my databases but when I view the maintenance plan history the transaction log backup steps have a success tick but a message saying :
"Backup can not be performed on this database. This sub task is ignored"
I have looked in my backup directory and only see *.BAK files and no *.TRN. The transaction logs are supposed to back up at 1AM and the databases at 2AM
All my databases being backed up have the truncate log on checkpoint option set. Is this best practise according to my backup schedule ?
