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

ldf file size Expand / Collapse
Author
Message
Posted Sunday, June 16, 2013 3:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Dear All

I have a db for which recovory model is Full. And i am taking only full back up.
.Ldf size is bigger than the .mdf file. I wanted to reduce the file size. On one site i saw following solution
1. change the recovery model to simple
2. change the ldf initial file size to 1mb
3.Change the recovery model back to Full.

Is it a correct way? What will be the side effects of this?

Regards
Krishna1
Post #1463931
Posted Sunday, June 16, 2013 4:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
No, it is certainly not the right way to do this. What you should do is take LOG backups every now or so often in between your full backups. Depending on what your situation is, meaning, how many database changes happen over what period, what your point-in-time restore requirements are in case of failure will influence the appropriate intervals between LOG backups. Also, is this is a High Availablity scenario, i.e. are you doing log shipping or something similar? It might be overkill to be in Full Recovery in the first place.



--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1463939
Posted Sunday, June 16, 2013 6:20 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: Today @ 8:54 AM
Points: 3,085, Visits: 3,196
Hi,

The transaction log has to be configured well too. Growing files is pretty expensive operation.
Do you care about the VLFs count? The following link can help you configure it well - http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

Check the following link too: http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

Regards,
Igor




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1463948
Posted Sunday, June 16, 2013 12:06 PM


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
No. Completely incorrect approach.
Please read through this: http://www.sqlservercentral.com/articles/Administration/64582/



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 #1463971
Posted Tuesday, June 18, 2013 9:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:11 AM
Points: 47, Visits: 134
The point of confusion here may be the recovery model set to "Full". When the recovery model is set to full, that means you are taking full backups, (Probably differential backups), and -here's the important part- regular log backups. These are *required* for the Full recovery model.

If you use the "Simple" recovery model, you are indicating to SQL server that you will be taking full backups from time to time.

In simple recovery mode, when you take a full backup, I believe the log file is backed up and truncated.
Post #1464759
Posted Tuesday, June 18, 2013 10:10 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
ryan.mcatee (6/18/2013)
In simple recovery mode, when you take a full backup, I believe the log file is backed up and truncated.


No.

In simple recovery model a checkpoint will truncate the log. Checkpoints run on a regular basis. In full and bulk-logged recovery model a log backup truncates the log
Full backups do not, in any recovery model, truncate the log. Nor do they back up the log (though they include enough of the log for a consistent restore in all recovery models)



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 #1464768
Posted Tuesday, June 18, 2013 11:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:11 AM
Points: 47, Visits: 134
Okay, then, the SQL geniuses here have debunked my understanding of backup models, so ignore everything after my first paragraph.
Post #1464795
Posted Tuesday, June 18, 2013 11:10 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
Details of recovery models:
http://www.sqlservercentral.com/articles/Administration/75461/
http://www.sqlservercentral.com/articles/Recovery+Model/89664/



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 #1464798
Posted Wednesday, June 19, 2013 5:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 15, 2014 12:45 AM
Points: 328, Visits: 544
Please read through the backup docs more carefully because your understanding of Recovery Models is completely wrong!

The Full model maintains a transaction log so that point in time recoveries are allowed. For this you need to backup your database appropriately including a minimum of a full backup and associated transaction logs. On being backed-up the amount of data contained within the log will be reduced although the file size remains the same to the file system.

In Simple mode, the transaction log is only maintained (for simplicity) for the purpose of being able to rollback transactions (as a singular example). Point in time recoveries are not possible under this mode.
Post #1465110
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse