<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Size of the ldf file increasing
11 posts, Page 1 of 2
1
2
»»
Size of the ldf file increasing
Rate Topic
Display Mode
Topic Options
Author
Message
IT researcher
IT researcher
Posted Tuesday, January 15, 2013 12:33 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 5:04 AM
Points: 181,
Visits: 931
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?
Post #1407052
GilaMonster
GilaMonster
Posted Tuesday, January 15, 2013 1:17 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 37,650,
Visits: 29,901
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 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 #1407066
IT researcher
IT researcher
Posted Tuesday, January 15, 2013 5:59 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 5:04 AM
Points: 181,
Visits: 931
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?
Post #1407209
GilaMonster
GilaMonster
Posted Tuesday, January 15, 2013 6:10 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 37,650,
Visits: 29,901
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 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 #1407214
Abu Dina
Abu Dina
Posted Tuesday, January 15, 2013 6:33 AM
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 484,
Visits: 2,122
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.
-----------------------------------
http://www.SQL4n00bs.com
Post #1407232
GilaMonster
GilaMonster
Posted Tuesday, January 15, 2013 6:55 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 37,650,
Visits: 29,901
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 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 #1407253
IT researcher
IT researcher
Posted Wednesday, January 16, 2013 6:43 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 5:04 AM
Points: 181,
Visits: 931
So shrinking only log file will not increase the fragmentation?
Post #1407809
GilaMonster
GilaMonster
Posted Wednesday, January 16, 2013 7:26 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 37,650,
Visits: 29,901
No. Repeatedly shrinking and letting it grow might lead to file-system fragmentation.
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 #1407832
Ratheesh.K.Nair
Ratheesh.K.Nair
Posted Wednesday, January 16, 2013 7:34 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:21 AM
Points: 2,415,
Visits: 3,370
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
Post #1407835
MyDoggieJessie
MyDoggieJessie
Posted Wednesday, January 16, 2013 7:38 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 2,035,
Visits: 3,759
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"
Post #1407837
« Prev Topic
|
Next Topic »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
