October 20, 2009 at 8:30 am
The Database is 48MB and the log file is 260MB. I run a full backup of this every night with Backup Exec. what should I do? Thank you
October 20, 2009 at 8:32 am
Simple or full recovery model ? If Full, How often do you back up the transaction logs ?
October 20, 2009 at 8:34 am
you need to do a log backup if your recovery model is full/bulk logged. a full backup will not truncate your log file.
If you dont have the space to do a log backup you should try and do a full backup, change your recovery model to simple and shrink the log. be very wary though if you have transactional replication running or log shipping.
October 20, 2009 at 8:47 am
Please read through this - Managing Transaction Logs[/url]
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 20, 2009 at 8:58 am
This is development server. Transaction job supposte to be every hour but it is been failing for ever. This is full db
October 20, 2009 at 9:06 am
Error message I get for transaction job:
Date10/15/2009 5:00:00 PM
LogJob History (SQL Backup transaction log job created 11/25/2008 3:24:53 PM)
Step ID1
Serverttttt
Job NameSQL Backup transaction log job created 11/25/2008 3:24:53 PM
Step Nameexecute master..sqlbackup
Duration00:00:00
Sql Severity16
Sql Message ID50000
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: NT AUTHORITY\SYSTEM. SQL Backup failed with exit code: 667 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.
October 20, 2009 at 9:10 am
is master..sqlbackup a procedure or a maintenance plan? i dont know off the top of my head if maint plans show in the error messages like that or not.
edit: i assume its an SP as it has the execute before it.
can you post the procedure code? Also, do you know where in the procedure its bombing out?
Do you have enough space to backup the log?
October 20, 2009 at 9:14 am
This is a maintance plan: How much space do I need for transaction back?
October 20, 2009 at 9:19 am
Well how much have you got, I would suggest doing a manual log backup, bear in mind that it will not alter the physical log size, you would need to do a shrink afterwards if the size of the log is an issue.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 20, 2009 at 9:20 am
If this is part of a maintenance plan, look at the maintenace plan history and post what it says is the issue for the transaction log backup failing
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 20, 2009 at 9:20 am
Krasavita (10/20/2009)
This is a maintance plan: How much space do I need for transaction back?
depends on how much of the transaction log is used. run dbcc sqlperf (logspace) to find out. that will roughly be the size of your log backup for each database.
whats in the job history log? why is it failing? common problems with maintenance plan setups are that you may have selected "all databases" in your tran log backup step and one (or more) or the databases are in simple recovery model and its failing on that.
October 20, 2009 at 10:59 am
Database NameLog Size (MB)Log Space Used (%)Status
db1809.2422 91.06001 0
October 20, 2009 at 1:18 pm
ok your backup is going to be about 700mb. if its a dev box and you dont have room for the log backup i would just switch it to simple recovery model and shink the log.
It still doesnt fully explain why your log backup job has always failed, it cant always have been a space issue
October 21, 2009 at 12:17 pm
I have a space and I just ran full backup on my db. My backup failed because of the space issue and that was fixed. What code for shrinking should I use?
It still doesnt fully explain why your log backup job has always failed, it cant always have been a space issue
October 21, 2009 at 12:53 pm
how can I shrink?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply