SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction log backup file is too big


Transaction log backup file is too big

Author
Message
Beatrix Kiddo
Beatrix Kiddo
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 4391
Hi

(I'm new here, and am in a new job where I don't know the setup and have nobody else to ask, so I'm sorry if I ask stupid questions. I'm used to having a senior DBA to run things by, but to cut a long story short, this place doesn't have one.)

My transaction log backup file (not the log file) is too big, to the extent that I'm running out of space on that drive. I wanted to back up the transaction log with over-write (it seems to have been set to append in the past, even though backups of it were never taken!), but there isn't enough space on the disk.

The details are

- SQL Server 2008R2
- Full recovery model (for what it's worth; see above re backups!)
- Production environment, but low transactions/few users
- Very little help from any sysadmins on the storage side

If I

1) Switch to Simple recovery model
2) Delete the backup.bak file (?)
3) Switch back to Full recovery model
4) Take a full backup
5) Schedule a regular backup of the transaction log, with overwrite

will that work ok and solve my problem?

Thanks in advance. As I said, sorry if this is basic stuff. It's a new job, I have no support, and I'd hate to mess this up.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89369 Visits: 45284
Beatrix Kiddo (2/12/2013)

5) Schedule a regular backup of the transaction log, with overwrite

will that work ok and solve my problem?


As long as you never want to restore that log backup, as it will be completely useless....

Please read through this - Managing Transaction Logs

Log backups form a chain, to restore to a point in time you need all the log backups from the full that you're using to the point you're restoring to, not the last one.

Set your backups to back up to individual files, preferably with the date as part of the name.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Beatrix Kiddo
Beatrix Kiddo
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 4391
Coincidentally I was just reading that article and was coming back to edit my post, but too late!

Is everything else ok though, apart from the need to set the log to append, not overwrite?



Set your backups to back up to individual files, preferably with the date as part of the name.

This is interesting; is that standard practice? Having had a poke around here, that's definitely not what they've done here, and not in my last place either. If it's best practice I'd like to do it.

Many thanks again.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10202 Visits: 6378
Beatrix Kiddo (2/12/2013)
Coincidentally I was just reading that article and was coming back to edit my post, but too late!

Is everything else ok though, apart from the need to set the log to append, not overwrite?



Set your backups to back up to individual files, preferably with the date as part of the name.

This is interesting; is that standard practice? Having had a poke around here, that's definitely not what they've done here, and not in my last place either. If it's best practice I'd like to do it.

Many thanks again.


Generally yes. If you have 1 media set where your constantly backing up to, so appending to that file time after time, should anything happen to that file, you wont be able to recover using any of the subfiles contained within it, so you have lost everything.

Where as if each backup is to a seperate file, your only ever risking that 1 file becoming un-usable and not all your backups.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Beatrix Kiddo
Beatrix Kiddo
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 4391
Thank you. I'll do that once I've done some more reading. I do wish everybody here hadn't quit, this is all a bit much!

Can I go ahead with steps 1-5 now (with the modifications already mentioned above)? It's deleting the backup.bak file that particularly worries me.

Cheers!
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10202 Visits: 6378
You dont need to do steps 1 and 3 as that wont do anything except break the log chain.

If you have somewhere big enough to store the backup.bak file, move it there for now. Then take a full backup to a timestamped bak file, then do your transaction logs to timestameped trn files.

Ensure that you have a routine in place that pulls the files to a tape drive or some other backup media, so that you have recoverability going back in time as well, you never know when you might need a backup from.

In my signature there is a link to Ola's website, there is a very good comprehensive script which will do the backups for you to individual files.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Beatrix Kiddo
Beatrix Kiddo
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 4391
Now that you say that (about points 1 & 3) it is actually obvious; sorry about that.

We do have overnight backups going to tapes in an external data vault.

Thanks very much for the script; I'll do that now. I really appreciate the help. This new job is not what was advertised, but I'm trying to make the best of it as it's a good learning opportunity (assuming I don't break everything) ;-).
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10202 Visits: 6378
Breaking things is a good way to learn as you need to fix them, so you learn what not to do and what to do should it happen.

Granted would break things on a mock environment away from the production systems, but sometimes it cant be helped.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Beatrix Kiddo
Beatrix Kiddo
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 4391
So I've done that and now the log_backup.bak file is actually larger than it was, and is now larger than the database.bak file. Have I done something wrong here?
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10202 Visits: 6378
Probably not, depends how long ago the transaction log backup was last run.

But it still sounds like your backing up to 1 big file and not individual files.

Can you post the script(s) you have used to perform the backups?

This book might help as well - http://www.sqlservercentral.com/articles/books/89519/



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search