October 28, 2008 at 4:13 am
Hi,
I setup the backup for transaction logs in Maintenance Plan and run it for every night. But every morning when I check the job status, it was failed. Some DB transaction logs are backup fine(I checked in the backup folder) but some aren't. I have 10 databases with some are FULL and some are SIMPLE model.
I checked in the SQL log and Application Log on the server but I can't see anything strange.
My question is, transaction log failed because some are SIMPLE mode? or Something?
Thanks.
October 28, 2008 at 4:18 am
You can't backup the log of a database in simple recovery mode. The point of simple is that the log is not retained and backups of it are not required.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2008 at 4:36 am
Hi Gail,
Well, I have aware of that but as you know when you set the maintenance plan I selected for ALL DATABASES so that I can do all in one go for Integrity Check, Backup Database, Transaction Logs and so on.
I can't take out only the database which are SIMPLE Mode, other wise those databases won't be backup.
Do you think transaction log failed because of all setup in one maintenance plan?
Anywhere can I look at the details error of transaction log failed?
Thanks.
October 28, 2008 at 4:51 am
Leo (10/28/2008)
Hi Gail,Well, I have aware of that but as you know when you set the maintenance plan I selected for ALL DATABASES so that I can do all in one go for Integrity Check, Backup Database, Transaction Logs and so on.
I can't take out only the database which are SIMPLE Mode, other wise those databases won't be backup.
Do you think transaction log failed because of all setup in one maintenance plan?
Anywhere can I look at the details error of transaction log failed?
Thanks.
You can setup 2 maintenance plans - one for log backup and the other for the integrity checks. In each plan you can include the needed databases.
As Gail said you can’t backup log for databases that are set as simple recovery log. If you want to make sure that those are log backups that fail and no other log backup failed, you can check SQL Server’s error log.
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 28, 2008 at 5:12 am
Thanks. I add another maintenance plan for transaction log only.
October 28, 2008 at 6:36 am
Leo (10/28/2008)
Thanks. I add another maintenance plan for transaction log only.
You need to have them in different plans because they have to run at different intervals. Remember what the point of backing up a transaction log is - point-in-time restores. If you're only backing the tran log up when you do a full backup, you are wasting your time and may as well have the DB in simple recovery. Your recoverability options in a disaster will be the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2008 at 8:52 am
Leo (10/28/2008)
Thanks. I add another maintenance plan for transaction log only.
How often does the maintenance plan for transaction log run ?
October 28, 2008 at 10:15 am
GilaMonster (10/28/2008)
Leo (10/28/2008)
Thanks. I add another maintenance plan for transaction log only.You need to have them in different plans because they have to run at different intervals. Remember what the point of backing up a transaction log is - point-in-time restores. If you're only backing the tran log up when you do a full backup, you are wasting your time and may as well have the DB in simple recovery. Your recoverability options in a disaster will be the same.
You are right, I run the full backup at 1:00AM then Tran Log backup at 4:00AM. As you said, that is completed waste of my time coz......nobody using the database between 1:00 to 4:00AM and I don't required for the Tran Log becasuse I already done the full backup on 1:00AM.
I don't need another maintenance paln for Tran Bak really. Thanks.
October 28, 2008 at 10:19 am
Leo (10/28/2008)
GilaMonster (10/28/2008)
Leo (10/28/2008)
Thanks. I add another maintenance plan for transaction log only.You need to have them in different plans because they have to run at different intervals. Remember what the point of backing up a transaction log is - point-in-time restores. If you're only backing the tran log up when you do a full backup, you are wasting your time and may as well have the DB in simple recovery. Your recoverability options in a disaster will be the same.
You are right, I run the full backup at 1:00AM then Tran Log backup at 4:00AM. As you said, that is completed waste of my time coz......nobody using the database between 1:00 to 4:00AM and I don't required for the Tran Log becasuse I already done the full backup on 1:00AM.
I don't need another maintenance paln for Tran Bak really. Thanks.
Yes you do need T-Log backup (if you care about recovering the data). Set your Tran Backup Plan to run every hour during business hours, such as 7 am through 8 pm.
Please refer back to your other thread where it's explained:
http://www.sqlservercentral.com/Forums/Topic589814-5-1.aspx#bm590092
October 28, 2008 at 10:35 am
Leo (10/28/2008)
You are right, I run the full backup at 1:00AM then Tran Log backup at 4:00AM. As you said, that is completed waste of my time coz......nobody using the database between 1:00 to 4:00AM and I don't required for the Tran Log becasuse I already done the full backup on 1:00AM.
I don't need another maintenance paln for Tran Bak really. Thanks.
So if the database fails at 12:30 AM, the complete loss of almost 24 hours of data is acceptable? If so, put your databases into simple recovery and don't have any transaction log backups.
If not (and I suspect it's not) you need transaction log backups and more often than once a day. The interval between your log backups is determined by the amount of data that you are willing to lose if the server fails. If that is 1 hour, then the transaction log needs to be backed up every hour. If it's 10 minutes, then the transaction log needs to be backed up every 10 minutes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2008 at 11:16 am
Not sure why I'm bothering to post again ..... but here goes. As a DBA, the main job is to protect the data, just as you might protect your house.
When you leave the house, maybe you lock the doors, maybe you don't. If you don't lock any doors or the garage door, and someone takes all your possessions including your car, then you will say "I should have locked the doors". Now lets say you lock the garage door, but not the other doors. Now they steal everything except your car, did you do a good job protecting your house ?
A FULL backup with frequent TRANS LOG backups is locking the garage and all the doors.
A FULL Backup is locking only the garage door.
If you do not run frequent TRANS LOG backups, then acknowledge that you have chosen to only lock the garage door and you are willing to lose everything else.
(Ok, not the greatest analogy. just trying to make a point ) 😛
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply