Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ldf file size


ldf file size

Author
Message
Krishna1
Krishna1
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
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
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2212 Visits: 6490
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)
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 4847
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47212 Visits: 44368
No. Completely incorrect approach.
Please read through this: http://www.sqlservercentral.com/articles/Administration/64582/


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


ryan.mcatee
ryan.mcatee
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47212 Visits: 44368
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, 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


ryan.mcatee
ryan.mcatee
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 134
Okay, then, the SQL geniuses here have debunked my understanding of backup models, so ignore everything after my first paragraph. :-P
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47212 Visits: 44368
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, 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


kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 914
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.
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