Do SS Transaction Logs Have To Be Backed Up For Recovery To POF?
Aug 17, 2006
Coming from a (mostly) Oracle shop, I am unclear how SS transaction logs
work as far as up to point of failure recovery goes.
I have the few MSSQL databases I look after in full recovery mode, and I
set up maintenance plans to back these up online on a daily basis. In
the research I did it was recommended backing up the transaction logs as
well on a frequent basis to ensure recovery to POF or point in time.
I had assumed that they work like Oracles archive logs, in that once
they are full they are archived to disk in case they are needed, to
duplex destinations if need be. Does SS do this, or do I need to be
backing up transaction logs on an hourly (or shorter) basis? In looking
at enterprise manager I see that it does not look like the transaction
logs are archived anywhere, but overwritten once filled up, similar to
Oracle noarchivelog mode, in that if you lose your online redo logs or
anyone of them has recycled since your last backup, then you can only
recover to the last good backup.
Is this a correct assumption? Should I be backing up transaction logs
hourly to make sure I can have PIT or POF recovery?
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 ?
We use AlwaysOn availability groups, which has 2 SQL nodes configured (version: 11.0.3373.0), my full and differential backups are working for all my databases, however I am unable to perform any LOG backups.
I have double checked my Availability Group settings, and the backup preferences is set to: 'Prefer Secondary'
I've tried creating a maintenance job as well as using Ola Hallengren's maintenancescript job to back them up, but nothing is written to the drive. All jobs return successful every time, and take less then 3 seconds to run. There are no events being written in the SQL error log or event log.
Out of using stored procedure, reports and all this staff, I want to know the possible way to make sure that the data inside my Secondary Server Read only database are same as data in my primary server database.
Hello All,The setting is SQL server 7, on Windows NT.If the distributor can not access the log on the publisher database ( butsubscribers are not deleted yet), will the full backup of the publisherdatabase truncate the transaction log? My guess is, it will not truncate thelog.Can any one confirm please?Thanks,Mokles
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.
Setup log shipping from an Australian SQL2008R2 server to a UK SQL2012 server. With the time involved to copy the large backup file across a WAN link, we end up with around 2 days of log backups since the full backup.
- The Full Backup is restored to the UK Server to create a new database in norecovery mode. - The first log backup file is manually copied to the UK server and restored. - Start the Log Ship copy SQL Agent job - starts copying files prior the full backup on the Australian server. Copies 1 Log backup file and then fails. - Manually copy the next Log backup file to the UL server. - Start the Log Ship Restore SQL Agent job and runs successfully and restores the Log file. - Start the Log Ship copy SQL Agent job - AGAIN it starts trying to copy files prior to the Full Backup. IS this correct behaviour? I thought it should only copy Log files since the full backup.
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 am restoring database from Transaction Logs. I followed all the steps mentioned in the book. Just before the last step there are three options 1. Leave Database Operational. No Additional Logs can be Restored. 2. Leave Database Nonoperational But Able to Restore Additional Transaction Logs. 3. Leave Database Read_Only And Able To Restore Additional Transaction Logs.
Option 2 and 3 aresupposed to set on the NORECOVERY flag.
I tried both options 2, and 3 , But still got messages that I did not specify WITH NORECOVERY or WITH STANDBY.
I am currently having a problem with a transaction log not emptying even when backed up and truncated. I have done a full database backup and then a transaction log backup expecting this to flush the log however the log is not emptied and it is growing larger and larger. Even when I truncate the log it still doesn't free any space up. Can anybody out there spot the fundemental error in my working??? most grateful. Andy (SQL allegedly)
