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

Big transaction log file Expand / Collapse
Author
Message
Posted Wednesday, February 19, 2014 12:40 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: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
cygne17_2 77208 (2/18/2014)
Which scenario do you recommend?

1- Backup the database, backup the transaction log to a new disk, shrink the transaction log file, schedule transaction log backup each hour.

2- Backup the database, put the recovery model option= simple, shrink the transaction log file, Backup the database.


It depends.

First identify the data loss allowance for that DB (RPO). That will determine whether that DB needs full recovery and log backups or simple recovery and just full backups.



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 #1542877
Posted Wednesday, February 19, 2014 4:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 1,887, Visits: 1,179
I agree with Jeff, but would put Step 10 as Step1; that way when things go wrong it won't seem so bad!

Regards
Lempster
Post #1542918
Posted Wednesday, February 19, 2014 7:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:50 AM
Points: 27, Visits: 74
Hi all,
Thanks for all your answer.
Very helpful !
regards
Cygne
Post #1542985
Posted Wednesday, February 19, 2014 7:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 20,460, Visits: 14,086
cygne17_2 77208 (2/19/2014)
Hi all,
Thanks for all your answer.
Very helpful !
regards
Cygne


You're welcome




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1543022
Posted Wednesday, February 19, 2014 8:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:13 PM
Points: 958, Visits: 1,285
Jeff Moden (2/18/2014)
cygne17_2 77208 (2/18/2014)
Hi,
I found a sql server database with a transaction log file of 65 GB.
The database is configured with the recovery model option = full.
Also, I noticed than since the database exist, they only took database backup.
No transaction log backup were executed.
Now, the "65 GB transaction log file" use more than 70% of the disk space.

Which scenario do you recommend?

1- Backup the database, backup the transaction log to a new disk, shrink the transaction log file, schedule transaction log backup each hour.

2- Backup the database, put the recovery model option= simple, shrink the transaction log file, Backup the database.


Neither.

1. Do a full backup of the database.
2. Set the recovery model to SIMPLE.
3. Shrink the transaction log file to ZERO.
4. Regrow the transaction log file to the expected max size you'll need to ensure you don't have a bazillion VLF's.
5. Make sure the auto-growth is set to something reasonable like 1,000MB (1GB) or whatever you are comfortable with with respect to VLFs. Whatever you do, don't use a percentage to control growth.
6. Set the database back to full recovery.
7. Take a DIF backup to re-establish the log chain.
8. Enable the POINT-IN-TIME (PIT) log file backups with the idea that the time between log file backups should be less than the max number of minutes of data you can affort to lose.
9. Verify that the PIT backups are working correctly.
10. Drink Beer.


I'm all in favor in Step 10, even if you haven't completed all steps 1-9

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1543067
Posted Wednesday, February 19, 2014 8:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:53 PM
Points: 3,733, Visits: 7,072
+1 , anyone live in the Nashville, TN area???

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1543074
Posted Wednesday, February 19, 2014 2:18 PM
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: Today @ 12:10 AM
Points: 574, Visits: 3,747
Yes, But SQL Saturday was last month...
Post #1543221
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse