| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2009-07-08 : 18:43:37
|
| Patrons of the Forum,may I ask you a question which relates to LDF file shrinking.As I shrunk the LDF file it has got successfully shrinked thereafter the following day it has picked up the transactions and the same size as before the shrinking is allocated to its original size.EX: DB_LDF.LOG which is 100 GB when shrunk got to its lowest level like 1 MB then following day it again picked up by 100 GB.Can anyone explain this phenomena. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-08 : 23:15:00
|
| Seems you need to take log backups more frequently |
 |
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-07-09 : 02:38:20
|
quote:
Originally posted by pdset
Patrons of the Forum,may I ask you a question which relates to LDF file shrinking.As I shrunk the LDF file it has got successfully shrinked thereafter the following day it has picked up the transactions and the same size as before the shrinking is allocated to its original size.EX: DB_LDF.LOG which is 100 GB when shrunk got to its lowest level like 1 MB then following day it again picked up by 100 GB.Can anyone explain this phenomena.
u shud look out for bulkinserts and other transaction that might be executing as a nightly job.... run a trace to chek this ....or change the recovery model to bulkinsert and monitor the log growth |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-07-12 : 21:11:18
|
| I have done this twice but of no use as the result of the trace is nothing to point out. Every Night there will be a backup being executed and there were some development activity going on during the day time and as a result of the following mornining the scenario is back to square one.Any Suggestions.Thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-07-12 : 22:02:48
|
| As Russel mentioned take log backup frequently and bulk insert in batches not in 1 transaction. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-07-12 : 22:49:01
|
| Thanks All |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-07-20 : 17:47:52
|
| Hi Forum,I have done exactly and it worked out. But still the same result following day it again picked up the LDF to original settings.Let me clarify - do I need to shrink the MDF file along with the Shrink of LDF?Thanks for your time and patience. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-07-21 : 04:31:23
|
| Can anyone give the clue to break this problem for meThanks |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-21 : 04:38:42
|
| Have you by any chance set the growth of the file to 100GB? |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-07-21 : 18:03:07
|
| No I haven't. But set to 10 % growth only.The commands used as follows:Backup log <dbname> with truncate onlydbcc shrinkfile (<dbname_log>, 2, truncate onlyWith this LDF is shrunk as desired but following day it is the SAME as before.My Recovery model needs to be FULL since it is Production environment and 15 Minute transactions (TRN's) from 6 AM to 9:30 PM and my full backup runs between 10:30 PM till 12 AM. then the LDF will be picked up as same before.I do have question, whether I need to do MDF shrink also?Am I missing any logic here?Please advise and thanks to all who put their suggestions here. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-21 : 21:03:17
|
| Also may want to start your tran log backups earlier. Do you have large import jobs running overnight?By the way, do you know what happens when you truncate your tran log? It DISCARDS the transactions in the log. So this is not a good paractice!The reason it keeps growing is because the activity in your db requires this much space.If you're doing large overnight imports, may try optimizing them. Else, as Tara says, run your backups more frequently. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-07-22 : 18:18:02
|
| Thanks Tara and Russel.My production server has atleast overall 100 MB growth daily.how often do I need to use this statement maintain my LDF'sBackup log <dbname_log> with truncate onlyOnce again thanks all. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-22 : 21:35:29
|
| Don't do that EVER.Are you running regular transaction log backups? You indicated earlier that you do. Either you need to start doing so, or you need to change your db to simple recovery model.Log files grow to accomodate the activity in the database. If you're running out of drive space, you need to either (a) Get more storage (hard disks) or (b) take transaction log backups more frequently. Or maybe both...If you're doing large imports into the database, then you may consider flipping the recovery model to bulk-logged during the import.should have a look athttp://msdn.microsoft.com/en-us/library/aa173531(SQL.80).aspxhttp://msdn.microsoft.com/en-us/library/ms191253.aspxhttp://msdn.microsoft.com/en-us/library/ms190692.aspx |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-07-23 : 01:36:34
|
| Thanks.yes I do take 15 minute Tlogs during business hours but couldn't understand what makes the LDF to grow.how to contain LDF to its lowest possible levelCan please explain this scenario.many thanks for your expert advise. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-23 : 08:48:19
|
| it is only growing to the size it needs to be to log the transactions occurring.is it growing throughout the day, or overnight? |
|
|
|
DKidd
Starting Member
4 Posts |
Posted - 2009-07-23 : 10:33:03
|
| When we first started using SQL Server 2005, I had a similar problem. I set up two jobs in SQL Server Agent. The first does a transaction log backup every hour from 6 AM to 10 PM. The second does a backup of the data and a transaction log backup after my bulk inserts which occur at 1 AM. My MDF is about 1.2 Gigs in size and the LDF stays around 380,000 KB. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-07-23 : 18:02:48
|
| Very relevant and helpful tips so far and Many thanks to all.I have Two (2) Maintenance jobs that relates to backup stuff.The first maintenance job is to procure the TRN type backups every 15 minutes between 6 AM till 9:30 PM for 7 days a week.The Second Maintenance job runs for taking whole backup at 10:30 PM everyday and finishes midnight, subsequently it does the maintenance cleanup tasks such as 'Deleting Old Backup which is a day old' and thereafter deleting the TRN's which have been procured during that particular day - thus releasing my disk space available for next day's TRN to be picked up.However, LDF file is not building (as said by Russel) during the day but is consistent and am happy to keep the transactions being secured in the LDF. But other day, when I tried to truncate the LDF well after the 'Full Backup' procured would be ideal and it worked well bringing as low as possible.But, the following day when the transaction were started and as narrated TRN started build up, LDF has grown to the same size as before.since your replies were very suggestive but my question is why it should build in the LDF as TRN's are working and these TRN's are helping in FULL (BAK) backup.In otherwords, I think LDF (should stay to its least and writing dirty buffers back to BAK file) which was brought to its lowest and only TRN's (15 Minute) should be building.Thanks for patiently reading this episode of mine and with your valuable time in reply to me. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-23 : 21:06:32
|
| please read the links in my earlier post. they explain this behavior |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
LDF Shrink