InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 11:30:35 GMT20http://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspxBest practice of shrinking the transaction log is to shrink it all the way down to smallest size possible (close to 0) and then alter the log to grow it back to the desired size.If the desired size is greater than 4gb, then alter the size in 4gb chunks. Per Kimberly Tripp (if I remember correctly), when you create the tran log greater than 4gb or alter it by more than 4gb, the vlf sizes are greater than what is optimal.Wed, 16 Jan 2013 09:57:39 GMTarnipeturssonhttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspx[quote][b]winmansoft (1/16/2013)[/b][hr]So shrinking only log file will not increase the fragmentation?[/quote]Shrinking the [b]data file[/b] 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).Wed, 16 Jan 2013 07:38:38 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspx[quote][b]winmansoft (1/16/2013)[/b][hr]So shrinking only log file will not increase the fragmentation?[/quote]Are you talking about index fragmentation??If yes it happens only if you shrink data file or the whole DBWed, 16 Jan 2013 07:34:38 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspxNo. Repeatedly shrinking and letting it grow might lead to file-system fragmentation.Wed, 16 Jan 2013 07:26:11 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspxSo shrinking only log file will not increase the fragmentation?Wed, 16 Jan 2013 06:43:10 GMT IT researcherhttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspx[quote][b]Abu Dina (1/15/2013)[/b][hr]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[/quote]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.Tue, 15 Jan 2013 06:55:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspx[quote][b]winmansoft (1/15/2013)[/b][hr]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?[/quote]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 sizeYou 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 7GBb) 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.Tue, 15 Jan 2013 06:33:14 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspx[quote][b]winmansoft (1/15/2013)[/b][hr]But shrinking the log file will increase fragmentation ryt? [/quote]No.[quote]So is it good habit to shrink the log file?[/quote]Not regularly, no.Tue, 15 Jan 2013 06:10:45 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspxSo 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?Tue, 15 Jan 2013 05:59:26 GMT IT researcherhttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspxPlease read through this - [url=http://www.sqlservercentral.com/articles/64582/]Managing Transaction Logs[/url]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.Tue, 15 Jan 2013 01:17:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407052-1550-1.aspxI 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?Tue, 15 Jan 2013 00:33:39 GMT IT researcher