Backing up Logs using Maintenance Wizard in SQL 2005 SP2
-
I have SQL Server 2005 SP2 64bit. I have four databases as of right now, I have schedule log backup maintenance wizard but for some reasone it fails. when I go back to edit all the selected databases are not selected any more. I also chekced report from sql agent and that does not tell me anything on why it failed.
As work around I am backing up log manually using query but this is not truncating my logs once the back up is done.
Please help!
-
Check what is the recovery model of the databases. Also it wil be great if yo post the error message that youget.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
recovery model is set to full.
when I select databases for log back up from maintenance plan wizard and click ok and than when I go back to and check the databases they get unchecked.
when i execute it runs with error and error does not tell anything. Failed to execute that's all it says in the logs.
-
I have encountered the same problem. When I use the Maintenance Wizard, it works fine for the backups, but does not work for the transaction logs. If you select "all user databases" it may back up some transaction logs, but it does not back up all of them. And when I go back in, even though all databases are selected, that is not what shows in the SQL script.
So I am using a TSQL statement to do the transaction logs.
In reference to transaction log cleanup: include the with retain days in the SQL statement. BACKUP LOG [iFix] TO DISK = @File With RetainDays = 4. This will cause it to clean up after itself.
-
how can I make my log backup work using Wizard and not script and where is the option for log cleanup in wizard?
-
You have to use the delete old files option available with the maintenance plan.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
I am talking about truncating logs after log backup. But for me I cannot use wizard for log backup.
Log should truncate after full backup,right? or do you have to manually trucate it.
-
You may not have seen this. Perhaps it can help?
http://www.simple-talk.com/sql/backup-and-recovery/pop-rivett-and-the-expanding-log-/
-
The log will not truncate after a full backup unless you also back up the transaction log. Backing up the transaction log, should cause the log file to shrink.
However, you may need to set AutoShrink to true on the database options. This may not be acceptable in some environments, and where that is the case, you would need to schedule a job to run that would shrink the database and log file for you you. The maintenance plan wizard will allow you to create a job whose only function is to shrink the databases.
-
We have the trans log backups failing after applying SP2 on a x64 SQL 2005 installation also with a message that it can't backup a database in simple recovery mode. We also select all user databases but then when you open the task again, that's not selected anymore. In any case, it shouldn't matter that some of the databases are set to simple recovery mode because the trans log backup task in the maintenance plan has a message in the window when you select the databases that says "Databases with simple recovery will be excluded". We've seen the trans log backup job work on other servers where it skips the simple recovery databases and doesn't fail. Why does it fail on certain installations? What's the difference? I couldn't find anything on Microsoft about any kind of bug. I did see other postings about this problem, but the person was always told to do it a different way or change the recovery mode. This is a nice feature so you don't have to micromanage which databases you can run trans log backups on depending on the recovery model. Is there something with the installation? Is there a bug? If there's a bug, how can this be reported to Microsoft as a bug?
-
There is shrinkdatabase task in maintenance plan can't that be used to shrink th log file.?
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
The shrink database task in the maintenance plan works, but the task to back up the transaction log does not behave in a consistent manner. When you tell it to back up the transaction log for all user databases, it does not back up all of them, even though the recovery model is set to full or bulk-logged.
-
We have seen the same tran backup issue with using 'All User databases' since applying SP2..then SP2a.
I seems that before SP2 'All User' ignored databases in simple mode or offline databases and now it does not. Had to delete and readd the task to get an offline db out of it even though it wasn't listed! But have found that even though the list only shows your Full mode databases you still have to individually select them. Anyone know if the latest rollup (build 3175) really fixes this problem?
50001018 The database backup selection is lost in the database designer task for a transactional backup or for a differential log backup http://support.microsoft.com/kb/936305/
thanks
-
Any help please! Still not able to back up logs with maintenance wizard.
-
our main database full backup is taking 2 hour time and scheduled @ 1 am and transaction log backup scheduled 24 hr @ the interval of 10 min.
is it possible to take log backup during full backup was running at mid night or i have to change the schedule for log backup?
pl.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply