Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
XML
Starting Member
3 Posts |
 Posted - 2011-05-31 : 23:42:08
|
| Good Day,I have an issue in our LDF File. The capacity of LDF is 50GB and the MDF file is 1GB only. Our processing is now getting slower and slower.. is it possible that i can attach new LDF to replace the old LDF with 50GB and if it will replace is there no issue in MDF file by means of record deleted ? what is the difference between shrinking and replacing new LDF file ? hoping you can help us. thank you.. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-01 : 00:00:25
|
| An LDF getting larger in size does not impact performance.Do not detach the database to fix the LDF file size. Detaching is risky, and there's an easy fix for this.All you need to do is change your recovery model to SIMPLE as it seems you don't care about point in time recovery and then shrink it down via DBCC SHRINKFILE. If you change your mind about point in time recovery, then switch back to FULL and start performing regular tlog backups such as every 15 minutes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-01 : 01:53:08
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
XML
Starting Member
3 Posts |
Posted - 2011-06-01 : 08:31:14
|
| Thanks for immediate response from GilaMonster and tkizer.if i use the DBCC SHRINKFILE to LDF to 1MB from 50GB, is it ok ? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-01 : 08:56:50
|
| no. Shrink to a sensible size. The size the log needs to be for regular activity.And that after you've sorted out the maintenance (refer my article)--Gail ShawSQL Server MVP |
|
|
|
XML
Starting Member
3 Posts |
Posted - 2011-06-02 : 03:00:55
|
| what is the best size to shrink the LDF file ? is there any script where in i can analyze or guide on how many size should i put in DBCC shrinkfile... thanks |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-02 : 05:17:58
|
| The size it needs to be for the regular activity of your database. You're the only one who can work that one out. Monitor for a few days, DBCC SQLPERF(LogSpace) and see what the max size the log needs, add 20% or so, shrink to that.And that's assuming you sorted out the maintenance.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
LDF File getting Bigger