Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transaction log backup file is too big Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 3:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 509, Visits: 1,885
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
Posted Tuesday, February 12, 2013 3:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
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
Posted Tuesday, February 12, 2013 3:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 509, Visits: 1,885
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
Posted Tuesday, February 12, 2013 3:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 4:10 AM
Points: 5,221, Visits: 5,119
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
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
Posted Tuesday, February 12, 2013 3:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 509, Visits: 1,885
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
Posted Tuesday, February 12, 2013 3:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 4:10 AM
Points: 5,221, Visits: 5,119
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
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 #1418853
Posted Tuesday, February 12, 2013 3:57 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 509, Visits: 1,885
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) .
Post #1418854
Posted Tuesday, February 12, 2013 3:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 4:10 AM
Points: 5,221, Visits: 5,119
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
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 #1418856
Posted Tuesday, February 12, 2013 4:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 509, Visits: 1,885
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
Posted Tuesday, February 12, 2013 4:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 4:10 AM
Points: 5,221, Visits: 5,119
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
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 #1418864
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse