Transaction log backup file is too big

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Transaction log backup file is too big

20 posts, Page 1 of 2

1 2 »»

Transaction log backup file is too big

Rate Topic

Display Mode

Topic Options

Author

Message

Beatrix Kiddo

Posted Tuesday, February 12, 2013 3:24 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 19, Visits: 63

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.

Post #1418837

GilaMonster

Posted Tuesday, February 12, 2013 3:29 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

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 2008, MVP
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

Post #1418839

Beatrix Kiddo

Posted Tuesday, February 12, 2013 3:41 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 19, Visits: 63

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.

Post #1418846

anthony.green

Posted Tuesday, February 12, 2013 3:44 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

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
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

Post #1418850

Beatrix Kiddo

Posted Tuesday, February 12, 2013 3:49 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 19, Visits: 63

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!

Post #1418852

anthony.green

Posted Tuesday, February 12, 2013 3:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1418853

Beatrix Kiddo

Posted Tuesday, February 12, 2013 3:57 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 19, Visits: 63

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) Wink

Post #1418854

anthony.green

Posted Tuesday, February 12, 2013 3:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1418856

Beatrix Kiddo

Posted Tuesday, February 12, 2013 4:18 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 19, Visits: 63

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?

Post #1418862

anthony.green

Posted Tuesday, February 12, 2013 4:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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/

Post #1418864

« Prev Topic | Next Topic »

20 posts, Page 1 of 2

1 2 »»

Permissions