SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Size of the ldf file increasing


Size of the ldf file increasing

Author
Message
IT researcher
 IT researcher
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1594 Visits: 1798
I have some sql server database for which recovery model is set to full.Now for some databases the ldf size has become large.I tried taking log backup regularly,but i didn’t find decrease in physical size of ldf. I followed what stan20 has told in this link http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/704bcfbc-9251-4f4e-9a80-04bc7fe9ae78 But it only reduced logical file size. So how i can mange size of ldf file?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217881 Visits: 46278
Please read through this - Managing Transaction Logs

If the log file has grown too large, a once off shrink to bring it back to normal size is fine. Log backups don't shrink the log, just allow the space inside to be reused.

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


IT researcher
 IT researcher
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1594 Visits: 1798
So log backup only reuse the space. But shrinking the log file will increase fragmentation ryt? So is it good habit to shrink the log file?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217881 Visits: 46278
winmansoft (1/15/2013)
But shrinking the log file will increase fragmentation ryt?


No.

So is it good habit to shrink the log file?


Not regularly, no.

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


Abu Dina
Abu Dina
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 3325
winmansoft (1/15/2013)
So log backup only reuse the space. But shrinking the log file will increase fragmentation ryt? So is it good habit to shrink the log file?



LOL.. got to love Gail's short answers, I guess the hint is to go and read the article. But here let me try and help with hopefully, a simple example:

Consider the following scenario:

1) You create a database and assign 100MB for data file and 100MB for log file.

2) You perform a large data import and some complex data manipulation on the database.

3) When you check the size of your files you find that the data file has grown to 1GB and the log file 2GB!

4) A week later you do another big job on the database which requires 3GB worth of transaction log information to be recorded. Suddenly your log file is 5GB in total size


You can see where this is going... as more stuff happens in your database, the log file will just keep growing and growing (assuming auto growth is enabled). Either way, you would soon find out about it when your machine runs out of disk space or SQL can't write to the log file!

So how to solve this?! Shrink or backup log file you say?

a) Log backup will basically allow SQL Server to re-use the space that was occupied by the backed up transactions. The actual size of the file will remain the same (until you perform another massive work load which might increase the size of the log from 5GB to say 7GB

b) Shrinking the file will give back the space the OS but if you know you're going to perform more large data manipulation then it's just going to grow again anyway.

Hope this helps.

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217881 Visits: 46278
Abu Dina (1/15/2013)

3) When you check the size of your files you find that the data file has grown to 1GB and the log file 2GB!

4) A week later you do another big job on the database which requires 3GB worth of transaction log information to be recorded. Suddenly your log file is 5GB in total size


Won't be 5 GB. If it's in simple recovery or full recovery with log backups it'd be around 3GB or less. If full recovery and no log backups, probably a lot larger than 5GB because it'll contain every single data modification during the week, along with the 5GB of data loads.

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


IT researcher
 IT researcher
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1594 Visits: 1798
So shrinking only log file will not increase the fragmentation?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217881 Visits: 46278
No. Repeatedly shrinking and letting it grow might lead to file-system fragmentation.

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


VastSQL
VastSQL
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9324 Visits: 5306
winmansoft (1/16/2013)
So shrinking only log file will not increase the fragmentation?


Are you talking about index fragmentation??

If yes it happens only if you shrink data file or the whole DB
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11958 Visits: 7444
winmansoft (1/16/2013)
So shrinking only log file will not increase the fragmentation?
Shrinking the data file will cause fragmentation within the database, and as Gail has stated, regular shrinking in general (both Log and Data) will lead to fragmentation at the filesystem level. And if you're using RAIDed storage, defragmenting such storage devices will take a considerable amount of time (which your SQL Server will be unavailable).

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
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